Question Compare 2 queries with same fieldnames and no unique identifier (1 Viewer)

bruceblack

Registered User.
Local time
Today, 01:55
Joined
Jun 30, 2017
Messages
119
Hello everybody! I have a somewhat complex problem i have been working on, but unfortunately my brain is too limited for this to comprehend and fix. Please help! (ill try an be as specific and simple as i can, bare with me)


I work in a warehouse where we have to do cyclecounts (count stock and compare with whats in the system). So i build a database that has 2 tables:

- Warehouse_Stock
- Scanned_Products



In both tables we have the following fields:

- Location
- Item
- Serial number
- Quantity


I have a query from the warehouse (queryWHS)
I have a query from the scanned products (queryPRODUCTS)

So i scan the products and at the end i need query that shows me if there are any differences between the 2 on any combination of the 4 fields.

We can have a product too many, missing, different quantity, wrong serial, wrong item. But how can i compare them? It's impossible to have a unique related field on the scanned products since they are not scanned in a particular order.

I assume i need some SQL, but i have no clue where to start.
I tried unmatched query wizard, but that doesn't return the right results.

Where should we start with this? Please help. Been breaking my head about it. (ps: only not matching records will be sufficient!)
 

Mark_

Longboard on the internet
Local time
Yesterday, 18:55
Joined
Sep 12, 2017
Messages
2,111
In each file, what would be unique? i.e. would Serial number be a unique value or could you have multiple instances of the same item/serial number floating about?

Once you know what would be unique, you can start looking for common matches and remove them from your reporting.

If you could have multiple of the same item/serial number, but location and/or quantity is the only difference, this can be much more tricky, especially if "Location 1, Item 1, Serial Number 1, quantity 1" and "Location 1, Item 1, Serial Number 1, quantity 2" are valid entries.

looks like Uncle Gizmo is "Quicker on the draw" than me...
 

bruceblack

Registered User.
Local time
Today, 01:55
Joined
Jun 30, 2017
Messages
119
Thank Gizmo for the swift reply. However, that stuff is ALOT to take in and doesn't seem to apply exactly.

Thanks Mark for you swift reply as well!
You last statement would be correct.
The problem with the serials is that they can be spare parts that ALL have the serial number 100. So thats not going to work either.

HOWEVER, my ONLY workaround concerning this issue is that:

The spare parts are merged in the system. Look something like this:

LOCATION : AA-01-01
PRODUCT_NAME: BSX777
SERIAL NUMBER: 100
QUANTITY : 26


So as you can see, there can only be product_name on a location.
It merged if there are more of them (see the quantity).

So that's something i can work with.
All i wanna do, is compare the 2 queries and compare and show what doesnt match in any case possible.
 

Mark_

Longboard on the internet
Local time
Yesterday, 18:55
Joined
Sep 12, 2017
Messages
2,111
BruceBlack,

Try reading through the link HERE to see if this can explain how to set up the proper outer join to return what you are looking for.

You are trying to show everything in BOTH table but including the quantity both from the system AND from the scans, then add in a field that shows the difference.

You will want to order by product name THEN location, that way you can see something that looks like

PRODUCT_NAME: BSX777
LOCATION : AA-01-01
SERIAL NUMBER: 100
SYSTEM QUANTITY : 26
SCAN QUANTITY : 0
[CALCULATED DIFFERENCE] : 26

PRODUCT_NAME: BSX777
LOCATION : JIM'S FLAT
SERIAL NUMBER: 100
SYSTEM QUANTITY : 0
SCAN QUANTITY : 26
[CALCULATED DIFFERENCE] : 26

And match up where your inventory is off. Having worked in supply in the past, I know how quickly things get moved where they are not intended.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 01:55
Joined
Sep 12, 2006
Messages
15,614
I don't follow. how can you not have a unique identifier to use to compare two items?

Can you give us an exact example of what you are trying to compare?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 01:55
Joined
Jul 9, 2003
Messages
16,245
What's even more worrying is you are allowed to scan things in Jim's flat!

Sent from my SM-G925F using Tapatalk
 

bruceblack

Registered User.
Local time
Today, 01:55
Joined
Jun 30, 2017
Messages
119
Thank you so much everyone! Your help did help along with my thinking pattern.

I SOLVED the problem with a rather simple workaround that seems to work:

For every field, i made a seperate unmatched query. (just use the wizard)
For each of them, i changed the join properties to 2: include all records.
I also made sure that all the field names are the same and in the same order.
(Do this for both tables)

Then i made a new union query by creating a blank query and go to SQL view.
My code looks like this:

SELECT location, article, partij, quantity, "Item does not match" as Problem
FROM unmatch_items_scan

UNION

SELECT location, article, partij, quantity, "Serial does not match" as Problem
FROM unmatch_serial_scan

UNION SELECT location, article, partij, quantity, "Location does not match" as Problem
FROM unmatch_locations_scan

UNION SELECT location, article, partij, quantity, "Quantity does not match" as Problem
FROM unmatch_quantity_scan;


The "as problem" part give you a new field in the query that describes the problem to me when it returns the values.

Flat out/template written it looks like this:

UNION SELECT field1, field2, field3, field4, "problem discription" as nameofproblemfield
FROM thequerynameforeachpossibleoutcome;


Hope this can help others as well.
Thank you all!
 

Users who are viewing this thread

Top Bottom