Count product code in invoice in table

[Waxmann]

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

 

[plog]

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]

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

 

[Deleted member 147267]

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]

You only need to add Autonumber (ID) field to your table.
then Create a Query to generate the LineNo.
see Query1.

 

[Waxmann]

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]

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]

create a Total (aggregate) query.

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

 

[Waxmann]

Thanks, that worked!