New User - need help with a query

I hope someone can help me with this. I’m still sort of new to access and I
think I’m confusing myself. I have three tables that I want to check for data
discrepancies.
One table holds 2006 inventory data, one table holds 2007 inventory data, and
then there is a Sales table that holds multiple years of sales information .I
ran some queries and was able to determine inventory discrepancies between
the 2006 and 2007 tables using a Batch number column as the unique identifier.
I was able to determine there were a combined total of 13,000 + records that
only appeared in one inventory table or the other. I need to run that
discrepancy list against the sales table…but there is not a unique identifier
that I can use. The sales table doesn’t include the batch number. I was going
to run another query and append that information but was not able to
determine what part number got what batch numbers….part numbers are used
multiple times and can have the same record information.

I need to see a discrepancy list between my query output and the sales table.
I’m not sure what to do since I don’t have that unique record identifier
between them. However each table (Sales table and my output) contains a part
number and a serial number column. The data in these columns are alpha
numeric. The numbers can be used multiple times within each table, but the
combo won’t be duped. So I have the same part number maybe 7 or 8 times but
each time the associated serial number is different. Or vice a versa…same
serial number a few times with a different part number. There are about 55
thousand records in my Sales table. Those fields are populated, but I have
some records with no serial number output and just a part number in my query
output table. What would be the best way to determine discrepancies between
the two tables using the part/serial number information? I feel kind of lost
here. I feel like I’m missing something.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200711/1

Create a table with fields for part number and serial number. Add another
field for batch number. In design view click on menu VIEW - Indexes. Select
the two fields, enter a name for the index in the top row. Click one row
down then back in the top row. In the pane below select Unique - Yes and
Ignore Nulls - Yes.

Append all instance of part number and serial number from all your tables
to create a complete list. Ignore the error messages as it is eliminating
duplication.

Then join the part number/serial number table with other table having batch
number and update the part number/serial number table to fill in the batch
number.
--
KARL DEWEY
Build a little - Test a little


"AnnieV via AccessMonster.com" wrote:

I hope someone can help me with this. I’m still sort of new to access and I
think I’m confusing myself. I have three tables that I want to check for data
discrepancies.
One table holds 2006 inventory data, one table holds 2007 inventory data, and
then there is a Sales table that holds multiple years of sales information .I
ran some queries and was able to determine inventory discrepancies between
the 2006 and 2007 tables using a Batch number column as the unique identifier.
I was able to determine there were a combined total of 13,000 + records that
only appeared in one inventory table or the other. I need to run that
discrepancy list against the sales table…but there is not a unique identifier
that I can use. The sales table doesn’t include the batch number. I was going
to run another query and append that information but was not able to
determine what part number got what batch numbers….part numbers are used
multiple times and can have the same record information.

I need to see a discrepancy list between my query output and the sales table.
I’m not sure what to do since I don’t have that unique record identifier
between them. However each table (Sales table and my output) contains a part
number and a serial number column. The data in these columns are alpha
numeric. The numbers can be used multiple times within each table, but the
combo won’t be duped. So I have the same part number maybe 7 or 8 times but
each time the associated serial number is different. Or vice a versa…same
serial number a few times with a different part number. There are about 55
thousand records in my Sales table. Those fields are populated, but I have
some records with no serial number output and just a part number in my query
output table. What would be the best way to determine discrepancies between
the two tables using the part/serial number information? I feel kind of lost
here. I feel like I’m missing something.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200711/1