Help with reporting...order details

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm designing an order entry db for my small business. My client requires an
daily order file to be sent to them in excel, but limits each order to a
maximum of 6 products. I can get the report to export - no problem. But they
want one record for each order indicating (up to) 6 products (SKU, Qty and
Price) in one row. I created customer, order, order details and products
tables. Is there a way to get all order details associated with a single
order on a single row in a query or report?
 
Is there anyway you can take the time to type in some records and the
expected output format?
 
Thank you for your reply. I do have the format that the client is requiring.
Can I send this to you?
 
Sorry, here are the fields that the client needs sent to them in excel
format. The fields of SKU, Qty, Unit Price are in the order details table.
I have attempted having these fields in the order table as SKU1, SKU2, etc...
but the problem with that is reporting is rather cumbersome, as a single SKU
could appear in any of the 6 SKU fields.

These are all column headers:
ContactFirstName
ContactLastName
CompanyName
ShipAddress
ShipCity
ShipState
ShipPostalCode
PhoneNumber
Ship Via
OrderID
SKU
Quantity
UnitPrice
SKU
Quantity
UnitPrice
SKU
Quantity
UnitPrice
SKU
Quantity
UnitPrice
SKU
Quantity
UnitPrice
SKU
Quantity
UnitPrice
Credit Card #
CreditCardExpDate
Notes
Order Total
OrderDate
 
I would probably have a temp table of the structure below. Then open
recordsets of this table and your other tables. Loop through the order
details and append records to the temp table.
 
Back
Top