If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > query results different on different computers

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Apr 2010
Posts: 33
query results different on different computers

I've tried to troubleshoot this problem but with no success. When I run a query I created on my computer the calculations work and the query runs as it should. When someone else runs the same query from the same shared database on our intranet there is a calculation error. Two different people have tried running the query on their machines and they both get the same error. Let me know if more information is required. If anyone has a suggestion I'm all ears!
Reply With Quote
  #2 (permalink)  
Old
(Making Your Life Easy)
 
Join Date: Feb 2004
Location: New Zealand
Posts: 1,275
can we see the query
__________________
hope this help

See clear as mud


StePhan McKillen
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment:
Access based on my own environment: DAO3.6/A97/A2000/A2003
VB based on my own environment: vb6 sp5
ASP based on my own environment: 5.6
VB-NET based on my own environment started 2007
SQL-2005 based on my own environment started 2008
MYLE
Reply With Quote
  #3 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,212
check the front ends are talking to the same back end (insert a record, say in 'you'r PC and make certain that record is visible in the others.

make certain the query isn't dependant on any local data

atfer tht asMyle says we'd probably need to see the query
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #4 (permalink)  
Old
Moderator
 
Join Date: Jun 2005
Location: Richmond, Virginia USA
Posts: 2,507
Quote:
Originally Posted by BigToe View Post

...When someone else runs the same query from the same shared database on our intranet...
Is this app split into a Front End/Backend configuration, as it has to be, for a multi-user environment? And if the answer is 'yes,' does each user have a copy of the Front End on their hard drives? The only thing that should be shared, here, is the Backend, which should only contain the Tables.

What is the Access version? Is the version/service pack app the same in the development machine and the errant machines?

Oh, yeah, we probably really, really, really need to see the query!

Linq ;0)>
__________________
Hope this helps!

The Devil's in the Details!!

All posts/responses based on Access 2003/2007

Last edited by Missinglinq; 01-19-13 at 16:34.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Apr 2010
Posts: 33
At the risk of exposing my admin flaws I will say that I do not have this Access 2007 database file split into a frontend/backend setup. It is used only by 4 trusted users and there has never been a problem with reports or queries with anyone opening this DB in the past. There are two backups of this DB and I did write the query on my personal backup file and exported it to the working file - if that helps provide any insight.
SQL is as follows:
Quote:
SELECT [client first name] & " " & [client last name] AS [Name of Provider], [Client Information].MRN, Format(CDate([year] & "/" & [month] & "/" & [days].[days]),"yyyymmdd") AS [Service Date2], Round(4*IIf(IsNull([client information].[days of service per month]),([client payments].[auth hrs]*[client payments].[Hrly rate]+[client payments].[variance])/[client payments].[hrly rate]/[days],([client payments].[auth hrs]*[client payments].[Hrly rate]+[client payments].[variance])/[client payments].[hrly rate]/[client information].[days of service per month]),0)/4 AS DayHrs, Format(Now(),"yyyymmdd") AS [Extract Date], Day(DateSerial(Year(forms!ClientInfo.parisDate),Mo nth(forms!clientinfo.parisDate)+1,0)) AS days, days.days, days.days, [Client Information].[Days of Service per Month]
FROM days, [Client Information] INNER JOIN [Client Payments] ON [Client Information].[Client Number] = [Client Payments].[Client Number]
WHERE (((days.days)<=Day(DateSerial(Year([forms]![clientinfo].[parisDate]),Month([forms]![clientinfo].[parisDate])+1,0))) AND (([Client Payments].Year)=[forms]![clientInfo].[CRyear]) AND (([Client Payments].Month)=[forms]![clientInfo].[CRmonth]) AND (([Client Information].Status)="active") AND (([Client Payments].[Hrly Rate])>0));
thanks for the help!!!
Reply With Quote
  #6 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,212
so have you checked that all users are using the same DB, ie new rows entered in one DB appear in all other DB's and vice versa

Your current approach is OK ASSUMING that the db is not used as a multi user db concurrently. allowing multiple users to work concurrently in a single MDB/MDE is asking for trouble. if it is a multi user DB then split the application into a separate DB AND ideally deploy a separate front end to every user
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Apr 2010
Posts: 33
yes, I've been on the phone with one coworker and we went through a step by step opening the same shared drive etc and I also walked through the query with him confirming he was looking at the same SQL as me. The error occurs here:
Quote:
Round(4*IIf(IsNull([client information].[days of service per month]),([client payments].[auth hrs]*[client payments].[Hrly rate]+[client payments].[variance])/[client payments].[hrly rate]/[days]
I used to have the "/31" instead of "/[days]" and even though the I confirmed on the phone that my coworker was looking at "/[days]" in this formula, the query acts like it is still dividing by 31.

If it's not obvious [days] is an expression that counts the number of days in a field on a form and I use that number in the formula above.
Reply With Quote
  #8 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,212
so what is the value of days

as ever you need to examine the actual SQL NOT the VBA creating the SQL

when you say its on a shared drive how is the share mapped/specified. is it a URL or a drive letter. Have you checked that new rows entered in one machine are seen ion all other machines AND vice versa. ie proving that they are the same db

Day(DateSerial(Year(forms!ClientInfo.parisDate),Mo nth(forms!clientinfo.parisDate)+1,0)) AS days, days.days, days.days,

it could be a fragment of pasting the SQL but that looks wrong....


assuming that all users are using the same DB then next step is to actually examine the SQL and see if there are differences. either set a watch/breakpoint or use a message box to display the ACTUAL SQL
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Apr 2010
Posts: 33
the value of [days] is the number of days in the month which is taken from a date field on the user's form. The hours alocated for each client per month is divided by [days] to give a breakdown of the number of hours of service per day. Each month will have a slightly different figure depending on if the formula gets divided by 30, 31, 28 or 29.

it is on a shared drive letter.

I haven't entered any new data to confirm if we are seeing the same database because I've walked through the process of opening the database on the shared drive with my coworker (i.e. not clicking on any shortcuts etc.)

yes, "Mo nth" is from pasting on the forum. it's actually "Month" in the SQL. I also got rid of the second days.days. I was probably trying something and forgot to delete it.

I may try deleting the query in the shared database and create a new query, pasting the same SQL and saving as a different name just to see if there is some weird link to my backup database and the shared one from exporting the query as I did. I believe the shared database was open by others the same time I was exporting. Not sure if that's a problem or not :-P
Reply With Quote
  #10 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,212
OK so check the shared drive maps to the same physical location
right now you need to prove that everyone is using the same db
again you need to take a step back from what the code shoudl be doign as opposed to what it is actually doing

so by asking what the value of days is, Im not asking what it should be, but the actual value taken form the form is and comaparing against both result sets.

withe respect
Quote:
I haven't entered any new data to confirm if we are seeing the same database because I've walked through the process of opening the database on the shared drive with my coworker (i.e. not clicking on any shortcuts etc.)
doesn't prove anything. put some new records in on your machine and find out if your colleague can see them. if they can then defacto you are looking at the same DB.


Quote:
yes, "Mo nth" is from pasting on the forum. it's actually "Month" in the SQL. I also got rid of the second days.days. I was probably trying something and forgot to delete it.
..actually you want to get rid of two of the days
Day(DateSerial(Year(forms!ClientInfo.parisDate),Mo nth(forms!clientinfo.parisDate)+1,0)) AS days, days.days, days.days,


is this db split into a front end and a separate back end?
it could be a symptom of corruption (try a compact and repair on a backup and see if that helps
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #11 (permalink)  
Old
Moderator
 
Join Date: Jun 2005
Location: Richmond, Virginia USA
Posts: 2,507
Exclamation

Quote:
Originally Posted by BigToe View Post

...It is used only by 4 trusted users and there has never been a problem with reports or queries with anyone opening this DB in the past...
You need to understand that having multiple users, opening the app concurrently, can work without problems for extended periods of time, but once corruption arises, it will become chronic! Corruption can take on many appearances, none of them pleasant, and can include permanent loss of data! Just to repeat, if more than one user is in this database at any given point in time, it simply has to be split! It doesn't matter whether you're speaking of two users or two hundred, and their Access skills makes no difference!

BTW, the longest period of one of these running, non-split, without problems, is twelve years! But once the problems started, they kept arising, over and over and over again until the developer heeded advice and split the app!

Linq ;0)>
__________________
Hope this helps!

The Devil's in the Details!!

All posts/responses based on Access 2003/2007
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Apr 2010
Posts: 33
corruption is such an ugly word - especially when it's bolded! However, point taken. I will start the process of splitting the database. If I find a solution to the initial problem I will post. I do appreciate the help!
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On