asked on

Product Sales by Customer over date peroid

Hi Experts,

I am looking for an Access query that will give me sales data for a particular product to a particular customer over a particular time period.

The tables and relevant fields are:

1)dbo.tblWEEKLYSALES
fld.ProductID
fld.Qty
fld.CustomerNum
fld.BusinessDate

2)dbo.tblCUSTOMER
fld.CustomerNum

3)dbo.tblPRODUCT
fldProductID
fldDescription

So far I have:

SELECT dbo_tblCUSTOMER.fld.CustomerNum, dbo_tblWEEKLYSALES.fldProductID, dbo_tblPRODUCT.fldDescription, dbo_tblWEEKLYSALES.fldQty, dbo_tblWEEKLYSALES.fldBusinessDate

FROM (dbo_tblWEEKLYSALES LEFT JOIN dbo_tblPRODUCT ON dbo_tblWEEKLYSALES.fldProductID = dbo.tblPRODUCT.fldProductID) LEFT JOIN dbo.tblCUSTOMER ON dbo_tblWEEKLYSALES.fldCustomerNum= dbo.tblCUSTOMER.fldCustomerNum
WHERE (((dbo.tblCUSTOMER)="74831") AND ((dbo_tblWSALES.fldProductID)=" 4957"));

This is fine but I want to be able to return the sales over say the last 2 months. How might I do this with fldBusinessDate? I am sure this is relatively straightforward but I have done some searching and am out of time...

Thanks!
Chris

Rey Obrero (Capricorn1)

...
try this

WHERE (((dbo.tblCUSTOMER)="74831") AND ((dbo_tblWSALES.fldProductID)=" 4957") And dbo_tblWEEKLYSALES.fldBusinessDate Between Date() And DateAdd("m",-2,Date())

Rey Obrero (Capricorn1)

or

WHERE (((dbo.tblCUSTOMER)="74831") AND ((dbo_tblWSALES.fldProductID)=" 4957") And dbo_tblWEEKLYSALES.fldBusinessDate Between Date() And DateSerial(Year(Date), (Month(Date) - 2), 1))

Christar

ASKER

hmmmm.. this dosen't seem to be generating correct data. The results are blank but i know there have been sales over the last couple of months. Ill keep playing with it. Thanks

Christar

ASKER

Thanks Capricorn1,

When i the second suggestion it asks me to 'Enter Parameter Value" Date. Is there somewhere i should be using fldBusinessDate rather than just Date?

Cheers,
Chris

Rey Obrero (Capricorn1)

try

WHERE (((dbo.tblCUSTOMER)="74831") AND ((dbo_tblWSALES.fldProductID)=" 4957") And dbo_tblWEEKLYSALES.fldBusinessDate Between Date() And DateSerial(Year(Date()), (Month(Date()) - 2), 1))

Christar

ASKER

Again this query seems to execute fine but is not giving me any data when i know it exists.

Did I mention my date format is DD/MM/YYYY. Might this be relevant?

Christar

ASKER

sorry that was an error the date format is: MM/DD/YYYY

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Christar

ASKER

Cool thats seemed to do it! Thanks Capricorn! Out of curiosity why the need to format? Do I have my data in an awkward date format?

many thanks again for the assistance.

Cheers,
Chris

Rey Obrero (Capricorn1)

perhaps, time ? sometime you have to use brute force to equate the date/time field of access

Christar

ASKER

hahaha no wonder i couldn't figure it out. I am a bit weak in the Access world! Thanks!