Proper way to deal with serial numbers of inventory stock catalog

[TylerBennett]

The end goal is to have data input (various serial numbers) along with data output (various serial numbers) handled such that I can continually update the database with the "remaining data" (any nonidentical serial numbers). If my data tables are such that:

InputData
serial1
serial2
serial3
serial4
serial5

and

OutputData
serial1
serial3

I would like to be able to:
- compare data points in each table
- recognize identical data points
- if datapoints are identical, delete said records... creating a remaining list of only the serial numbers in stock

I have two general questions that pertain to the best method to do so...

Firstly, handling the data:
I have two thoughts... create a count of each of the input and output data (assuming they are in separate tables)... each count will be 1 because the serial numbers are unique. I can then theoretically identify identical records in each table, subtract the count (will always subtract to 0 if there are identical serial numbers) and then have code that deletes the record if the combinedcount=0 .

Conversely, I can theoretically search each table for identical text... if there are identical text records, delete record.

^This questions pertain mainly to: is it easier to deal with the data identification and deletion through numbers (a count of 0) or simply through data recognition (text)


Secondly,
i have two databases I was messing around with...
Database1: input and output are two separate tables
Database2: input and output data are in the same table and thus represent different fields in the table.


Does anybody have experience/an opinion on which database would best set me up for success when trying to delete identical records (should I have two separate tables that are compared or can i compare data in the same table and delete identical records between the two fields)??

Thank you!

[TylerBennett]

Apologies, I was stuck in the line of thinking of my previous problem that I was viewing incorrectly.

Due to the fact that each record is guaranteed to be unique upon entry (no record in the same table will have the same values because serial numbers are unique), this is simply an unmatched union query between two separate tables.

Thus, the answers to my above questions are:
1) use unmatched union query (query wizard helped for this)
2) The unmatched union query requires comparison of two separate tables and therefore my input, output records had to be separate tables!!

[NeoPa]

Hi Tyler.

I've reset the Best Answer as this is generally not set to any post by the OP (Original Poster - in this case you). Nevertheless, providing an answer yourself is always appreciated and respected.

Another tip :
We generally require all questions be kept in separate threads. Thus, this would typically have been spread across two as it contains two questions. You'll see, if you think about it, why this makes good sense when dealing with the volumes of questions we do.

Please don't see either comment as criticism. You're new here and learning the ropes. Doing pretty well so far, but will certainly improve once some of these extra points are taken on board.

[TylerBennett]

Thats fair and no worries all constructive! I just figured marking it as best answer would remove it from being an 'unanswered question'

[NeoPa]

That makes perfect sense and I also find it easy to believe. Just to reiterate, this is just to help you to understand how things work here. All good so far :-)