querey design help

[Tony in Michigan]

I need some design help.

I have two tables which store record sets with 48 fields and 1 to hundreds
of lines.

T1 is my "main" table or record table
T2 is my "transfer" table or a temp file

there are two fields that I believe are significant here [serial num] and
[line num]

Serial num is unique to each record set and it caries the version of the
record set.

abc123_nn, where abc123 is the unique alpha-numeric with hyphens s/n and _nn
is the version number.

line num is a sequential count of the records in the set 1,2,3,etc. This
could potentially increase or decrease with a reversion.

Now, I would like to figure out how to do one or both of two queries.

a, a query or queries that will append the new unique record sets from T2 to
T1 and overwrite any existing record sets that have been reversioned.

or

b, a make table query that will extract the record sets by "MAX" version,
making a clean T1_nn+1

I tried a couple of different ways, but ended up either not working at all
or getting lost in the steps. I'm thinking that this should be relatively
simple, and just a matter of properly parsing the serial num field, to get
the version and doing some sort of compare to extract only the "MAX" version.

 

[KARL DEWEY]

a. ------
Update -
- Substitute T1 & T2 for EMP & EMP1
- Substitute [serial num] & [line num] for FirstName & LastName

UPDATE EMP INNER JOIN EMP1 ON (EMP.FIRSTNAME = EMP1.FirstName) AND
(EMP.LASTNAME = EMP1.LastName) SET EMP.ADDR1 = [EMP1].[ADDR1], EMP.CITY =
[EMP1].[CITY], EMP.STATE = [EMP1].[STATE]
WHERE (((EMP.ADDR1)<>[EMP1].[ADDR1])) OR (((EMP.CITY)<>[EMP1].[CITY])) OR
(((EMP.STATE)<>[EMP1].[STATE]));

Append -
Add the rest of the fields to the INSERT and SELECT parts.
INSERT INTO EMP ( [Field1], [Field2], LASTNAME, FIRSTNAME )
SELECT [Field1], [Field2], EMP.LASTNAME, EMP.FIRSTNAME
FROM EMP RIGHT JOIN EMP1 ON (EMP.FIRSTNAME = EMP1.FirstName) AND
(EMP.LASTNAME = EMP1.LastName)
WHERE (((EMP.LASTNAME) Is Null) AND ((EMP.FIRSTNAME) Is Null));

b. ---------
[Find Max] --
SELECT Max(EMP.LASTNAME) AS MaxOfLASTNAME, Max(EMP.FIRSTNAME) AS
MaxOfFIRSTNAME
FROM EMP;

Make table --
SELECT EMP.* INTO EMP_xx
FROM EMP INNER JOIN EMP1 ON (EMP.FIRSTNAME = [Find Max].FirstName) AND
(EMP.LASTNAME = [Find Max].LastName);

 

[Tony in Michigan]

Thanks Karl!

I think I am able to follow the logic, so it's just a matter of editing in
my stuff, and editing out the N/A items.

a. ------
Update -
- Substitute T1 & T2 for EMP & EMP1
- Substitute [serial num] & [line num] for FirstName & LastName

UPDATE EMP INNER JOIN EMP1 ON (EMP.FIRSTNAME = EMP1.FirstName) AND
(EMP.LASTNAME = EMP1.LastName) SET EMP.ADDR1 = [EMP1].[ADDR1], EMP.CITY =
[EMP1].[CITY], EMP.STATE = [EMP1].[STATE]
WHERE (((EMP.ADDR1)<>[EMP1].[ADDR1])) OR (((EMP.CITY)<>[EMP1].[CITY])) OR
(((EMP.STATE)<>[EMP1].[STATE]));

Append -
Add the rest of the fields to the INSERT and SELECT parts.
INSERT INTO EMP ( [Field1], [Field2], LASTNAME, FIRSTNAME )
SELECT [Field1], [Field2], EMP.LASTNAME, EMP.FIRSTNAME
FROM EMP RIGHT JOIN EMP1 ON (EMP.FIRSTNAME = EMP1.FirstName) AND
(EMP.LASTNAME = EMP1.LastName)
WHERE (((EMP.LASTNAME) Is Null) AND ((EMP.FIRSTNAME) Is Null));

b. ---------
[Find Max] --
SELECT Max(EMP.LASTNAME) AS MaxOfLASTNAME, Max(EMP.FIRSTNAME) AS
MaxOfFIRSTNAME
FROM EMP;

Make table --
SELECT EMP.* INTO EMP_xx
FROM EMP INNER JOIN EMP1 ON (EMP.FIRSTNAME = [Find Max].FirstName) AND
(EMP.LASTNAME = [Find Max].LastName);

I need some design help.

I have two tables which store record sets with 48 fields and 1 to hundreds
of lines.

T1 is my "main" table or record table
T2 is my "transfer" table or a temp file

there are two fields that I believe are significant here [serial num] and
[line num]

Serial num is unique to each record set and it caries the version of the
record set.

abc123_nn, where abc123 is the unique alpha-numeric with hyphens s/n and _nn
is the version number.

line num is a sequential count of the records in the set 1,2,3,etc. This
could potentially increase or decrease with a reversion.

Now, I would like to figure out how to do one or both of two queries.

a, a query or queries that will append the new unique record sets from T2 to
T1 and overwrite any existing record sets that have been reversioned.

or

b, a make table query that will extract the record sets by "MAX" version,
making a clean T1_nn+1

I tried a couple of different ways, but ended up either not working at all
or getting lost in the steps. I'm thinking that this should be relatively
simple, and just a matter of properly parsing the serial num field, to get
the version and doing some sort of compare to extract only the "MAX" version.