Relationships and pulling data.

[Subhunter]

Please see attached for Relationship Design.



I am setting up an Invoice Log to generated Invoice numbers and show what items have been shipped. This is not for billing.

I have set up a Query and Form for Invoices Log, Order Line Items and Orders.PurchaseOrderNumber. This works just fine as is. My quandry is that I need to use the Order Details.UnitPrice instead of the Order Line Items.UnitPrice. This way I can match up the unit price in the order with what we're invoicing. We need to do this in order to match up the correct price with what we charged.

I have hit a wall in trying to tie these together. Any suggestions would be most helpful. Is this something easy to do or am I looking at something more in depth that is outside my expertise?

Thanks,

Jeff

[NTC]

The InvoicesLog table need only be joined to the OrdersTable, in terms of relationships.

Because Orders has relationships to Details, Customers, etc....you will be able to pull in the data from these other tables in a form's record source when you make that query.

Categories-Products-OrderLineItems : look to me to be inventory. I don't see why an 'InvoiceID' would be in inventory; that should be in the sales area (Orders-Details-Log)

But it is only a cursory look, and difficult to give overly precise advice on something like this via a forum.

Hope this helps a little.

[Subhunter]

NTC,

Thanks. I think I got it. Now I have to find fixes for filtering the data that I need.

Jeff