expression in query

[klnmis]

I have two tables. One is items produced, the other is items sold. Each table
contains three fields. Item_nbr, Cs and Per.
I would like to make a query that shows the net effect for each item, each
period. I am looking for (cases produced - cases sold) for each period, for
each item.
How do I set up my data, queries to show information for each period? For
example; there may be production for an item in period 1 but no sales. I
would like to show 100-0=100 for that item. In period 3 there may be 80 cases
sold with no production for that period on that item. I would like to show
0-80 = (-80). In period 5 there may be 120 cases produced and 50 cases sold.
I would like to show 120 - 50 = 70.
Thank you in advance.

 

[tedmi]

This is the reverse of a classic mistake often made with relational
databases. Most new users err on the side of putting TOO MUCH data in one
table, instead of splitting it over multiple related tables. You have split
data that belongs in one table over multiple tables. Both misdesigns cause
all sorts of problems, such as the difficulty you're excperiencing in getting
some very simple information out of your database.

I recommend you set up your data in one table, with these columns:
Item_nbr, Per, CsMade, CsSold.
Create a COMPOUND PRIMARY KEY on Item_nbr and Per.
For fields CsMade and CsSold, set the default value to zero and disallow
nulls.

Now your query is simple:
SELECT Item_nbr, Per, CsMade, CsSold,
CsMade-CsSold AS CsNet FROM YourTableName

If for some reason you absolutely need to keep the tables separate, you’ll
need to do a FULL OUTER JOIN, which is not supported in Jet SQL. For a
far-from-trivial workaround, search for “full outer join†in this newsgroup.