multi column lookup single value

[davedinger]

I am building an inventory database that will allow me to scan products and end up with the total number of each product.

Table 1: Products
Purpose: Stores product Information
Field: Auto ID (number)
Field: Part Number (short Text)
Field: Description (short Text)
Field: MFG Number (short Text)
Field: Extra Number (short Text)

Table 2: Scans
Purpose: Records each scan
Field Auto ID (Number)
Field: Barcode Scan Number
Field: Quantity (Number Default value= 1)
Field: Date (Short Date)

So the way this works is that I scan a barcode on a product. It could be Our Part Number barcode or The Manufacturer Barcode or some other barcode we have on the product. That scan is recorded in the Scans table.

When done I run a report that shows all of the scans for the day along with the Product information for each of the scans.



My problem is that when I scan say a jar a pickles, I may scan our part number for one jar of them, and maybe the manufacturer number for the next jar, ect.
This means my report is showing two different totals for the pickles. One total for all of the jars scanned with the part number. And One total for all of the jars scanned as the Manufacturer number.

I need to figure out how to do two things with the report

1. The report has to be able to search all three fields Part Number, MFG Number, Extra Number

And then

1. Show the scan by the Part Number regardless if the Part Number, MFG Number, or Extra Number is scanned

[kd2017]

What about making a separate barcode table? This table has an ID, product ID, barcode type, barcode.

Anyway, I wouldn't think you'd store the scanned barcode, but rather a foreign key to the product or barcode.

[davedinger]

well maybe this will help,
The way this works is i scan a bar code on a product, the bar code is recorded in the Scans table with a default quantity of 1, then a next record command is given and its ready for the next scan. so the Scans Table is nothing more then a history of all of the scans that I have done. very quick and simple.

When I run the report it looks at the scan pat number and attaches the product information and then groups by part number with a total

This is the Scans Table

[kd2017]

Thank you for the explanation. You mentioned a few times about the report linking these tables together. That's the job of a query. Can you post the underlying query? Can you post your table relationship window?

Let me clarify what I meant as well:


The NumberTypes = Product No., Mfg No., Extra No., Etc.

The way this would work is you would scan a product number, you'd lookup the number in the PartNumber table, and save a record for the corresponding ID in the scans table (not the scanned code itself). This PartNumberID in the Scan table can be traced back to a distinct product. Your aggregate queries and reports then become very easy to build.

Here is an example query that would sum the quantity for each product scanned on 8/22/2018, regardless if the scans were product numbers, mfg numbers, etc:


Also be sure to google around for existing inventory model examples. like so: http://www.databaseanswers.org/data_...ales/index.htm

[davedinger]

First off Thank You for your reply, what you say about the scan look up is very interesting, I see where you are going with the scanned table but I'm afraid the look up of each product would take too long. I'm looking at over 400,000 product numbers. each with 3 possibilities. I need the scanning to be instantaneous, speed is key when scanning and I'm doing this on a low end Tablet. The report can take time since that is run after all the scans are done but the scanning portion cant wait for the lookup.

I think the main question i have is how do I get a report to search 3 seperate fields and then return a single corresponding answer.

In other words, say i have these scans
Scan Quantity
1) 12345 1
2) 4444 1
3) 77777 1
4) 88888 1
5) 33333 1
6) 65466 1
7) 4444 1
8) 33333 1
9) 33333 1

if I have a product in my Product table that looks like this
Auto ID Product Number MFG Number ALt Number Description
30 12345 88888 333333 Crackers

I need a report to show
Description Product Number Quantity
Crackers 12345 5

Quantity of 3 because of the totals of scans numbers 1,4,5,8 &9

Currently I'm using only one column for the product number and then grouping them on the report.
I'm now trying to add the addional two colums in on a query so that i can get counts added when they use the alternate or mfg barcodes.

Below is my current query and report

[kd2017]

Are all 3 of these fields indexed? Are they unique? Are they unique to each other? Is it *possible* for a barcode to belong to multiple products?

How about three select queries tied together with a union?

Something like:

SELECT Products.ID, Quantity FROM Products INNER JOIN [Scan Data] ON [Scan Data].[Part #] = Products.[Product Number]
WHERE [Scan Data].[Scan Date] = #8/21/2018#

UNION

SELECT Products.ID, Quantity FROM Products INNER JOIN [Scan Data] ON [Scan Data].[Part #] = Products.[MFG Number]
WHERE [Scan Data].[Scan Date] = #8/21/2018#

UNION 

SELECT Products.ID, Quantity FROM Products INNER JOIN [Scan Data] ON [Scan Data].[Part #] = Products.[ALT Number]
WHERE [Scan Data].[Scan Date] = #8/21/2018#

And then run an aggregate query on the union query.