Access to MSDE/SQL Server conversion resources?
We have an Access 2002 app used by 10 remote users who replicate back to a
central site. We have recently had a problem with replication plus a general
issue with a corrupted central database. We are now going to do what we
should have done in the first place - user MSDE for the remote laptops and
SQL Server and the central site.
Can anyone point me to resources/books that describe the issues we are about
to face and best practice approach to this project.
"Peter Jones" <PeterJones@discussions.microsoft.com> wrote in message
news:200179AA-0934-4F13-...SQL totalling items report
I will be very grateful if someone will give me the SQL to run a query using
the Northwind data base that will produce a report that shows totals of
“Units in Stock” for each “Category” (ie Beverages etc) that runs across the
report so each column in headed by the Category (ie Beverages) and so on.
with kind regards
Take a look at Crosstab query. I don't have Northwind on this computer so I
can't tell you the exact methodology, but the crosstab wizard may be able to
Access MVP 2002-2005, 2007-2010
The Hilltop ...Convert Query to SQL
I wrote this query (in access03) and then went to sql view and tried
top copy and paste it into a button's on click event
DoCmd.RunSQL "UPDATE tblAnimalBleedsStatus INNER JOIN
tblCatalogPartNumbers ON tblAnimalBleedsStatus.CatalogNumber =
tblAnimalBleedsStatus.FinalLotsWB = [tblCatalogPartNumbers]![WB]WHERE
(((tblAnimalBleedsStatus.FinalLotsWB) Not Like '11*' And
(tblAnimalBleedsStatus.FinalLotsWB) Not Like 'R&D' And
(tblAnimalBleedsStatus.FinalLotsWB) Not Like '44*'));"
I had to modify it by changing...Converting Lead Notes and Activies information into Opportunity re
The Lead Activities (phone calls, emails, task, appointments, fax, letter)
and the Notes created while working on a Lead do not "convert" into the
Opportunity. To get this information, you have to go to Closed Leads or
drill down through the Originating Lead link. I've read that this will be in
2.0 but in the meantime, can anyone suggest a simple workaround. I would
also like to hear about the best way to do this, simple or note. If I create
new fields in the Lead form and map them to new fields in the opportunity
form, the activity functionality is reduced to that of ...Diagram show blank as zero value when based on a formula
When a chart is based on a formula e.g. =IF(A1=0;"";A1*B1) then the chart
will show the blank value "" as zero even if you whant to plot gaps or plot
the empty cell interpolated. Yes, I know Excel say empty cell and the cell is
not empty when there is a formula. Is there anybody who have found a "work
around"? I often have formulas that generate zero in the result but I want to
plot the result as if it was empty.
I found the reply myself in a question from another user similar to this issue.
I will use NA() instead of ""
Thank you that works!
&q...minimize/hide query windows
I run ~200 queries which modify the data, create some tables etc,
through a macro. The macro runs when the user clicks a button on a
form. I would like the queries to be invisible while they are running;
but currently the user sees 50+ query windows open/close which is
Is there a way to automatically minimize/hide query windows or disable
screen update? I know this is possible in Excel by setting
application.screenupdating =false, is there a similar way in Access?
Let me get this clear. You don't want the queries to be shown. That
implicates that you a...LINQ2SQL ... Mini SQL Browser
I am developing a mini SQL browser wherein I display all database objects
such as tables, functions etc.,
Upon selecting a table, its contents to be displayed.
I use Mapping.GetTables() and Mapping.GetFunctions() to get the list.
Writing dynamic linq query and displaying seems little problem
Any help would be appreciated
On 2010-03-02 6:35, Raj wrote:
> I am developing a mini SQL browser wherein I display all database objects
> such as tables, functions etc.,
> Upon selecting a table, its contents to be displayed.
> I use Mapping.GetT...Sql Server 2008 installation problem
Here is an interesting problem I now have several Sql Server 2008
servers set up at the company. I have installed several times the client
tool for this server this set up is for users and low level admins. On
one machine the load keeps failing.... During the process it tells me to
reboot the machine then starts all over again with the first steps of
the istallation then reboot again. Someone mentioned that you could not
load SQL Server 2008 if you have a copy of Sql server 7.0 and SQL
server2000 and SQL server2005 on the same box? Is that true?
*** Sent via D...Lead to Product and Lead to Competitor relationships
While browsing MS CRM logical DB Diagrams (http://www.microsoft.com/
dynamics/crm/using/sdk/DBDiagrams.mspx) for Sales Entities, I have
found many-to-many relationship between Lead and Competitor entities.
In SQL Server I made a diagram with the following tables: LeadBase,
ProductBase and CompetitorBase and then I added tables
LeadCompetitors, LeadProduct (these tables contain no data in my
case). You can see my diagram below. I saw many-to-many relationships
between Lead & Product and Lead & Competitor. As far as I understand,
these relationships must support functionality for adding ...Display SQL Code for a Query
How might I display the SQL code for a query in a report?
>How might I display the SQL code for a query in a report?
Depends on where/how the query is specified.
If the report's record source is the name of the query, then
just select the query in the database window and click on
the design button. If that takes you to the QBE grid, then
select SQL view from the View menu or the query design tool
If the report's record source is an SQL statement, then just
right click on the record source property and select Zoom
from the pop up menu (or you could use the...how to Sum last 10 values of a dynamic column excluding zero
hi, i have this formula =SUM(OFFSET(P2,MATCH(1E+30,P:P)-1,0,-10,1)) which
sums the last ten values in column P, i was hoping someone could amend it
slightly for me. As column P is dependent on other entries the entire column
has 0 in it which makes this formula ineffective, i can't put an IF formula
into column P because then that wrecks other formulas that need column P, so
i need to work in a way of excluding zero into the forumla above.
Also, the formula does not work until there are ten values in the column, is
there any way to make it work and count the values up until ...payroll query sample
Does anyone know of a good MS Access payroll system sample I could look at ?
Preferably one that uses promotion dates with new job rates, calculates
employee and employer taxes, etc.
I am trying to compile one for my family's business. Thank you !
...Keeping one of 2 cells always zero
Hi all of you,
I have such a requirement. There are 2 cells in which the user ca
input values. However, one of the two cells must always be zero fo
valid inputs. Is there any way to do this?
I was thinking of setting other cell to zero when user enters value i
one cell or prohibit user from entering value in a cell till the othe
is made zero but I don't seem to figure out how to do this.
Is a macro the only way out (trapping a worksheet change?)? Is i
possible to use someother functionality like data validation. I woul
also like to impose restrictions on the non-zero cell of values betw...CSV File
I am opening a common delimted file in Excel and formating 2 fields as "text"
to retain leading zeros. They appear correctly in Excel. I then need to
save as a "csv" file and retain the leading zeros. How can I do this? The
zero's always drop off in the csv file when retrieved again in excel.
do you need to save the csv every time, if you save I agree it will remove
There is no perfect solution to this but if your file format is standard,
you may consider preparing a excel template with the repeate, concencanate
and left and right formul...Column Limit in MS-Query
A user had a query that used to work fine with about 200
columns. It now will not run. We kept deleting columns
in the query until it ran and it did so at 179 columns.
This issue is happening on XP and Windows 2000 in Office
2002 and Office 2000. The database that is being queried
is an Oracle database. The SQL runs fine in a normal SQL
...Fill leading zeros
I have a SELECT statement that I want to fill leading zeros in one column and
trailing blanks in another column up to xx characters. For example
SELECT CAST(EmployeeNumber AS varchar(8))
If the EmployeeNumber is 1234 I want to output 00001234
SELECT CAST(LastName AS varchar(15))
If the LastName is SMITH I want to output SMITHxxxxxxxxxx where x = space
Could this be done by casting a CHAR(15)?
> SELECT CAST(EmployeeNumber AS varchar(8))
> If the EmployeeNumber is 1234 I want to output 00001234
SELECT RIGHT('00000000' + CAS...Generate Leads/Accounts/Cases from a public website?
My employer asked me to investigate using a public .Net website to help
generate information to be used in CRM. Specifically, he'd like our website
to have webforms that, when filled out and submitted, would generate leads,
accounts, or cases depending on what our needs are (I suspect generating
leads would suffice for now).
Can anyone point me in the right direction on how to accomplish this? I've
found it difficult to find information online concerning this.
We're currently using CRM 3.0 although I'd be interested to know what
differences would be in...SQL SERVER 2005 Full backup job takes 3 times longer than before
I have a sql server 2005 production db server. Before the full backup
job using maintenance plan is only taking around 16 minutes but
recently it's taking around an hour. I'm not sure what's causing the
problem. The databases just get a little bigger so it shouldn't be the
problem. The only thing I can think of is that I set up replication to
another server around that time so I'm wondering if the replication's
causing this problem. Do you have the same problem before? If the
replication is causing it, is there any way to fix the issue? Thanks.
I want to count the number of lead in the database with reports, but
don't known how to do ? If anyone has an idea ?
You won't be able to accomplish this by using the Report Wizard. You need to
use the SRS BI tool for Visual Studio.
> I want to count the number of lead in the database with reports, but
> don't known how to do ? If anyone has an idea ?
On 14 ao=FBt, 16:01, SS <S...@discussions.microsoft.com> wrote:
> You won't be able t...Calling SQL in ON_LOAD Event
Does anyone have any JScript on how to call SQL from a form ON_LOAD
event? I want to populate a few fields with information stored in a
separate database. Thanks, John
I would approach it like this:
1. create a simple web service with a method to take a "select" statement
and execute this against the destination database.
2. Get the result back as XML
3. From client side , call the web service method and pass the "SQL" through
as a parameter.
4. Get the responsexml from the called method, parse it and display it.
JS Code to call your web service (the SQL code ...Error 3001 recordset find sql
I am new to VBA and am stumped by the error 3001 I'm getting for some data
values. I tried using the suggestions given for similar posts in this group,
but without success. Here is a summary of my problem: I'm using MSAccess
2007. From a form button click event, I am using the Find method to locate a
specific record in a table (cnaddres). The field name I want to search on is
Est Num; it is a text data type in the cnaddres table. I declared strSQL as
string variable and assigned it as strSQL = "[Est Num] = " & Me!EstNum (where
Me!EstNum is a current value fr...How to print a simple list of tables, queries & forms ?
How can I print a list of all the tables, queries and forms in my data
I've tried Tools | Analyze | Documenter | Tables | Options | Select
... Include for Table = Nothing
... Include for Fields = Nothing
... Include for Indexes = Nothing
We only want a simple list of tables, queries & forms.
thanks for any help.
Dim o As AccessObject
For each o in currentData.AllTables
For each o in currentProject.AllForms
For each o in currentProject.AllQueries
On May 1, 10:38 am, Mel <MyEm...Averaging function is pulling back a zero in Excel
I have a simple averaging formula in an excel spreadsheet that is now
providing nothing but a zero as a result of the formula. I have ensured that
the cell format is for "Number" and that all of the cells for the formula are
formatted for "number" as well. I am not getting the "DIV/0!" error so I know
it is not a dividing by zero error.
Are you using AVERAGE() or a formula you created to do the average?
Maybe the precission you chose to display is too small. i.e. if you have a
cell formatted to show no decimal places a number like .15 will be rounded to...Error: failed to create windows users/groups or add the sql loggin
I'm trying to install MS POS 2.0 on SBS 2003
But after install I get this error: Error details: failed to create windows
users/groups or add the sql loggins. This is from the after database install
wizard. So I cannot start the program without finishing this wizard.
Do you know where is the problem?
> Do you know where is the problem?
Restore database manually via osql/sqlcmd or sql management studio..
do you have the privileges to restore a database on your sql server? or
the admin rights?
> beba wrot...SQL for deleting specific transaction
I have test transactions I need to delete mixed in with real transactions and
would like to delete them based on TransactionNumber however
TransactionNumber is not a column is some related tables. Please help!!
> I have test transactions I need to delete mixed in with real transactions and
> would like to delete them based on TransactionNumber however
> TransactionNumber is not a column is some related tables. Please help!!
If you want to delete a transaction.. you must delete data in
[Transaction], [TransactionEntry], [TenderEntry] and you...