scanning barcodes on boxes ...& totalling all up in Access

[peskywinnets]

How would I approach scanning a barcode on a box & then totaling the result using access?

For example, let's say Product A comes in a box containing 5 individual sellable pieces. & Product B comes in a box containing 10 individual sellable pieces.

I have two boxes of product A & three boxes of Product B.

I want to be able to scan the barcode label on each of the 5 boxes & then see a report like this...

(the dots are just to format the info)

Product...TotalQuantity
Product A.........10
Product B.........30

I wouldn't even know how to approach the above.

I do already have a table that looks like this.

Product..............Barcode...........BoxQuantity
Product A........4135567234561............5
Product B........4135567234562...........10

So I'm figuring I need someway of counting up the number of times a barcode was scanned (therefore in the above example 4135567234561 would be input twice to access & 4135567234562 would be input three times to Access .....from then on it's simple multiplication against the table holding the BoxQuantity for each barcode ....but how would I count up the individual barcode entries?!)

 

[arnelgp]

it would be:

SELECT product, barcode, Sum(BoxQuantity) As SumOfBoxQty, Count(barcode) As CountOfBarCode From yourTableName GROUP BY product, barcode;

 

[peskywinnets]

Thanks...perhaps I didn't explain myself well.

I want to have a 'feature' within access that once run, Access then prompts a user in a way something like this....

"Begin scanning boxes"

....the user then starts scanning all the barcodes on the boxes & access keeps a tally. When finished, access knows how many boxes of each barcode was input & can therefore compute how many products in total were contained in all the boxes.

So taking this a small bite at a time...the first part...prompting the user & then access keeping tabs on the count barcodes scanned.....this presumably need a bit of VBA?

 

[Uncle Gizmo]

I think this thread might be of interest to you. It's a similar sort of problem, might give you some ideas.

http://www.access-programmers.co.uk/forums/showthread.php?t=282197

 

[peskywinnets]

There are probably a few ways to use an 'access hammer' to crack this particular nut.

Since posting I've had a dabble & I'm 90% of the way there, the process is

Assuming a table called Shipments (the name isn't important)

1. Delete all rows in the table called shipment.
2. Run a form to take user input & append to the table called shipment (two stages, scan those product in boxes first, then scan those products that are loose/singles)

I end up with a table like this



3. Now Run a 'sum' query on that table (this query also has another table that has the quantity per box for each product)

It all works a treat.....I end up with the correct totals.

However the onlt thing I'm not sure how to implement is the user input.

I first need to prompt the user "Scan boxes first" ....so now off to work out how append to the table called 'shipments' from user input?

 

[peskywinnets]

Ok, my first use of form wizard has got me where I wanted to be ....happy days

thanks for all your input.