Count product code in invoice in table (1 Viewer)

Waxmann

New member
Local time
Today, 11:49
Joined
Aug 18, 2020
Messages
26
OK, so I have a table called Pass1

In the tbl I have a field called InvoiceNo and a field called ItemNo and a field called LineNo.
Explain: When a customer buys several items, each item is contained under the same InvoiceNo along with a unique LineNo.
Example: A customer buys the same item twice, along with a single item. In this case there would be 3 lineNo (1, 2, 3)

I want to generate a table that shows the InvoiceNo, ItemNo and count of how many times the same ItemNo appears for each InvoiceNo

Any help would be MUCH appreciated
 
Last edited:

plog

Banishment Pending
Local time
Today, 12:49
Joined
May 11, 2011
Messages
11,611
Example: A customer buys the same item twice, along with a single item. In this case there would be 3 lineNo (1, 2, 3)

I don't understand how your data is going into your table. Please repost how the above data is going into your table using this format:

TableNameHere
InvoiceNo, ItemNo, LineNo


Copy the above, change TableNameHere to your table name, then put the data below the field names, being sure to seperate each field with a comma.
 

Waxmann

New member
Local time
Today, 11:49
Joined
Aug 18, 2020
Messages
26
Pass1
InvoiceNo, ItemNo, LineNo
10100001, 11223, 1
10100001, 11223, 2
10100001, 88923, 3
10100002, 45124, 1
10100003, 11223, 1
10100003, 11223, 2
10100003, 11223, 3
Thanks
 

Micron

AWF VIP
Local time
Today, 13:49
Joined
Oct 20, 2018
Messages
3,476
I want to generate a table that shows the InvoiceNo, ItemNo and count of how many times the same ItemNo appears for each InvoiceNo
Actually, no you do not. That violates 2 basic principles of database normalization and/or best practice:
- you intend to store calculations
- item data is not an attribute of an invoice

so there should be a table for invoice records with one record per invoice. The line items should be in their own table, with tblInvoice PK (primary key) stored as the foreign key in tblInvoiceItems (or whatever you call these). You join the PK and FK fields if creating a query; you'd have a main form for invoice data and a subform for line item data, and link them by the PK / FK fields. The form controls do the calculations such as sum/count.

I think it might be a good idea for you to research database normalization.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:49
Joined
May 7, 2009
Messages
19,169
You only need to add Autonumber (ID) field to your table.
then Create a Query to generate the LineNo.
see Query1.
 

Attachments

  • Invoicing.zip
    19.1 KB · Views: 195

Waxmann

New member
Local time
Today, 11:49
Joined
Aug 18, 2020
Messages
26
Actually, no you do not. That violates 2 basic principles of database normalization and/or best practice:
- you intend to store calculations
- item data is not an attribute of an invoice

so there should be a table for invoice records with one record per invoice. The line items should be in their own table, with tblInvoice PK (primary key) stored as the foreign key in tblInvoiceItems (or whatever you call these). You join the PK and FK fields if creating a query; you'd have a main form for invoice data and a subform for line item data, and link them by the PK / FK fields. The form controls do the calculations such as sum/count.

I think it might be a good idea for you to research database normalization.
Actually, I do have all those tables. I have queried those tables and created Pass1.
Now I want to run a series of queries to massage the data in Pass1 into a table (like combining identical itemNo that appear on the same invoice and adding a count) that will eventually be exported and sent to a FTP site where it will be recovered by a different vendor.

Hope that clears it up. The final table will be exported to a static pipe deliminated text file.
 

Waxmann

New member
Local time
Today, 11:49
Joined
Aug 18, 2020
Messages
26
You only need to add Autonumber (ID) field to your table.
then Create a Query to generate the LineNo.
see Query1.
So I already have the lineNo What I need is;
Pass1
InvoiceNo, ItemNo, LineNo---INTO--- InvoiceNo, ItemNo, Quanity
10100001, 11223, 1---------------------10100001, 11223, 2
10100001, 11223, 2---------------------10100001, 88923, 1
10100001, 88923, 3---------------------10100002, 45124, 1
10100002, 45124, 1---------------------10100003, 11223, 3
10100003, 11223, 1
10100003, 11223, 2
10100003, 11223, 3
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:49
Joined
May 7, 2009
Messages
19,169
create a Total (aggregate) query.

select InvoiceNo, ItemNo, Count(InvoiceNo) As Quantity From Pass1 Group By InvoiceNo, ItemNo;
 

Users who are viewing this thread

Top Bottom