Revised Calculation

[John Paul Angala Pigao]

Here is an idea hoping from some help from you guys.

This is regarding my Inventory Database that i would like to implement in
our small office. Now i have a table for the Main Stock with Item ID, Item
Description, Beginning Balance, Qty on Hand. I also has another table called
Issue Voucher where qty will be deducted to the main stock. I also has
another table called Purchase Received where qty will be added to the main
stock.

Before i try to input a code in the Main Stock form On Current (Event
Procedure)

Me.QtyonHand = Nz(Me.Begbal, 0) + Nz(DSum("QtyIssued",
"tbl_PurchasedReceivedTransaction_Details", "ItemID = " & Me.ItemID & ""), 0)
- Nz(DSum("QtyIssued", "tbl_Transaction_Details", "ItemID = " & Me.ItemID &
""), 0)

But the problem is qty on hand will just be updated if i go to the main
stock form and look for the specific item. But if i make a transaction in the
Issue Voucher and go directly to the report qty on hand will not be updated.

Now i came up in this idea. Would it be possible to input the code above in
the Issue Voucher Form so that every time i make transaction qty on hand will
immediately be updated without going to the main stock form.

Please help me....

Thanks in advance.

John Paul Angala Pigao

 

[banem2]

Here is an idea hoping from some help from you guys.

This is regarding my Inventory Database that i would like to implement in
our small office. Now i have a table for the Main Stock with Item ID, Item
Description, Beginning Balance, Qty on Hand. I also has another table called
Issue Voucher where qty will be deducted to the main stock. I also has
another table called Purchase Received where qty will be added to the main
stock.

Before i try to input a code in the Main Stock form On Current (Event
Procedure)

Me.QtyonHand = Nz(Me.Begbal, 0) + Nz(DSum("QtyIssued",
"tbl_PurchasedReceivedTransaction_Details", "ItemID = " & Me.ItemID & ""), 0)
- Nz(DSum("QtyIssued", "tbl_Transaction_Details", "ItemID = " & Me.ItemID &
""), 0)

But the problem is qty on hand will just be updated if i go to the main
stock form and look for the specific item. But if i make a transaction in the
Issue Voucher and go directly to the report qty on hand will not be updated.

Now i came up in this idea. Would it be possible to input the code above in
the Issue Voucher Form so that every time i make transaction qty on hand will
immediately be updated without going to the main stock form.

Please help me....

Thanks in advance.

John Paul Angala Pigao

I am not sure you are using proper Event. I will rather use
AfterUpdate on specific field. This will activate your code and add
value to field QtyonHand. OnCurrent runs when you move focus from one
record to another.

However, I will also rather use SQL for this.

Regards,
Branislav Mihaljev, Microsoft Access MVP

 

[John Paul Angala Pigao]

I am not sure you are using proper Event. I will rather use
AfterUpdate on specific field. This will activate your code and add
value to field QtyonHand. OnCurrent runs when you move focus from one
record to another.

However, I will also rather use SQL for this.

Regards,
Branislav Mihaljev, Microsoft Access MVP

Thanks Branislav for the quick respose.

Are you talking about the AfterUpdate(Event Procedure) in the Main Stock form?
I tried this before but qty on hand will update if you look for the specific
item id. The reason why i put it on the On Current so that everytime the end
user look for the item id even thou the cursor is on different field qty n
hand will still be updated.
Now if i follow your idea, Does qty on hand will be updated if i make
transaction in the Issue Voucher qty on hand will be updated without looking
in the Main Stock form?

Let me also try this idea of yours again. What is this SQL idea that you
have? I will appreciate it if you can expalin to me or make more samples that
i can follow.

Hope to hear from you soon..

 

[banem2]

Thanks Branislav for the quick respose.

Are you talking about the AfterUpdate(Event Procedure) in the Main Stock form?
I tried this before but qty on hand will update if you look for the specific
item id. The reason why i put it on the On Current so that everytime the end
user look for the item id even thou the cursor is on different field qty n
hand will still be updated.
Now if i follow your idea, Does qty on hand will be updated if i make
transaction in the Issue Voucher qty on hand will be updated without looking
in the Main Stock form?

Let me also try this idea of yours again. What is this SQL idea that you
have? I will appreciate it if you can expalin to me or make more samples that
i can follow.

Hope to hear from you soon..

This looks a bit confusing to me. Actually I am not sure what do you
want. To make it simple: I think slow DSum function can help you.
Create unbound field with DSum function:

=DSum("[qty]", "myTable", "[IDArticle] = " & Me.IDArticle)

Also, if the qty "updates" on form and it won't appear on report as
proper value, you can use similar calculation you have in query which
is used as report record source (in this case you can keep your
calculation).

Idea is to sum up all transactions for two purposes: for the form and
for the report. Qty should be calculated always; if you have it as
field in table consider to remove it. Hope this helps.

Regards,
Branislav Mihaljev, Microsoft Access MVP

 

[John Paul Angala Pigao]

Thanks Branislav for the quick respose.

Are you talking about the AfterUpdate(Event Procedure) in the Main Stock form?
I tried this before but qty on hand will update if you look for the specific
item id. The reason why i put it on the On Current so that everytime the end
user look for the item id even thou the cursor is on different field qty n
hand will still be updated.
Now if i follow your idea, Does qty on hand will be updated if i make
transaction in the Issue Voucher qty on hand will be updated without looking
in the Main Stock form?

Let me also try this idea of yours again. What is this SQL idea that you
have? I will appreciate it if you can expalin to me or make more samples that
i can follow.

Hope to hear from you soon..

This looks a bit confusing to me. Actually I am not sure what do you
want. To make it simple: I think slow DSum function can help you.
Create unbound field with DSum function:

=DSum("[qty]", "myTable", "[IDArticle] = " & Me.IDArticle)

Also, if the qty "updates" on form and it won't appear on report as
proper value, you can use similar calculation you have in query which
is used as report record source (in this case you can keep your
calculation).

Idea is to sum up all transactions for two purposes: for the form and
for the report. Qty should be calculated always; if you have it as
field in table consider to remove it. Hope this helps.

Regards,
Branislav Mihaljev, Microsoft Access MVP

Branislav sorry for making you confused and giving you a head ache. Actually
you got my point on hand qty is just updating in the form and not in the
report. I want to have the on hand updating both ways on form and on the
report.

I will try this code
=DSum("[qty]", "myTable", "[IDArticle] = " & Me.IDArticle)

Will give you a feedback about the output.

Thank you sir.

 

[John Paul Angala Pigao]

Thanks Branislav for the quick respose.

Are you talking about the AfterUpdate(Event Procedure) in the Main Stock form?
I tried this before but qty on hand will update if you look for the specific
item id. The reason why i put it on the On Current so that everytime the end
user look for the item id even thou the cursor is on different field qty n
hand will still be updated.
Now if i follow your idea, Does qty on hand will be updated if i make
transaction in the Issue Voucher qty on hand will be updated without looking
in the Main Stock form?

Let me also try this idea of yours again. What is this SQL idea that you
have? I will appreciate it if you can expalin to me or make more samples that
i can follow.

Hope to hear from you soon..

This looks a bit confusing to me. Actually I am not sure what do you
want. To make it simple: I think slow DSum function can help you.
Create unbound field with DSum function:

=DSum("[qty]", "myTable", "[IDArticle] = " & Me.IDArticle)

Also, if the qty "updates" on form and it won't appear on report as
proper value, you can use similar calculation you have in query which
is used as report record source (in this case you can keep your
calculation).

Idea is to sum up all transactions for two purposes: for the form and
for the report. Qty should be calculated always; if you have it as
field in table consider to remove it. Hope this helps.

Regards,
Branislav Mihaljev, Microsoft Access MVP

Branislav Good day,

Just to inform you that in my form named Main Stock and i already has a
script or a code that will calculate all the issued and purchased qty for the
item id.

Here is the code:
Me.QtyonHand = Nz(Me.Begbal, 0) + Nz(DSum("QtyIssued",
"tbl_PurchasedReceivedTransaction_Details", "ItemID = " & Me.ItemID & ""), 0)
- Nz(DSum("QtyIssued", "tbl_Transaction_Details", "ItemID = " & Me.ItemID &
""), 0)

I place this in the Main Stock form On CUrrent (Event Procedure) so that if
the end user try to scroll the list of items qty on hand will be updated
automatically.

Now in the report named rpt_MainStock it is based on a query from the table
of the Main Stock which include Item ID, Item Description, Qty on Hand, Etc.
I try to input your code below

=DSum("[QtyonHand]", "tbl_Stock", "[ItemID] = " & Me.ItemID)

in the Query below QtyonHand, Criteria but i am having this error.

The expression you entered contains invalid syntax.

and when i press Ok or hit enter the cursor go to the comma after the
[QtyonHand]"

QtyonHand and ItemID are Numbers i defined in the tbl_Stock.

Hope to hear from you soon.

Thanks in advance.

 

[banem2]

This looks a bit confusing to me. Actually I am not sure what do you
want. To make it simple: I think slow DSum function can help you.
Create unbound field with DSum function:

=DSum("[qty]", "myTable", "[IDArticle] = " & Me.IDArticle)

Also, if the qty "updates" on form and it won't appear on report as
proper value, you can use similar calculation you have in query which
is used as report record source (in this case you can keep your
calculation).

Idea is to sum up all transactions for two purposes: for the form and
for the report. Qty should be calculated always; if you have it as
field in table consider to remove it. Hope this helps.

Regards,
Branislav Mihaljev, Microsoft Access MVP

Branislav Good day,

Just to inform you that in my form named Main Stock and i already has a
script or a code that will calculate all the issued and purchased qty for the
item id.

Here is the code:
Me.QtyonHand = Nz(Me.Begbal, 0) + Nz(DSum("QtyIssued",
"tbl_PurchasedReceivedTransaction_Details", "ItemID = " & Me.ItemID & ""), 0)
- Nz(DSum("QtyIssued", "tbl_Transaction_Details", "ItemID = " & Me.ItemID &
""), 0)

I place this in the Main Stock form On CUrrent (Event Procedure) so that if
the end user try to scroll the list of items qty on hand will be updated
automatically.

Now in the report named rpt_MainStock it is based on a query from the table
of the Main Stock which include Item ID, Item Description, Qty on Hand, Etc.
I try to input your code below

=DSum("[QtyonHand]", "tbl_Stock", "[ItemID] = " & Me.ItemID)

in the Query below QtyonHand, Criteria but i am having this error.

The expression you entered contains invalid syntax.

and when i press Ok or hit enter the cursor go to the comma after the
[QtyonHand]"

QtyonHand and ItemID are Numbers i defined in the tbl_Stock.

Hope to hear from you soon.

Thanks in advance.

For the report you can drop DSum and calculate QtyOnHand in query
which is used as record source for the report. If you need details on
the report, use Grouping and Sorting option and group data by ItemID,
turn on footer and place unbound text to sum up Items qty.

Regards,
Branislav Mihaljev, Microsoft Access MVP

 

[John Paul Angala Pigao]

On Apr 10, 11:36 am, John Paul Angala Pigao
:
On Apr 10, 8:16 am, John Paul Angala Pigao
Here is an idea hoping from some help from you guys.

This is regarding my Inventory Database that i would like to implement in
our small office. Now i have a table for the Main Stock with Item ID, Item
Description, Beginning Balance, Qty on Hand. I also has another table called
Issue Voucher where qty will be deducted to the main stock. I also has
another table called Purchase Received where qty will be added to the main
stock.

Before i try to input a code in the Main Stock form On Current (Event
Procedure)

Me.QtyonHand = Nz(Me.Begbal, 0) + Nz(DSum("QtyIssued",
"tbl_PurchasedReceivedTransaction_Details", "ItemID = " & Me.ItemID & ""), 0)
- Nz(DSum("QtyIssued", "tbl_Transaction_Details", "ItemID = " & Me.ItemID &
""), 0)

But the problem is qty on hand will just be updated if i go to the main
stock form and look for the specific item. But if i make a transaction in the
Issue Voucher and go directly to the report qty on hand will not be updated.

Now i came up in this idea. Would it be possible to input the code above in
the Issue Voucher Form so that every time i make transaction qty on hand will
immediately be updated without going to the main stock form.

Please help me....

Thanks in advance.

John Paul Angala Pigao

I am not sure you are using proper Event. I will rather use
AfterUpdate on specific field. This will activate your code and add
value to field QtyonHand. OnCurrent runs when you move focus from one
record to another.

However, I will also rather use SQL for this.

Regards,
Branislav Mihaljev, Microsoft Access MVP

Thanks Branislav for the quick respose.

Are you talking about the AfterUpdate(Event Procedure) in the Main Stock form?
I tried this before but qty on hand will update if you look for the specific
item id. The reason why i put it on the On Current so that everytime the end
user look for the item id even thou the cursor is on different field qty n
hand will still be updated.
Now if i follow your idea, Does qty on hand will be updated if i make
transaction in the Issue Voucher qty on hand will be updated without looking
in the Main Stock form?

Let me also try this idea of yours again. What is this SQL idea that you
have? I will appreciate it if you can expalin to me or make more samples that
i can follow.

Hope to hear from you soon..

This looks a bit confusing to me. Actually I am not sure what do you
want. To make it simple: I think slow DSum function can help you.
Create unbound field with DSum function:

=DSum("[qty]", "myTable", "[IDArticle] = " & Me.IDArticle)

Also, if the qty "updates" on form and it won't appear on report as
proper value, you can use similar calculation you have in query which
is used as report record source (in this case you can keep your
calculation).

Idea is to sum up all transactions for two purposes: for the form and
for the report. Qty should be calculated always; if you have it as
field in table consider to remove it. Hope this helps.

Regards,
Branislav Mihaljev, Microsoft Access MVP

Branislav Good day,

Just to inform you that in my form named Main Stock and i already has a
script or a code that will calculate all the issued and purchased qty for the
item id.

Here is the code:
Me.QtyonHand = Nz(Me.Begbal, 0) + Nz(DSum("QtyIssued",
"tbl_PurchasedReceivedTransaction_Details", "ItemID = " & Me.ItemID & ""), 0)
- Nz(DSum("QtyIssued", "tbl_Transaction_Details", "ItemID = " & Me.ItemID &
""), 0)

I place this in the Main Stock form On CUrrent (Event Procedure) so that if
the end user try to scroll the list of items qty on hand will be updated
automatically.

Now in the report named rpt_MainStock it is based on a query from the table
of the Main Stock which include Item ID, Item Description, Qty on Hand, Etc.
I try to input your code below

=DSum("[QtyonHand]", "tbl_Stock", "[ItemID] = " & Me.ItemID)

in the Query below QtyonHand, Criteria but i am having this error.

The expression you entered contains invalid syntax.

and when i press Ok or hit enter the cursor go to the comma after the
[QtyonHand]"

QtyonHand and ItemID are Numbers i defined in the tbl_Stock.

Hope to hear from you soon.

Thanks in advance.

For the report you can drop DSum and calculate QtyOnHand in query
which is used as record source for the report. If you need details on
the report, use Grouping and Sorting option and group data by ItemID,
turn on footer and place unbound text to sum up Items qty.

Regards,
Branislav Mihaljev, Microsoft Access MVP

Branislav thank you for the response. Now i tried your suggestion "turn on
footer and place unbound text to sum up Items qty" using the this code

Me.QtyonHand = Nz(Me.Begbal, 0) + Nz(DSum("QtyIssued",
"tbl_PurchasedReceivedTransaction_Details", "ItemID = " & Me.ItemID & ""), 0)
- Nz(DSum("QtyIssued", "tbl_Transaction_Details", "ItemID = " & Me.ItemID &
""), 0)

But error is coming up it should be staring with = only and syntax error as
well. I also tried this code

=DSum("[QtyonHand]", "tbl_Stock", "[ItemID] = " & Me.ItemID)

But same problem came up. Now i would appreciate more if you can give me an
idea with an example code. What proper code should i put in that unbound text?

Thanks for your patient and help.

 

[banem2]

Branislav thank you for the response. Now i tried your suggestion "turn on

footer and place unbound text to sum up Items qty" using the this code

Me.QtyonHand = Nz(Me.Begbal, 0) + Nz(DSum("QtyIssued",
"tbl_PurchasedReceivedTransaction_Details", "ItemID = " & Me.ItemID & ""), 0)
- Nz(DSum("QtyIssued", "tbl_Transaction_Details", "ItemID = " & Me.ItemID &
""), 0)

But error is coming up it should be staring with = only and syntax error as
well. I also tried this code

=DSum("[QtyonHand]", "tbl_Stock", "[ItemID] = " & Me.ItemID)

But same problem came up. Now i would appreciate more if you can give me an
idea with an example code. What proper code should i put in that unbound text?

Thanks for your patient and help.

Let's make it simple.

You have a report and table or query as record source. If it's table,
change it to SQL or query.

Now create query which will sum up quantity (turn on Totals) grouped
by Item. If this works, close and save SQL.

Now you have 2 fields to put on the report, no need for any coding.

If there are qty fields in 2 or more tables, add them all into query,
group by Item and sum by Qty (the same as with one table). Here you
won't need Sorting and Grouping with footer.

If you want more details on the report i.e. when you have query in
record source which cannot sum qty (like when you have date/time of
transaction there), you will need grouping by Item on the report. In
this case in Item footer you will have unbound field with following
formula:

=Sum(qty_field)

You priority is to have SQL/query which returns data you want. When
this works, easy part is to create report. Usually you won't need to
code summing on the report using VBA code.

Let me know if you still have issues with this. If its possible, you
can send me DB using some public file sharing Website and I will
create it for you to see how it should work.

Regards,
Branislav Mihaljev, Microsoft Access MVP

 

[John Paul Angala Pigao]

Branislav thank you for the response. Now i tried your suggestion "turn on
footer and place unbound text to sum up Items qty" using the this code

Me.QtyonHand = Nz(Me.Begbal, 0) + Nz(DSum("QtyIssued",
"tbl_PurchasedReceivedTransaction_Details", "ItemID = " & Me.ItemID & ""), 0)
- Nz(DSum("QtyIssued", "tbl_Transaction_Details", "ItemID = " & Me.ItemID &
""), 0)

But error is coming up it should be staring with = only and syntax error as
well. I also tried this code

=DSum("[QtyonHand]", "tbl_Stock", "[ItemID] = " & Me.ItemID)

But same problem came up. Now i would appreciate more if you can give me an
idea with an example code. What proper code should i put in that unbound text?

Thanks for your patient and help.

Let's make it simple.

You have a report and table or query as record source. If it's table,
change it to SQL or query.

Now create query which will sum up quantity (turn on Totals) grouped
by Item. If this works, close and save SQL.

Now you have 2 fields to put on the report, no need for any coding.

If there are qty fields in 2 or more tables, add them all into query,
group by Item and sum by Qty (the same as with one table). Here you
won't need Sorting and Grouping with footer.

If you want more details on the report i.e. when you have query in
record source which cannot sum qty (like when you have date/time of
transaction there), you will need grouping by Item on the report. In
this case in Item footer you will have unbound field with following
formula:

=Sum(qty_field)

You priority is to have SQL/query which returns data you want. When
this works, easy part is to create report. Usually you won't need to
code summing on the report using VBA code.

Let me know if you still have issues with this. If its possible, you
can send me DB using some public file sharing Website and I will
create it for you to see how it should work.

Regards,
Branislav Mihaljev, Microsoft Access MVP

Good day.

Branislav here's what i have done. Just to let you know i am using Office 2003

I took the tbl_Stock and make a query out of it then saved as qry_Stock
Query. Have it summarized and get the sum of QtyonHand. Now good it gives me
the output. Now i open the qry_Stock Query again add QtyIssued under
tbl_PurchasedReceivedDetails and add QtyIssued under tbl_IssueVoucher. So
basically i added two QtyIssued from different tables inside that query.

By the way. I have Issue Voucher form and inside it is a Subform where you
will input multiple item's for a specific user. Everytime a user uses this
form qty is deducted from the QtyonHand of the Main Stock Form.

Also a Purchased/Received form and inside it is a Subform where you will
input multiple item's from a specific Supplier.
Everytime a user uses this form qty is added from the QtyonHand of the Main
Stock Form.

So let's go back.

Inside the query below QtyIssued from tbl_PurchasedReceivedDetails (total)
it is Group By and for the QtyIssued from tbl_IssueVoucher (total) is also
Group By.

Now I save it and try to make a report based fromt he qry_Stock Query well
it added all the qty it also give me two lines for evry item, one for the
correct QtyonHand and the Other for same line of information but it doubled
up the QtyonHand. So basically it didn't solve my problem. My goal is to have
the database calculate the QtyonHand after the Transactions has been made in
the Issue Voucher Form and Purchased Received Form without going to the Main
Stock form and look for the item before it updates it's QtyonHand.

Branislav would it be possible to have this Query calculate the QtyonHand
like this...

Main Stock Form

Item ID 190001
Beginning Balance 10
Qty on Hand 10


Issue Voucher Transactions

Item No. 190001
Qty Issued 1


Generate Stock List Report
Item No. 190001
Qty on Hand 9

Purchased Received Transactions

Item No. 190001
Qty Received 20

Generate Stock List Report
Item No. 190001
Qty on Hand 29

In the Query can we put like this. QtyonHand of Item xxxx =
tbl_IssueVoucherDetails.QtyIssued + tbl_PurchaseReceivedDetails -
tbl.MainStock.Begbal

So that everytime the user open the Report it will calculate the on Hand and
display the accurate Qty on Hand. Because right now it is not happening, what
is happening right now is that, User make transactions either Issue Voucher
or Purchased Received if i run the report Qty on Hand of the Items didn't
changed so what i will do, i will go to the Mian Stock form look for the item
id and then it will update the QtyonHand and now if i check the report the
QtyonHand is now updated.

This is the situation hope we can fix this the soonest.

Thanks in advance.

Best Regards,

John Paul Angala Pigao

 

[banem2]

Branislav thank you for the response. Now i tried your suggestion "turn on
footer and place unbound text to sum up Items qty" using the this code
Me.QtyonHand = Nz(Me.Begbal, 0) + Nz(DSum("QtyIssued",
"tbl_PurchasedReceivedTransaction_Details", "ItemID = " & Me.ItemID & ""), 0)
- Nz(DSum("QtyIssued", "tbl_Transaction_Details", "ItemID = " & Me.ItemID &
""), 0)
But error is coming up it should be staring with = only and syntax error as
well. I also tried this code
=DSum("[QtyonHand]", "tbl_Stock", "[ItemID] = " & Me.ItemID)
But same problem came up. Now i would appreciate more if you can give me an
idea with an example code. What proper code should i put in that unbound text?
Thanks for your patient and help.

Let's make it simple.

You have a report and table or query as record source. If it's table,
change it to SQL or query.

Now create query which will sum up quantity (turn on Totals) grouped
by Item. If this works, close and save SQL.

Now you have 2 fields to put on the report, no need for any coding.

If there are qty fields in 2 or more tables, add them all into query,
group by Item and sum by Qty (the same as with one table). Here you
won't need Sorting and Grouping with footer.

If you want more details on the report i.e. when you have query in
record source which cannot sum qty (like when you have date/time of
transaction there), you will need grouping by Item on the report. In
this case in Item footer you will have unbound field with following
formula:

You priority is to have SQL/query which returns data you want. When
this works, easy part is to create report. Usually you won't need to
code summing on the report using VBA code.

Let me know if you still have issues with this. If its possible, you
can send me DB using some public file sharing Website and I will
create it for you to see how it should work.

Regards,
Branislav Mihaljev, Microsoft Access MVP

Good day.

Branislav here's what i have done. Just to let you know i am using Office 2003

I took the tbl_Stock and make a query out of it then saved as qry_Stock
Query. Have it summarized and get the sum of QtyonHand. Now good it gives me
the output. Now i open the qry_Stock Query again add QtyIssued under
tbl_PurchasedReceivedDetails and add QtyIssued under tbl_IssueVoucher. So
basically i added two QtyIssued from different tables inside that query.

By the way. I have Issue Voucher form and inside it is a Subform where you
will input multiple item's for a specific user. Everytime a user uses this
form qty is deducted from the QtyonHand of the Main Stock Form.

Also a Purchased/Received form and inside it is a Subform where you will
input multiple item's from a specific Supplier.
Everytime a user uses this form qty is added from the QtyonHand of the Main
Stock Form.

So let's go back.

Inside the query below QtyIssued from tbl_PurchasedReceivedDetails (total)
it is Group By and for the QtyIssued from tbl_IssueVoucher (total) is also
Group By.

Now I save it and try to make a report based fromt he qry_Stock Query well
it added all the qty it also give me two lines for evry item, one for the
correct QtyonHand and the Other for same line of information but it doubled
up the QtyonHand. So basically it didn't solve my problem. My goal is to have
the database calculate the QtyonHand after the Transactions has been made in
the Issue Voucher Form and Purchased Received Form without going to the Main
Stock form and look for the item before it updates it's QtyonHand.

Branislav would it be possible to have this Query calculate the QtyonHand
like this...

Main Stock Form

Item ID 190001
Beginning Balance 10
Qty on Hand 10

Issue Voucher Transactions

Item No. 190001
Qty Issued 1

Generate Stock List Report
Item No. 190001
Qty on Hand 9

Purchased Received Transactions

Item No. 190001
Qty Received 20

Generate Stock List Report
Item No. 190001
Qty on Hand 29

In the Query can we put like this. QtyonHand of Item xxxx =
tbl_IssueVoucherDetails.QtyIssued + tbl_PurchaseReceivedDetails -
tbl.MainStock.Begbal

So that everytime the user open the Report it will calculate the on Hand and
display the accurate Qty on Hand. Because right now it is not happening, what
is happening right now is that, User make transactions either Issue Voucher
or Purchased Received if i run the report Qty on Hand of the Items didn't
changed so what i will do, i will go to the Mian Stock form look for the item
id and then it will update the QtyonHand and now if i check the report the
QtyonHand is now updated.

This is the situation hope we can fix this the soonest.

Thanks in advance.

Best Regards,

John Paul Angala Pigao

Hello John,

Can you send me DB with couple of dummy records on banem2 (at) gmail
(dot) com?

Regards,
Branislav Mihaljev, Microsoft Access MVP

 

[John Paul Angala Pigao]

Branislav thank you for the response. Now i tried your suggestion "turn on
footer and place unbound text to sum up Items qty" using the this code

Me.QtyonHand = Nz(Me.Begbal, 0) + Nz(DSum("QtyIssued",
"tbl_PurchasedReceivedTransaction_Details", "ItemID = " & Me.ItemID & ""), 0)
- Nz(DSum("QtyIssued", "tbl_Transaction_Details", "ItemID = " & Me.ItemID &
""), 0)

But error is coming up it should be staring with = only and syntax error as
well. I also tried this code

=DSum("[QtyonHand]", "tbl_Stock", "[ItemID] = " & Me.ItemID)

But same problem came up. Now i would appreciate more if you can give me an
idea with an example code. What proper code should i put in that unbound text?

Thanks for your patient and help.

Let's make it simple.

You have a report and table or query as record source. If it's table,
change it to SQL or query.

Now create query which will sum up quantity (turn on Totals) grouped
by Item. If this works, close and save SQL.

Now you have 2 fields to put on the report, no need for any coding.

If there are qty fields in 2 or more tables, add them all into query,
group by Item and sum by Qty (the same as with one table). Here you
won't need Sorting and Grouping with footer.

If you want more details on the report i.e. when you have query in
record source which cannot sum qty (like when you have date/time of
transaction there), you will need grouping by Item on the report. In
this case in Item footer you will have unbound field with following
formula:

You priority is to have SQL/query which returns data you want. When
this works, easy part is to create report. Usually you won't need to
code summing on the report using VBA code.

Let me know if you still have issues with this. If its possible, you
can send me DB using some public file sharing Website and I will
create it for you to see how it should work.

Regards,
Branislav Mihaljev, Microsoft Access MVP

Good day.

Branislav here's what i have done. Just to let you know i am using Office 2003

I took the tbl_Stock and make a query out of it then saved as qry_Stock
Query. Have it summarized and get the sum of QtyonHand. Now good it gives me
the output. Now i open the qry_Stock Query again add QtyIssued under
tbl_PurchasedReceivedDetails and add QtyIssued under tbl_IssueVoucher. So
basically i added two QtyIssued from different tables inside that query.

By the way. I have Issue Voucher form and inside it is a Subform where you
will input multiple item's for a specific user. Everytime a user uses this
form qty is deducted from the QtyonHand of the Main Stock Form.

Also a Purchased/Received form and inside it is a Subform where you will
input multiple item's from a specific Supplier.
Everytime a user uses this form qty is added from the QtyonHand of the Main
Stock Form.

So let's go back.

Inside the query below QtyIssued from tbl_PurchasedReceivedDetails (total)
it is Group By and for the QtyIssued from tbl_IssueVoucher (total) is also
Group By.

Now I save it and try to make a report based fromt he qry_Stock Query well
it added all the qty it also give me two lines for evry item, one for the
correct QtyonHand and the Other for same line of information but it doubled
up the QtyonHand. So basically it didn't solve my problem. My goal is to have
the database calculate the QtyonHand after the Transactions has been made in
the Issue Voucher Form and Purchased Received Form without going to the Main
Stock form and look for the item before it updates it's QtyonHand.

Branislav would it be possible to have this Query calculate the QtyonHand
like this...

Main Stock Form

Item ID 190001
Beginning Balance 10
Qty on Hand 10

Issue Voucher Transactions

Item No. 190001
Qty Issued 1

Generate Stock List Report
Item No. 190001
Qty on Hand 9

Purchased Received Transactions

Item No. 190001
Qty Received 20

Generate Stock List Report
Item No. 190001
Qty on Hand 29

In the Query can we put like this. QtyonHand of Item xxxx =
tbl_IssueVoucherDetails.QtyIssued + tbl_PurchaseReceivedDetails -
tbl.MainStock.Begbal

So that everytime the user open the Report it will calculate the on Hand and
display the accurate Qty on Hand. Because right now it is not happening, what
is happening right now is that, User make transactions either Issue Voucher
or Purchased Received if i run the report Qty on Hand of the Items didn't
changed so what i will do, i will go to the Mian Stock form look for the item
id and then it will update the QtyonHand and now if i check the report the
QtyonHand is now updated.

This is the situation hope we can fix this the soonest.

Thanks in advance.

Best Regards,

John Paul Angala Pigao

Hello John,

Can you send me DB with couple of dummy records on banem2 (at) gmail
(dot) com?

Regards,
Branislav Mihaljev, Microsoft Access MVP

Branislav,

Thanks for the big help and patient. Now the problem is solved and case is
closed.

Please keep it up and please keep on helping someone like me in learning
more about database.

God bless all and more power!

John Paul Angala Pigao