Connecting Four Tables

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

Guest

I have the following four tables:

1) ALL Items sold in 2004 and each customer
2) ALL Items EVER sold that meet a certain criterion and their Components
3) ALL Components and their Descriptions
4) All Components and their Vendors

Here is what I want:

ALL Items Sold in 2004 That Meet That Criterion
The Customer
The Component
The Component's Description
The Vendor

I can't seem to write a query that connects the Items together correctly!

And I can't change the tables....!


Arrggghh... Spent Two days and still can't get it!
 
I can't see where your customer data is stored. You indicate that table 1
has the customer and items sold in 2004. You indicate that table 2 has all
items sold and components. Where are the customer records for those?

Also, does table 2 contain duplicate records for the items in table1?

Also, table 2 contains all items ever sold that MEET A CERTAIN CRITERIA.
What about the items sold that don't meet that criteria, where do they live?

I would almost guess that Items 1 through 4 are queries, not tables. A
query would limit the results as you have indicated below, not a table. If
that is the case, then you would need to tell us what your TABLES are.

Rick B
 
They're all tables in that they were all dumped from the accounting system
into Excel!

Table 1 is every line from every invoice sold in 2004- so it lists ITEM and
CUSTOMER.
* Some items can have more than one customer
* All customers have more than one item

Table 2 was pre-sorted - ITEMS that *may* run in a certain cost center. The
COMPONENTS for those ITEMS are listed as well.

Table 3 is a "menu" of ALL ITEMS and their DESCRIPTIONS.

Table 4 is a "menu" of ALL COMPONENTS and their VENDORS

I want a report of All items sold in 2004 that went through this COst
Center, their components, and their vendors. Sorted by customer.

* All Items in Table 1 are in Table 3
* All ITEMS in Table 2 are in Table 3
* All COMPONENTS in Table 2 are in Table 4
* SOME ITEMS in Table 1 are NOT in Table 2
* SOME Items in Table 2 are NOT in Table 1
(In other words, there were items sold in 2004 that did not hit this cost
center. There are items that can run through this cost center that did not
sell in 2004.)

Does this make any sense???

Thanks
 
Back
Top