New comer to reports

[Guest]

I am using access 2003. I have a db created for our warehouse inventory
control, but when I enter things into the inventory transaction table & then
run the query it is listing each item separately instead of a total of the
item all together as a whole. So I thought I would run a report & maybe it
would total the same items together in it, but..........it don't.

I enter daily Ins & Outs into the Inventory Transaction form & there is a
lot of times there are multiple listings for the same product #, I need
something to combine the same product #'s together subtract some amounts &
add other to the opening bal for a grand total & then that grand total needs
to be the new opening balance for the next product with the same number.

Can ANYONE help me with this MAJOR problem that isn't to complicated to us
new beginners?

Thanks,

 

[Steve Schapel]

Shelian,

Make a query based on your Inventory Transaction table, and then make it
a Totals query (select Totals from the View menu). Add the Product #
field to the query design grid, and leave the default setting of 'Group
By' in the totals row. Add the other field(s) related to the Ins and
Outs and probably you need a Sum entered in the Totals row of the query
grid. I can't be too explicit here without knowing more details of the
fields and the way you are tracking ther transactions, but hopefully
this will point you in ht e right direction. It may be possible to get
the required data calculated in the query, and then just base your
report on the query.

Not directly related to your question, but you may find this article
helpful... http://www.allenbrowne.com/AppInventory.html

By the way, as an aside, it is not a good idea to use a # as part of the
name of a field.

 

[Guest]

Thank you I will try that. Also thanks for the warning about the # signs.

shelian1

 

[Guest]

This didn't help. On the report I picked the query from the transaction
sheet, but it is still showing each item separtely instead of as a whole.
Instead of in the total line putting "sum" I have the formula at the end of
the listings columns. So in other words I created another column for
"Subtotal, Total & Grand Total" is this not correct?

 

[Steve Schapel]

Shelian,

Can you please let us know what are the relevant fields in your table,
and some example data, and an example of what your required output to
the report should look like? Thanks, sometimes it is a lot easier to
deal with concrete examples rather than "concepts".

 

[Guest]

My inventory transaction table has the important fields of (plus other fields
but these are the important ones for right now):

Customer ID Product ID Description Units In Units Out
Pcs per Unit
(sample) 1485 26555 BB2464 2
250
1485 26555 BB2464
1 250
1485 26555 BB2464 4
112
1485 26555 BB2464 1
75

In a days "Ins & Outs" I could have at least 5 pages of product to enter
like above, what needs to be done is when I enter it into a form, I type in
the product ID & the description & other data get pulled from the other
tables, which is what I wanted it to do, but on a report I need it to gather
ALL the same product as one with a total after subtracting & adding the
above. My Opening Bal is on my product table, so when I run a query it pulls
the "Op Bal" from that table on all of the above instead of the balance AFTER
all the above is figured from the original opening balance. So in other
words if my opening balance is 12000 & by the time all of the "Ins & Outs"
are entered, the ending balance for that product should be: 12,773. I would
think on the table (or forms) it should figure the first enry for the product
ID that total should show up as the opening balance on the next item with the
same product ID number.

Now in my query for the inventory transaction I have the above fields plus
"Op Bal", "Subtotal", "Total", & "Grand Total". It is figuring it correctly
except not as a whole item, they are listed separately & all with the same
"Op Bal". I can e-mail you the database to look at if all this don't make
since.

 

[Steve Schapel]

Shelian,

It is not clear what you mean by "Subtotal", "Total", & "Grand Total" in
the query. It's not that you don't make sense, it's just that you are
not providing enough relevant information.

But if you want to process the "Op Bal" for each product with the total
transactions, you will need to do it in 2 steps. In other words,
calculate the total transactions first. Make a query based on the
Inventory Transactions table, make it a Totals Query (select Totals from
the View menu), leave 'Group By' in the Totals row or the query design
grid for the Customer ID and Product ID columns, and Sum in the column
for however you want to work out the transactions. This should then
give you the total units/pcs for each product for each customer. Then,
make another query, which uses this first query, plus the Products
table, joined on the Product ID field, and in this second query is where
you calculate based on the "Op Bal". Is that what you mean?

 

[Guest]

OK this is what I mean (I hope I can explain it better). In my products
table I have a field called "Op Bal". The "Inventory Transaction" table is
where I enter all the daily "Ins & Outs". I want the query &/or the report
to pull the "Op Bal" from the products table & then add or subtract what I
enter from that "Op Bal". I created the "Subtotal" field for the "Ins" the
"Total" field for the "Outs" & the "Grand Total" for the balance after it
figures if units were brought in or sent out. So in other words, the query
has "Op Bal" (from "Products" table), "Units In", "Units Out", "Pcs per
Unit", "Subtotal", "Total" & "Grand Total". The "Grand Total". Each line
may be different so I made it to do it this way & it would figure it either
adding or subtracting & still come up with the balance. Anyway, then I NEED
it to copy the "Grand Total" to the "Op Bal" for the next product with the
same Product ID number. Let's say for Product ID 23456 the op bal is 12000
we bring in 2 units of 250 pcs. Well then that gets added onto 12000 which
would make it 12500. Then the next entry is a different product ID. Two
entries later we send out 3 units of 250 pcs. Now it would take that away
from the 12500 which of course would be 11,750 left. So this figure should
go in the next entry for product ID 23456 when ever it may be, either the
next line or 2 days later. Please tell me you understand now. I am about to
quit trying to figure it out, I just want to SCREAM!!!

 

[Steve Schapel]

Shelian,

I am beginning to get the picture. The answer to your question is
relatively complex, and I can't answer fully right now. Unless somebody
else chips in, I will get back to you later. In the meantime, yes,
scream if you like - I know it often helps me.

 

[Guest]

OK I will wait for your answer. AHHHHHHHHHHHHHHH!! Better.

 

[Steve Schapel]

Shelian,

Ok, here's the problem... The database has been set up on spreadsheet
principles, not on database principles. If it was set up on database
principles, there wouldn't be any such thing as a "Op Bal" field in the
Products table. And there wouldn't be a Units In field and a Units Out
field, there would be a TransactionUnits field and a TransactionType
(in/out) field. Even so, getting a running inventory balance like you
are asking for, as far as I know, will need a subquery, or else a domain
function such as DSum() or DLookup(). In both these cases, if you have
a large number of records (which it sounds like you have), the query
will be noticeably slow. One option would be to export your table to
Excel, do your processing there, and then import back into a temporary
table in Access for use in your report. This is an attractive idea, but
not exactly a trivial undertaking - it will take you a while to get it
running sweetly, so a bit of scream control will be needed.

The other aspect is the concept of "the next entry". I am not sure what
you have in your Inventory Transaction table to identify the order of
transactions. A date and time for each transaction? A sequential
TransactionID number or some such? Whatever, you will have to use this
if you decide to try a subquery in Access. If you do this approach,
here is a starting point... I haven't tested this, and it assumes a
sequential TransactionID, but try putting like this in the Field row of
a blank column in the query design grid:
RunningBal: [Products].[Opp Bal] - (SELECT Sum((Nz([Units
In],0)-Nz([Units Out],0))*[Pcs per Unit]) FROM [Inventory Transaction]
WHERE [TransactionID]<[Inventory Transaction].[TransactionID] And
[Product ID]=[Inventory Transaction].[Product ID])

 

[Guest]

Thank you for saving my voice with all the screaming. I will try your
suggestions. Thank you for all your time & effort

Shelian,

Ok, here's the problem... The database has been set up on spreadsheet
principles, not on database principles. If it was set up on database
principles, there wouldn't be any such thing as a "Op Bal" field in the
Products table. And there wouldn't be a Units In field and a Units Out
field, there would be a TransactionUnits field and a TransactionType
(in/out) field. Even so, getting a running inventory balance like you
are asking for, as far as I know, will need a subquery, or else a domain
function such as DSum() or DLookup(). In both these cases, if you have
a large number of records (which it sounds like you have), the query
will be noticeably slow. One option would be to export your table to
Excel, do your processing there, and then import back into a temporary
table in Access for use in your report. This is an attractive idea, but
not exactly a trivial undertaking - it will take you a while to get it
running sweetly, so a bit of scream control will be needed.

The other aspect is the concept of "the next entry". I am not sure what
you have in your Inventory Transaction table to identify the order of
transactions. A date and time for each transaction? A sequential
TransactionID number or some such? Whatever, you will have to use this
if you decide to try a subquery in Access. If you do this approach,
here is a starting point... I haven't tested this, and it assumes a
sequential TransactionID, but try putting like this in the Field row of
a blank column in the query design grid:
RunningBal: [Products].[Opp Bal] - (SELECT Sum((Nz([Units
In],0)-Nz([Units Out],0))*[Pcs per Unit]) FROM [Inventory Transaction]
WHERE [TransactionID]<[Inventory Transaction].[TransactionID] And
[Product ID]=[Inventory Transaction].[Product ID])

--
Steve Schapel, Microsoft Access MVP

OK this is what I mean (I hope I can explain it better). In my products
table I have a field called "Op Bal". The "Inventory Transaction" table is
where I enter all the daily "Ins & Outs". I want the query &/or the report
to pull the "Op Bal" from the products table & then add or subtract what I
enter from that "Op Bal". I created the "Subtotal" field for the "Ins" the
"Total" field for the "Outs" & the "Grand Total" for the balance after it
figures if units were brought in or sent out. So in other words, the query
has "Op Bal" (from "Products" table), "Units In", "Units Out", "Pcs per
Unit", "Subtotal", "Total" & "Grand Total". The "Grand Total". Each line
may be different so I made it to do it this way & it would figure it either
adding or subtracting & still come up with the balance. Anyway, then I NEED
it to copy the "Grand Total" to the "Op Bal" for the next product with the
same Product ID number. Let's say for Product ID 23456 the op bal is 12000
we bring in 2 units of 250 pcs. Well then that gets added onto 12000 which
would make it 12500. Then the next entry is a different product ID. Two
entries later we send out 3 units of 250 pcs. Now it would take that away
from the 12500 which of course would be 11,750 left. So this figure should
go in the next entry for product ID 23456 when ever it may be, either the
next line or 2 days later. Please tell me you understand now. I am about to
quit trying to figure it out, I just want to SCREAM!!!