Query surprise!

[Guest]

I have a Select Query, theSQL looks like this:
SELECT DISTINCT tblPayments.Yatah! Yatah! ---, tblCourseSelect. Yatah!
Yatha! ----
FROM tblPayments LEFT JOIN tblCourseSelect ON tblPayments.CourseNoPaidFor=
tblCourseSelect.SelectedCourseNo
WHERE (((tblPayments.PaymentDate)>#6/30/2006#));
Both tables have key fields that are not part of the relationship and both
tables have another common field "StudentID" also not part of the
relationship.
This query has worked for over a year on a frontend backend setup with only
changes to the WHERE statement > date being made to all computers. Then not
on all the computers, but some computers are showing 24084 records in the
query and the computers that are working, as designed, have 1245 records in
the query. What happened? If I set the quey's unique values property to "Yes"
the record count becomes 1245, but then in the form that has this query as
its source, fields can not be edited.

 

[John Vinson]

I have a Select Query, theSQL looks like this:
SELECT DISTINCT tblPayments.Yatah! Yatah! ---, tblCourseSelect. Yatah!
Yatha! ----

Do you actually have fields named [Yatah! Yatah! ---] and [Yatah!
Yatah! ----] ????

or is that "spinach" for the newsgroup?

I'd be utterly astonished if this query could ever work, at least not
without brackets around these fieldnames.

FROM tblPayments LEFT JOIN tblCourseSelect ON tblPayments.CourseNoPaidFor=
tblCourseSelect.SelectedCourseNo
WHERE (((tblPayments.PaymentDate)>#6/30/2006#));
Both tables have key fields that are not part of the relationship and both
tables have another common field "StudentID" also not part of the
relationship.
This query has worked for over a year on a frontend backend setup with only
changes to the WHERE statement > date being made to all computers. Then not
on all the computers, but some computers are showing 24084 records in the
query and the computers that are working, as designed, have 1245 records in
the query. What happened? If I set the quey's unique values property to "Yes"
the record count becomes 1245, but then in the form that has this query as
its source, fields can not be edited.

Without knowing more about these two computers - Access versions,
service packs, the actual SQL of the query - I have no idea. Differing
results on different computers for the same query makes me suspect
that the two computers are out of synch for service packs, but it's
hard to be sure.

John W. Vinson[MVP]

 

[Guest]

Sorry John, I just felt using up the space to show all the fields that were
used from these 2 tables would be a waste of time for me and someone like
you to have to read through, but obviously I was wrong and you would rather
have read though the following:
SELECT tblPayments.RegisterReceiptNumber, tblPayments.PaymentDate,
tblPayments.[StudentID], tblPayments.PaymentType, tblPayments.AmountPaid,
tblPayments.TuitionAmount, tblPayments.FeesAmount, tblPayments.PaymentMethod,
tblPayments.FundingSource, tblPayments.DocDate, tblPayments.RegisterNumber,
tblPayments.[3rdPartyName], tblPayments.[ tbl3rdPartyAddress],
tblPayments.[3rdPartyCity], tblPayments.[3rdPartyState],
tblPayments.[3rdPartyZip], tblPayments.[3rdPartyPhone],
tblPayments.[Account#], tblPayments.PaymentNote, tblPayments.TransactionType,
tbl Payments.TaxStatus, tblPayments.[CourseNoPaidFor], tblPayments.User,
tblPayments.[GL#], tblPayments.Cost, tblPayments.Quantity,
tblPayments.ItemDescription, [Quantity]*[Cost] AS Amount,
tblSelectCourse.ClassName, tblSelectCourse.TestStatus
FROM tblPayments LEFT JOIN tblCourseSelect ON tblPayments.CourseNoPaidFor=
tblCourseSelect.SelectedCourseNo
WHERE (((tblPayments.PaymentDate)>#6/30/2006#));
(I changed the Unique Values property back to "No".)
Since I am not privey to the the disposition of each of the 9 computers
involved, I can only say I will check with the IS manager Monday and see if
getting all of the computers at the same version and service packs version
will help. I can not belive all were working and we had sevice packs updates

I have a Select Query, theSQL looks like this:
SELECT DISTINCT tblPayments.Yatah! Yatah! ---, tblCourseSelect. Yatah!
Yatha! ----

Do you actually have fields named [Yatah! Yatah! ---] and [Yatah!
Yatah! ----] ????

or is that "spinach" for the newsgroup?

I'd be utterly astonished if this query could ever work, at least not
without brackets around these fieldnames.

FROM tblPayments LEFT JOIN tblCourseSelect ON tblPayments.CourseNoPaidFor=
tblCourseSelect.SelectedCourseNo
WHERE (((tblPayments.PaymentDate)>#6/30/2006#));
Both tables have key fields that are not part of the relationship and both
tables have another common field "StudentID" also not part of the
relationship.
This query has worked for over a year on a frontend backend setup with only
changes to the WHERE statement > date being made to all computers. Then not
on all the computers, but some computers are showing 24084 records in the
query and the computers that are working, as designed, have 1245 records in
the query. What happened? If I set the quey's unique values property to "Yes"
the record count becomes 1245, but then in the form that has this query as
its source, fields can not be edited.

Without knowing more about these two computers - Access versions,
service packs, the actual SQL of the query - I have no idea. Differing
results on different computers for the same query makes me suspect
that the two computers are out of synch for service packs, but it's
hard to be sure.

John W. Vinson[MVP]

 

[John Vinson]

I can only say I will check with the IS manager Monday and see if
getting all of the computers at the same version and service packs version
will help. I can not belive all were working and we had sevice packs updates
on 3 or 4 computers last Monday and not on the rest. Hope the SQL helps,
JimN

Sorry about misinterpreting the SQL - you're quite right, it was just
shorthand for "a lot of fieldnames irrelevant to the problem" and I
should have seen that!

AFAIK the query itself is not at fault - the query isn't (by itself)
going to work on one computer and fail on another, nor work on Monday
and fail on Tuesday. *Something* changed on these computers to cause
this problem: possibly the database became corrupted (if it's split,
can you install a clean frontend?), the Access installation or library
changed (was some other software installed?), or something! Good luck
figuring it out, I'm perplexed!

John W. Vinson[MVP]