SQL QUERY DROPPING RECORDS, WHY??

[faxpay]

I am using an Access 2000 database. The following statement works well until I have 2 or more records with same WAGENO but different WRATE. The first record is skipped but the second is pulled ok.
What's curious is if I have only one record of a particular WAGENO it pulls ok. As soon as I add that second matching WAGENO and they have different WRATE's then only one of the two records is pulled.
The WAMT field should not be summed and records combined unless the WAGENO and WRATE match.
All the above is on a per EMPNO basis.

Can anyone tell me how I can pull both records that do not have matching WRATE's but the WAGENO's are matching ???

SELECT E.WAGENO,EMPNO,REGHRS,WRATE,OTHRS,WAMT,
(SELECT SUM(WAMT)
FROM EMPEARNPERWAGE
WHERE WAGENO=E.WAGENO AND EMPNO=E.EMPNO AND WRATE=E.WRATE
AND DATEPAID>"12/31/2004" AND PPS>"12/31/2004"
AND PPE>"12/31/2004") AS YTD_AMT
FROM EMPEARNPERWAGE AS E
WHERE PYRLNO=2317
ORDER BY EMPNO

If this needs any more explanation let me know. Thank You.

faxpay, Tom

 

[THWatson]

faxpay
It looks to me as if your SQL is asking for only those records where the WRATE is the same to pulled, as you include the clause WRATE = E.WRATE

I was also wondering about your dates. Are they Date fields or Text fields? If they are Date fields, then they would be enclosed with the # sign, as in #12/31/2004# rather than "12/31/2004"

Tom

 

[faxpay]

THWatson,

Those are in fact text fields that hold dates.

Is not the main query asking for all records with the named fields but then the subquery is asking that all records with matching WRATE fields be summed??

Is the problem that maybe the text fields that hold dates should be date fields?? Can I somehow have the statement look at the text fields as date fields??

Thank You for your input.

faxpay, Tom

 

[THWatson]

faxpay, Tom
Is there a reason that you are storing Date fields as Text? I'm not 100% sure but I suspect that may be causing the difficulty.

Take a look at this Microsoft link, which talks about changing a field's data type...

http://office.microsoft.com/en-us/assistance/HP051875231033.aspx

It refers to Access 2003, but date and text formats haven't changed, so it would appear that your text fields should convert to date fields without problem by just changing the Data type in the table.

For security purposes, however, I would make a back-up copy and do the conversion on that just to be sure that things work okay.

Tom

 

[faxpay]

THWatson,

Changed fields to date, no help.

faxpay, Tom