Is this possible

[ChrisLeicester]

Hi All

I am working on a POS/orders system and have a transaction main form, and transaction items subform.

I have a barcode reader which looks up the products. An example could be;

Duck, 1, £1.50
Duck, 1, £1.50
Chicken, 1 £2.00
Chicken, 1, £2.00
Chicken, 1, £2.00

Currently, my subform would list this as 5 records, and 5 lines on a continuous form.

I would like to change the subform to calculate and group the products, I.e.

Duck, 2, £3.00
Chicken, 3, £6.00

Very easy on a report, but not on a form as they don't do grouping.

How could this be achieved

Thanks

 

[theDBguy]

You can use a Totals query to group and sum but the data would be read only. Is that okay?

 

[ChrisLeicester]

Hi
Thanks,
I will look again at a totals query, I did try building a totals query but the results didn't produce what I expected.

Will play around and see

 

[Gasman]

Very easy on a report, but not on a form as they don't do grouping.

They do if you use a query, but then might have other issues as already mentioned?

 

[ChrisLeicester]

Hi
I.already have the form set up with also a subform that lists the products along.with a qty button and an additional button

I have an option to ammend price and qty once a supervisor has logged in so read only wont do.

The barcode reader was another input method.

Think I will work through some vba that looks to see if that product is already in the cart and if so then add 1 more qty and if not then make a new cart record.

That will achieve the desired results
Thanks

 

[The_Doc_Man]

An aggregate query could do both a count and a total at the same time, working from the detailed list of individual 1-item purchases.

SELECT ItemName, COUNT(ItemName) AS Qty,SUM(ItemCost) AS Cost FROM DetailedList GROUP BY ItemName ;

You would need another query or a couple of Domain Aggregate queries to get the sub-totals and grand totals.

 

[Mike Krailo]

Hi All

I am working on a POS/orders system and have a transaction main form, and transaction items subform.

I have a barcode reader which looks up the products. An example could be;

Duck, 1, £1.50
Duck, 1, £1.50
Chicken, 1 £2.00
Chicken, 1, £2.00
Chicken, 1, £2.00

Currently, my subform would list this as 5 records, and 5 lines on a continuous form.

I would like to change the subform to calculate and group the products, I.e.

Duck, 2, £3.00
Chicken, 3, £6.00

Very easy on a report, but not on a form as they don't do grouping.

How could this be achieved

Thanks

Why not scan one item and put in the quantity instead of scanning each individual item? Then the form would automatically group everything for you just like it does when you order something on Amazon. You select an item and enter the quantity of that item to purchase per transaction.

 

[GPGeorge]

Hi
I.already have the form set up with also a subform that lists the products along.with a qty button and an additional button

I have an option to ammend price and qty once a supervisor has logged in so read only wont do.

The barcode reader was another input method.

Think I will work through some vba that looks to see if that product is already in the cart and if so then add 1 more qty and if not then make a new cart record.

That will achieve the desired results
Thanks

During the development of the Northwest Developer template, we looked at Stocktake activities. In a video I did for YouTube, I talked about the two different approaches to recording stocktake, particularly when you have a barcode scanner.

In summary, I came to the conclusion that the most efficient approach would probably be to scan each item on the shelf individually, and let the application handle the aggregation for reports. If you have people manually counting each item, you add the human fallibility factor, i.e. miscounting, forgetting, mistyping the number of items.

While it involves more scan actions, one per item, I think the advantage lies at least slightly with that approach due to the lack of human variability.

Ultimately, though, this is one of those choices that has no automatic answer.

I suggest you revisit the aggregate query as well. Such queries can be tricky, for sure. However, it's a staple of relational database reporting.

 

[cheekybuddha]

I have a barcode reader which looks up the products

What is the code behind this operation?
You could adjust it to see whether there is already a similar item and then just adjust the quantity instead of adding a new item.

But it would require your tables to be set up correctly too

 

[GPGeorge]

What is the code behind this operation?
You could adjust it to see whether there is already a similar item and then just adjust the quantity instead of adding a new item.

But it would require your tables to be set up correctly too

I like that strategy, too. It also avoids the hazard of relying on the StockTaker to hand count items (accurately) and type the number in (accurately) for each product.

 

[CJ_London]

avoids the hazard of relying on the StockTaker to hand count items (accurately

Depends on what you are counting- a shelf stacked 5 items high by 4 deep by 10 wide? You really expect the user to effectively unload the shelf to scan each item?

I’ve built several stocktake modules over the years and the best option in my mind is to give the user both methods - where the user can either keep scanning or they can enter a calculation, might be just a number or a simple calculation

 

[GPGeorge]

Depends on what you are counting- a shelf stacked 5 items high by 4 deep by 10 wide? You really expect the user to effectively unload the shelf to scan each item?

I’ve built several stocktake modules over the years and the best option in my mind is to give the user both methods - where the user can either keep scanning or they can enter a calculation, might be just a number or a simple calculation

Like you say, it depends on the specific circumstances. Inventorying lawn-mowers in a garden shop is quite different from counting cans of corn in a warehouse.

 

[Pat Hartman]

I also like the idea of being flexible. But, I think in order to accumulate the way you are asking to, you need to scan into an unbound control rather than directly to the subform, then your code will either insert a new row and requery the subform or update an existing row. I think this should be fast enough to not interfere with the data entry.

I'm not sure of the best way to actually code this. You don't want the user to have to do anything except scan the item. You don't want him to have to press a button also. But, you also have to be careful to make sure the form doesn't close without saving the last scanned item when you are scanning into an unbound control so you may actually have to scan into a bound control on the main form to ensure that Access doesn't help you and inadvertently loose the last scan. At least that way, if you end up in the main form's BeforeUpdate event with a value in the scanned field, you can insert or update a child record as appropriate. I don't have a scanner so I can't experiment. I'll leave it to someone else

 

[Mark_]

Why not scan one item and put in the quantity instead of scanning each individual item? Then the form would automatically group everything for you just like it does when you order something on Amazon. You select an item and enter the quantity of that item to purchase per transaction.

Having worked retail, this can be really really slow. For some reason people don't like putting all of the same item together. Just scanning them can be far faster than trying to group and count all of the same time.

 

[Mark_]

Hi All

I am working on a POS/orders system and have a transaction main form, and transaction items subform.

I have a barcode reader which looks up the products. An example could be;

Duck, 1, £1.50
Duck, 1, £1.50
Chicken, 1 £2.00
Chicken, 1, £2.00
Chicken, 1, £2.00

Currently, my subform would list this as 5 records, and 5 lines on a continuous form.

I would like to change the subform to calculate and group the products, I.e.

Duck, 2, £3.00
Chicken, 3, £6.00

Very easy on a report, but not on a form as they don't do grouping.

How could this be achieved

Thanks

If this was me, I'd have the parent form take the input.

As each scan is accepted, do a lookup based on parent ID and the looked up product ID in your child record.
If the lookup succeeds, it increments the qty by one and updates the record. If it fails it creates the record with a qty of 1.

What is the lag time for your scanner and does it append a CR/LF at the end?

 

[Gasman]

Having worked retail, this can be really really slow. For some reason people don't like putting all of the same item together. Just scanning them can be far faster than trying to group and count all of the same time.

I hate it when I buy six items and have to scan 6 times on those self service tills.
With manned tills I just say 6 of whatever.

 

[Mark_]

I hate it when I buy six items and have to scan 6 times on those self service tills.
With manned tills I just say 6 of whatever.

If you have six of the same item, I agree, unless you "need to place the item in the bagging area" before going to the next.

 

[The_Doc_Man]

I'm not disagreeing with anyone here, but Mark raised an important point. What are the business rules regarding scanning? If you make the wrong choice, you have the tail wagging the dog. Never a good thing.