Query Sales 2

[johnny45]

I need to query the accpac sales and sales details tables.

We are on the latest accpac windows with pervasive 9

I need to build an sql query within a date range grouping sales by cust and item.

The data I saw stored in the pervasive table is formated like this: 20071230

How would I build the sql with a date range ?

 

[ettienne]

Select * from table where transdate >= 20070101 and transdate <=20071231

 

[johnny45]

Thank You !
I've never worked dates as strings before....

I will give this a try Monday...

sSql = "SELECT *" & vbNewLine
sSql = sSql & "FROM table" & vbNewLine
sSql = sSql & "WHERE transdate >=' " & Format(start_date, "yyyymmdd") & "' and transdate <='" & Format(end_date, "yyyymmdd") & "'"

 

[Jay Converse]

They're not strings, they're numbers, take out the quotes.

 

[johnny45]

So I have this so far..but I cant seem to get the grouping ...

SELECT OEORDH.CUSTOMER, OEORDH.ORDNUMBER, OEORDH.SHIPTO, OEORDH.BILNAME, OEORDH.SHPNAME, OEORDH.ORDDATE, OEORDD1.ITEM, OEORDD1.DESC, OEORDD1.UNITPRICE
FROM OEORDH INNER JOIN OEORDD1 ON OEORDH.ORDUNIQ = OEORDD1.ORDUNIQ
WHERE OEORDH.ORDDATE  Between 20081001 And 20081031
GROUP BY OEORDD1.ITEM
ORDER BY OEORDH.CUSTOMER, OEORDH.ORDDATE, OEORDD1.ITEM, Sum(OEORDD1.QTYORDERED), OEORDD1.ITEM;

 

[Jay Converse]

What's your question?

 

[johnny45]

I cant seenm to get the GROUPING to work...

I want to sort by customer and have the sql total the units by ITEM

 

[Jay Converse]

CUSTOMER needs to be in the GROUP BY

 

[johnny45]

like this :

SELECT OEORDH.CUSTOMER, OEORDH.ORDNUMBER, OEORDH.SHIPTO, OEORDH.BILNAME, OEORDH.SHPNAME, OEORDH.ORDDATE, OEORDD1.ITEM, OEORDD1.DESC, OEORDD1.UNITPRICE
FROM OEORDH INNER JOIN OEORDD1 ON OEORDH.ORDUNIQ = OEORDD1.ORDUNIQ
WHERE OEORDH.ORDDATE  Between 20081001 And 20081031
GROUP BY OEORDH.CUSTOMER,OEORDD1.QTYORDERED
ORDER BY OEORDH.CUSTOMER, OEORDH.ORDDATE, OEORDD1.ITEM, Sum(OEORDD1.QTYORDERED), OEORDD1.ITEM;

 

[ettienne]

Are you looking for sales or for orders, because orders =! sales.
If you strictly want sales information then look at OESHDT or OESHHD.