complex query

cahickm2

Board Regular
Joined
May 14, 2007
Messages
101
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

ggranger007

Board Regular
Joined
Jan 21, 2007
Messages
107
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

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,258
Messages
5,600,568
Members
414,389
Latest member
MarkElla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top