T
thedrumdoctor
I'm currently testing a simple database that deals with stock taking. In
order to try and make my question a bit clearer, I have posted a screen shot
of the query result at the following URL:
http://www.adcdrums.com/eb/msa/query.jpg
I’ll take each column heading and explain how it was derived:
PaisteItem contains rows of product names. This has been populated by
another query which has concatenated records from a stock item table to
display a meaningful product name. Each record under this column has a unique
ID number on the stock item table.
Is This New? is taken from the table I am using to record stock items
counted and it simply identifies if an item is new or used. As you can see,
this query has asked for all items that are new.
Date Counted is taken from the table I am using to record stock items
counted and it records the date an item was counted. The query has produced
results for all items counted on 2nd September.
Quantity is taken from the table I am using to record stock items counted
and it records the number of items counted.
Here is a screen shot of the Query in design view:
http://www.adcdrums.com/eb/msa/query2.jpg
Because items of stock can be located in different parts of a storage area,
quantities of the same item can be recorded in no particular sequence, i.e.
you might find 3 of an item in one box and then another 10 in a different box
say, an hour later. Hence, why the same items appear in my example query with
different quantities. Just check out rows 3 & 4 and 6 & 7 in my first screen
shot as example items.
My goal is to take this query further so I can end up with a report that
will display each individual item and its total quantities. So, if the item
on rows 3 & 4 has a combined total of 5, then the final report will show that
result rather than 2 entries with different quantities.
I’ve tried a crosstab query but this doesn’t seem to work for me so
hopefully somebody will be able to see my work so far and point me in the
right direction. Thanks again for those looking in.
order to try and make my question a bit clearer, I have posted a screen shot
of the query result at the following URL:
http://www.adcdrums.com/eb/msa/query.jpg
I’ll take each column heading and explain how it was derived:
PaisteItem contains rows of product names. This has been populated by
another query which has concatenated records from a stock item table to
display a meaningful product name. Each record under this column has a unique
ID number on the stock item table.
Is This New? is taken from the table I am using to record stock items
counted and it simply identifies if an item is new or used. As you can see,
this query has asked for all items that are new.
Date Counted is taken from the table I am using to record stock items
counted and it records the date an item was counted. The query has produced
results for all items counted on 2nd September.
Quantity is taken from the table I am using to record stock items counted
and it records the number of items counted.
Here is a screen shot of the Query in design view:
http://www.adcdrums.com/eb/msa/query2.jpg
Because items of stock can be located in different parts of a storage area,
quantities of the same item can be recorded in no particular sequence, i.e.
you might find 3 of an item in one box and then another 10 in a different box
say, an hour later. Hence, why the same items appear in my example query with
different quantities. Just check out rows 3 & 4 and 6 & 7 in my first screen
shot as example items.
My goal is to take this query further so I can end up with a report that
will display each individual item and its total quantities. So, if the item
on rows 3 & 4 has a combined total of 5, then the final report will show that
result rather than 2 entries with different quantities.
I’ve tried a crosstab query but this doesn’t seem to work for me so
hopefully somebody will be able to see my work so far and point me in the
right direction. Thanks again for those looking in.