Urgent Help Needed

[Jim Riley]

I urgently need to find a way to interrogate the Actinic orders database to extract some sales listings for certain of our products. Could someone help me with this?

The requirement is for a data dump to a spreadsheet (presumably using an Access query) which would list the following details about every product sale over a specified period (e.g. a month):

Company Name
Post code
Order date
Name of product
Quantity ordered
Sales value (including VAT)

The data in individual orders needs to be broken down as above - line by line (i.e. the query is not just a summary of an order).

It really is a data dump - every sales line of every product in every order over a specific time period. The only query variable I would need to change would be the time period (which will always be a month).

I can handle manipulating Access queries - if only someone could give me a helping hand to get at the base data from the various Actinic tables

Could anyone help me with this? Happy to pay

Jim

Using V7.5 MU

[pinbrook]

if is sales data you need then Jan's export prog may do the job for you

You can use this to choose fields to export, and it writes you a xls or csv

Maybe wait for Jan to reply or email her direct

[TraceyG]

Hi,

The links between the order tables are:

Order sequence number (Order table) - nOrderSequenceNumber (OrderDetail table)

InvoiceContactId (Order table) - ContactID for the invoice row (Person table)
DeliveryContactId (Order table) - ContactID for the delivery row (Person table)

Order Sequence Number (Order table) - OrderNumber (PaymentHistory table)

Company Name and Postcode are in the 'Person' table
Order Date and Order total (inc VAT) are in the 'Order' table
Name of Product and Quantity Ordered are in the 'OrderDetail' table

I hope this helps.

[Jim Riley]

Thanks Tracey for taking the trouble to provide the table info.

Actually I've just solved the problem - with the help of Mole End's One Stop order processing (advanced). An absolutely amazing app. it allowed me to select all the relevant fields, create relationships between the data and then export to excel. A project I feared might take me several weeks (numerous returns for government departments) has just taken me only four hours!

Many thanks Pat & Jan!

Jim