complex query

[cahickm2]

I am having trouble building a query and am in need of some assistance. The guy that created the database I am try to work with died so I have no one around to help. Essentially this query will be used to compare data. It will compile information from 4 different tables.

Request
RequestNo -----(1)----->
RequestPONo---(2)----->

RequestDetail
ItemRequestNo <----(1)----
ItemQuantity
ItemPrice

Invoices
PONumber <-----(2)------
InvoiceID ------(3)----->

InvoiceDetail
InvoiceID <----(3)-----
Amount


First, It will go to the request table and pull in RequestNo and RequestPONo.
Then, It will pull ItemQuantity and ItemPrice based on the RequestNo from the Request Table.
Then, It will pull data from the Invoices table based on the RequestPONo from the request table.
Finally, pull data from Invoice detail table based on InvoiceID from the invoice table.

Im not sure how to do this, or if it can be done.

Thanks

 

[ggranger007]

You have to set up relationship joins (go to Tools -> Relationships) to let Access know how the tables sync up with one another. Then run a New Query Wizard. You can create joins using the numbers you have shown.

You could also pull in the PO number into each of your data tables, if it is available in the system you are using and base the join off of that.

I would definitely head off to the bookstore, if you have never used Access.

 

[giacomo]

I'm wondering if the issue that you're having is that you may have a request that has not yet been invoiced, if you do and you wish to see all requests with and without invoices then you need an outer join. When you pull your tables into the query builder and join the fields you can right-click on the join to change its properties. This will present you with the option to show only those records that exist in both joined tables, or all the records in one or the other regardless of a match. If you choose one of the latter options then the join will be represented as an arrow from one table to the other.

hth,
Giacomo