Can this be done with a query? (1 Viewer)

[Waxmann]

I'm looking for a query to look at the invoiceNo and when it finds two that are =, look at the plu's. If they are = then delete one row and increase the Qty to 2. If there are 3 that are =, just count the first 2 and leave the third row alone. If there are 4 that are = then delete three rows and change qty to 4

 

[Minty]

Short answer no. But, you can do a grouped query and sum the quantities.

To do the complex rules you have described want would require code. I'm puzzled as to why you would sum 2 or 4 the same but not 3?

 

[Dreamweaver]

I think you need to look at the stucture of your tables as there seems to be 5 problems I can see with that data set in your picture?

 

[Waxmann]

Short answer no. But, you can do a grouped query and sum the quantities.

To do the complex rules you have described want would require code. I'm puzzled as to why you would sum 2 or 4 the same but not 3?

This is a rebate program. They only give a rebate for 2 purchased at the same time (Buy2 save .50)
Know anyone who could write that code? - For a price?

 

[onur_can]

I'm not sure if this event can be done in the query, but I think it will be done with VBA code.

 

[Waxmann]

I think you need to look at the stucture of your tables as there seems to be 5 problems I can see with that data set in your picture?

? What are you looking at?

 

[plog]

You are going about this wrong. This isn't a spreadsheet its a database, treat it as such. Leave that table alone and just build a Totals Query:

Sum data by using a query

Learn how to use an aggregate function in Access to sum the data in a query result set.

support.microsoft.com

.

And reference that query when you want the quantity per invoice.

 

[Pat Hartman]

How are the duplicates getting into the table. I would fix that first.

 

[plog]

I can only get to 3 issues and one nitpick:

1--Two fields for quantity ([Quantity] & [Qty])

2-- Not having a product table. In a table that has items/invoice you only should have 1 field that links to the Products table, you are storing a bunch of other item related values that shouldn't be on this table ([FullDesc], [ItemSize])

3--Fields that share a prefix. I see 3 fields named [Multi-Unit...], most likely those fields with that prefix should be in their own table.

Nitpick-- Special characters in field names. Only use alphanumeric characters and underscores in field names ([Multi-Unit...] shouldn't have dashes.

 

[MajP]

Know anyone who could write that code?

Yeah, me. But that would be putting a band aid on a bigger issue. If you design this correct no code is needed. May want to look at the Northwind sample db for some ideas.

 

[Waxmann]

How are the duplicates getting into the table. I would fix that first.

There should be a lineItem# (Not copied in the example) . He purchased 2 packs of cigarettes and they were each scanned. Causing 2 lines on the invoice

 

[Waxmann]

You are going about this wrong. This isn't a spreadsheet its a database, treat it as such. Leave that table alone and just build a Totals Query:

Sum data by using a query

Learn how to use an aggregate function in Access to sum the data in a query result set.

support.microsoft.com

.

And reference that query when you want the quantity per invoice.

Please understand that this will be exported to a | delimited file and then submitted to a clearing house. This is the format they require.

 

[MajP]

If you needed to do something like this (and I am not suggesting anyone should), it would be pretty easy. If you had a table of inventories and PLUs like
tblData tblData

ID	Invoice	PLU	Qty
1​	1	123	1​
2​	1	123	2​
3​	2	333	3​
4​	2	333	1​
5​	2	333	2​
6​	1	234	3​
7​	2	444	1​
8​	3	555	1​
9​	3	666	2​
10​	3	555	2​
11​	3	555	2​
12​	1	456	2​

you can do an aggregate query to get the sums by plu and inventory where the record count is >1
qryInvPLU qryInvPLU

qryInvPLU qryInvPLU

Invoice	PLU	SumOfQty	CountOfQty
1	123	3​	2​
2	333	6​	3​
3	555	5​	3​

So you loop the above query and would delete all records with Invoice of 1 and PLU of 123 and then insert a new one for that invoice and PLU with the sum of the quantities

However, you should just export the query and not delete/add records to the original table IMO.

 

[plog]

That has nothing to do with the proper storage of data. Storage of data is completely separate from the delivery of data.

 

[Dreamweaver]

? What are you looking at?

1. You have dupilicate invoice numbers As ponted our by others
2. You have 2 Quantitys
3. As with invoice number you also have duplicate PLU but I don't know your bussiness rules
4. You store the product name "I prefer not to"
5. Item Size Does this really need to be stored As It would relate to each product not an invoice and should there be a Quantity I.E units per box
6. Not sure what Dept No is doing within the invoice but I don't know your bussiness rules.

 

[arnelgp]

if you are using a POS, then you are messing with the data.
leave the data as-is.
if you have tape printer then if you summarize your data like that,
the print-out and the actual data will not match.

each items on every transaction is valueable.

when the customer returns with the 2 packs of cigarette and you
check the customer's copy with the one you summarized it will not match
since you already tampered with it.

 

[namliam]

if you are using a POS, then you are messing with the data.
leave the data as-is.

Just to add my 2 cents, deleting data and/or messing with your source data is a matter of last last last last last resort.
Leave your data as is and summarize your data in a query as suggested, this is the way to go.

 

[CJ_London]

I agree with the other comments - this is source data and should not be messed with, use a query and export the query as a .csv

However your explanation of your requirement does not go far enough to write this query - what if there are 5? 6?, 10? purchases. What if the offer is 'buy 3'? From your example data what does the output actually need to look like? Use excel and provide it