B
BillKalbo
Some of you experts will probably snicker at this as being SO simple but
being a newbie to Access, I'm perplexed as how to approach it.
Basically, I'm trying to verify 2 inventories: one real (as in a physical
inventory) and one is what is expected to be there, we'll call it "claimed".
All quantities are 1 as each entry in the physical inventory has a serial
number and bar code. The problem arises in that some of the claimed
inventory is by model only so some are claiming 3 of model 1600 so we do not
have unique records. Also, none of the claimed have barcodes listed. 95%
have a unique "machine name" but there are many duplicates or missing names
so the output of a simple query gives 3 times as many records as were put
into the query.
The end result should be 3 reports, tables, etc:
1. All the hardware that was found and is expected to be there. (no
discrepancy)
2. All hardware that the physical inventory found but can not be located on
the "claimed" inventory (phy orphans)
3. All hardware that the is "claimed" but can not be found in the physical
inventory (claimed orphans)
my best guess is to try the following approached. Since I'm a newbie, I'll
not try to use the proper database terms
Run multiple queries that remove records (or marks them) in each input
inventory and writes them to one of the 3 output tables listed above. First
clean out the easy matches based on the machine name. Then pass the
remaining records through a different query that tries to match them by
another field like serial number, then a 3rd or possibly 4th pass. The
remaining records will be our orphans that can't be matched up and will be
cleaned up manually.
I can think of a few variances on this idea, like to write one complex query
that does it in one pass but I'll leave it to you to suggest the best
approach.
Any help would be appreciated
Billkalbo
being a newbie to Access, I'm perplexed as how to approach it.
Basically, I'm trying to verify 2 inventories: one real (as in a physical
inventory) and one is what is expected to be there, we'll call it "claimed".
All quantities are 1 as each entry in the physical inventory has a serial
number and bar code. The problem arises in that some of the claimed
inventory is by model only so some are claiming 3 of model 1600 so we do not
have unique records. Also, none of the claimed have barcodes listed. 95%
have a unique "machine name" but there are many duplicates or missing names
so the output of a simple query gives 3 times as many records as were put
into the query.
The end result should be 3 reports, tables, etc:
1. All the hardware that was found and is expected to be there. (no
discrepancy)
2. All hardware that the physical inventory found but can not be located on
the "claimed" inventory (phy orphans)
3. All hardware that the is "claimed" but can not be found in the physical
inventory (claimed orphans)
my best guess is to try the following approached. Since I'm a newbie, I'll
not try to use the proper database terms
Run multiple queries that remove records (or marks them) in each input
inventory and writes them to one of the 3 output tables listed above. First
clean out the easy matches based on the machine name. Then pass the
remaining records through a different query that tries to match them by
another field like serial number, then a 3rd or possibly 4th pass. The
remaining records will be our orphans that can't be matched up and will be
cleaned up manually.
I can think of a few variances on this idea, like to write one complex query
that does it in one pass but I'll leave it to you to suggest the best
approach.
Any help would be appreciated
Billkalbo