This article provides a custom SQL to generate a report of items purchased by a certain customer.
Go to Custom Query Report on the File menu. The Manual SQL Form displays.
Copy and paste the following SQL command into the form.
select C.custfname,C.custlname,C.custphone1,A.itemdesc,A.itemqty,A.itembarcode,B.posdate,A.itemunit
,B.possaleid from lineitems A
inner join possales B on B.possaleskey=A.possaleskey
inner join customer C on C.custkey=B.custkey
where C.custid = 1945
and B.posdate between '1/1/2010' and '12/31/2010'
and B.posoring = 'F'
order by B.posdate
The following instructions allow you to customize this command to fit your needs:
In the 4th line from the end, change the custid from 1945 to
the desired customer number.
In the 3rd line from the end, change the date range as desired.
The report generated from this SQL statement will sort by date purchased. If you prefer to sort the report a different way, you can replace "B.posdate" in the last line of the command with any of the following fields.
| To Sort by This: |
Substitute This: |
| First Name |
C.custfname |
| Last Name |
C.custlname |
| Home Phone |
C.custphone1 |
| Item Description |
A.itemdesc |
| Item Quantity |
A.itemqty |
| Barcode |
A.itembarcode |
| Sale Date |
B. posdate |
| Price |
A.itemunit |
Click "Execute" to generate the report.
The Field Selector screen displays. Select the fields for your report by clicking on the checkboxes. Click "Ok" and the report will display.
|
