Field Totals, Spining my head

[darno]

Hi Folks,

First of all i apologise for posting double, but i m compelled. I badly need help. i m not an expert like you guys. I hope someone will surely come to my rescue.

I have a problem with summing values in a query that is based on 5 tables.

In my form i have a text box which shows the total quantity on hand. The formula comes from all 5 table fields. for example:

GOODS RECEIVED GOODS SENT REPAIR RECEIVED REPAIR SENT ISSUES
Trx_Qty Trx_Qty1 Recd_Qty Sent_qty Issued_Qty

The formula works this way:
Expr1: [trx_qty]+[recd_qty]-[Trx_Qty1]-[Sent_qty]-[Issued_Qty]

When the records are limited to one row only then all works as desired, but as soon as i add more records in REPAIR RECEIVED Table and REPAIR SENT Table i get multiple rows in the form. Also the sum is not correct. For example:

GOODS RECEIVED GOODS SENT REPAIR RECEIVED REPAIR SENT ISSUES
Trx_Qty Trx_Qty1 Recd_Qty Sent_qty Issued_Qty
40 10 5 10 20

In the above example the formula gives following result

Expr1: 40 + 5 – 10 – 10 – 20 = 5
Expr1 = 5 (This is quantity on hand)

Bu now when I add more rows to other tables I get multiple rows and incorrect sum. Example

GOODS RECEIVED GOODS SENT REPAIR RECEIVED REPAIR SENT ISSUES
Trx_Qty Trx_Qty1 Recd_Qty Sent_qty Issued_Qty
40 10 5 10 20
5

I m attaching the database for you to have a look at it and come up with the solution.

Please help me I m badly stuck

I tried using group by and sum functions but no use

[Sumoftrx_qty]+[recd_qty]-[Trx_Qty2]-[Sent_qty]-[Issued_Qty]

Please Help me out of this mess. It will be highly appreciated

i will b gr8ful

Thanx once again for investing ur time in reading

Note: Expr1 formula may be different in database as quoted here on this forum due to trial and run.

Regards

Darno

 

[stopher]

Hi

First of all I think you have some problems with your database structure…

For instance, what is Seq_Number in TBL_ISSUE_TRX_DTL? From your relationship diagram it appears to be a stock item (I’ll call it product for clarity) identifier since it is related to Stock Master Full.

Is there any reason why you can’t use Part_No as your PK in Stock_Master_Full? I appreciate this is a questionable decision and you may have good reason to apply your own code.

Also, there is no PK defined in TBL_ISSUE_TRX_DTL and other tables.

You have linked TBL_Issue_Trx_Hdr and Tbl_Issu_Trx_Dtl using Issue_Doc_Type and Issue_Voucher_Number. Is this really necessary? Will you really have a Q,999 and say an R,999 or will each voucher have a new sequence number regardless of type. There are good reasons to have multiple field relationships but I’m not convinced that this is the case here.

The relationship between Stock Master Full and Tbl_Pl_GRN_Dtl is unnecessary I think because it should be inferred Tbl_Pl_Line_Items. Actually I’m not really sure what you are trying to model with these tables so it’s difficult for me to comment.

What is PL_Seq_No in Tbl_Pl_Items? Again you link it to code number.

Typically with Hdr/Dtl type documents you would have a PK for the header (typically the document number), then you will need a PK for the Dtl. This can be a sequenced number but it is not your product number (because product could appear many times in different docs). So it tends to be an independent sequence number. The dtl will also contain the Header PK as its FK. The dtl will also contain the product number as a FK related to the PK in Stock Master Full.

There are other similar issues but I do think you need to go back to basics and think carefully about your design before attempting to solve your current problem

As to your problem, typically you could create a separate aggregate (group) query for each of your dtl tables (grouped by your “product”). This will ensure you are then able to looked at summed data as a single line per product. They you can do a join query using the aggregate queries as sources and the Stock Mater Full table.

Hth
Stopher

 

[The_Doc_Man]

darno, while you may have an urgent problem, it might not be the one you think. Search this forum for the topics "Inventory" and "On Hand" and "Stock" and such keywords related to an inventory database. We have had many threads posted on this topic. Many of those threads go quite deeply into the subject.

We appreciate your newness to this forum and to Access. One of the great strengths of the forum is that so many questions have already been answered already. Some questions are so common that we refer people to the Search function precisely because of the depth of exploration involved.

 

[darno]

Badly Need Help...

darno, while you may have an urgent problem, it might not be the one you think. Search this forum for the topics "Inventory" and "On Hand" and "Stock" and such keywords related to an inventory database. We have had many threads posted on this topic. Many of those threads go quite deeply into the subject.

We appreciate your newness to this forum and to Access. One of the great strengths of the forum is that so many questions have already been answered already. Some questions are so common that we refer people to the Search function precisely because of the depth of exploration involved.

I have checked the forum but none is similar to mine. Please just advice me one thing that on which field i may group and on which i may apply SUM.

Regards

Darno

 

[The_Doc_Man]

Unfortunately, my answer won't help you.

You may apply GROUP BY or SUM() to any field almost any time and almost any where. It is a matter of what you are trying to group and what you are trying to accumulate in a Sum.

Stopher commented that you have structural problems. These can prevent ANY placement of GROUP BY or SUM() from doing what you intended. My best advice is to look at Stoper's comments carefully, fix the problems he has mentioned, amd try again.

 

[darno]

STOPHER and All Those Who Helped May GOD Bless You...

Unfortunately, my answer won't help you.

You may apply GROUP BY or SUM() to any field almost any time and almost any where. It is a matter of what you are trying to group and what you are trying to accumulate in a Sum.

Stopher commented that you have structural problems. These can prevent ANY placement of GROUP BY or SUM() from doing what you intended. My best advice is to look at Stoper's comments carefully, fix the problems he has mentioned, amd try again.

The comments from stopher and The Doc Man are really helpful, I have taken a deep look at the table structures and relationships. Well as far as table structures are concerned, i used seq number field as code number just for a change (Foolish Mistake). But it had the same data as i have in code number. Now for consistency i have changed the table structures as per Stopher's Instructions. For relationships, i m really stumped, its really hard for me to figure out which one to declare primary key in PL_GRN_DTL table, secondly how to connect the table to TBL_PL_LINE_ITEMS so that i could have same code number appearing as many times as i wish to. Coz the code number is an entry which may be repeated several times depending upon the items received after repairs. For example if i have sent 10 items for repair and i receive 3 items on sunday and then 2 more on monday and 5 on next sunday then it must show 3 records of same code number but different line numbers. This is whats not happening. It only accepts one record the way the tables are connected now. but when i finish the relationship it accepts multiple entries of the same code number but now the quantity on hand formula gets disturb and shows wrong sum of all values and shows double entry of the same item in the main form. It must not show the same code number in stock list form, but it must show the total sum against each code number.

Any Ideas???


Any Help will be gr8, plz take a look at my DB, and do any possible structuring that you may think about, i m heavily thankful to STOPHER for helping me in getting this much close. I really appreciate your sicerity and effort. Please help me to rectify this problem.

Regards


Darno