How to set this query and report among four tables?

  • Thread starter Thread starter smith
  • Start date Start date
S

smith

Thare are Table 1, Table2, Table3 that contain three types of items.

There is Table 4 that are sold items which are included in the above three
tables.

I want a query and report that shows un-sold items (Table
1+Table2+Table3-Table 4).

could you pls tell me how to do it? thanks in advance.
 
Thare are Table 1, Table2, Table3 that contain three types of items.

There is Table 4 that are sold items which are included in the above three
tables.

I want a query and report that shows un-sold items (Table
1+Table2+Table3-Table 4).

could you pls tell me how to do it? thanks in advance.
Click to expand...
Not without knowing more about the structure of the tables. If Table1,
Table2 and Table3 have identical structures, just different table
names, then you should reconsider your design! Storing data in
tablenames is NEVER a good idea. If you start selling a fourth type of
item would you add a new table, redesign all your forms, all your
queries, all your code? Ouch!

Even if the tables have different structures, you should still
consider having a master table of all items (related one-to-one to
three tables with the fields specific to that type of item). This is a
rather advanced technique called "Subclassing".

With your current design, you will need to create a UNION query
splicing together tables 1, 2 and 3:

SELECT ItemID, <other fields> FROM Table1
UNION ALL
SELECT ItemID, <other fields> FROM Table2
UNION ALL
SELECT ItemID, <other fields> FROM Table4;

Save this as uniAllItems; then create a second query

SELECT uniAll.ItemID, <other fields>
FROM UniALl LEFT JOIN Table4
ON uniAll.ItemID = Table4.ItemID
WHERE Table4.ItemID IS NULL;

This "unmatched" query will find all records in uniAll (i.e. in all
three tables) which do NOT have a corresponding record in Table4.

John W. Vinson[MVP]
 
Thank you very much. I am really a new hand in Access. But "three tables" is
just in my mind, which has not yet been built up.

What is your suggestion on buiding up one table that could be sorted in
three fields? To make three Queries that can be easily runned? Please give
me detailed instructions.

And if all these items are in one table, how to creat the specific query
then?

Thanks again!
 
smith said:
Thare are Table 1, Table2, Table3 that contain three types of items.

There is Table 4 that are sold items which are included in the above three
tables.

I want a query and report that shows un-sold items (Table
1+Table2+Table3-Table 4).

could you pls tell me how to do it? thanks in advance.
Click to expand...

the other bloke is right
you use 1 table for this

set a field called Sold
you can then run a query against it to just display the sold/unsold items
SELECT * from items WHERE sold = yes
SELECT * from items WHERE sold = no

do that, then add a field for item types
make it a number type, and use 1, 2, 3 depending on the item type
(previously where you would put them in each table)
 
Thank you very much. I am really a new hand in Access. But "three tables" is
just in my mind, which has not yet been built up.

What is your suggestion on buiding up one table that could be sorted in
three fields? To make three Queries that can be easily runned? Please give
me detailed instructions.
Click to expand...

Be careful using the term "Sort". It has a very specific meaning in
database terms: it means "take a set of records and present them in
some specific sequential order", as in to sort the list alphabetically
by last name, or sort the list in descending order of salary. You are
apparently using the term to mean filter records, or select records.
That will just confuse people!
And if all these items are in one table, how to creat the specific query
then?
Click to expand...

If you have three classes of products, simply include a ProductClass
field in your Table. Create a Query based on the table; on the
Criteria line under ProductClass you can put

"Widgets"

to select just the class of Widgets from the table. The resulting
query can be used just as if it were a table; you can view it, edit
it, use it as the basis of a form, and so on.

Or, you can get one step more advanced; put

[Enter product class:]

on the criteria line. When you use the query (say, by opening a form
or report based on it) you will be prompted with the text in the
brackets - the "Parameter" - and if you type Widgets in response,
you'll again get just that class.

Or, even more useful but a bit more work: create a little unbound form
named frmCrit. Put a Combo Box on it, named cboClass, with the three
(or more) product classes as options. Use a criterion of

[Forms]![frmCrit]![cboClass]

and put a button on frmCrit to open a Report or Form displaying the
desired data. The query will use whatever value the user selected to
filter the table and display those results.

John W. Vinson[MVP]
 
Back
Top