Duplicating values to empty field

[DanBakerUSAF]

I am new to SQL in queries. I can ussually get what I want with forms and
the query wizard. This I cannot figure out and it seems like a SQL statement
might work. I import data from a spreadsheet automatically (a click of a cmd
button). I have most of the fields removed or appended to except one. I
need to duplicate a feild value to the empty values below it. Example:

ID NSN/MODDACC Description QTY UOM
1 7310-01-507-9310 (Stove) Modern Burner Unit LIN AA0084
2 20 EA
3 1005-00-550-8141 ACCELERATOR, MACHINE
4 25 EA
5 1010-01-043-2044 ACTUATOR FIRING PIN
6 5 RL

Records 1 & 2, 3 & 4 and 5 & 6 both need the same NSN/MODACC# respectively.
The ID#s change but are always sequencial. Basically if the NSN/MODDACC
field is null it needs to equal the previous records NSN/MODDACC. If it is
not null then move to next record.

End result looking like:

ID NSN/MODDACC Description QTY UOM
1 7310-01-507-9310 (Stove) Modern Burner Unit LIN AA0084
2 7310-01-507-9310 20 EA
3 1005-00-550-8141 ACCELERATOR, MACHINE
4 1005-00-550-8141 25 EA
5 1010-01-043-2044 ACTUATOR FIRING PIN
6 1010-01-043-2044 5 RL

Anyway this can be done with either an append or update query? I would even
be able to work with a make table if need be.

This is the last in a long string of firsts for me and would greatly
appreciate any input or suggestions. As I said before SQL and most VB code is
above me, I like to keep it simple. I am looking for a dumb down
explanation. Thank you in advance for your help.

 

[John Spencer]

If the IDs are always sequential.

Use the following as a query to populate a table - MatchTable with Three
fields A_ID, B_ID and NSN

/tchTable (A_ID, B_ID)
SELECT Max(A.ID) as MatchTo, B.ID
FROM YourTable as A INNER JOIN YourTable As B
ON A.ID < B.ID
WHERE A.[NSN/ModdAcc] is Not Null AND
B.[NSN/Moddacc] is Null
GROUP BY B.ID

Next Step
Get the NSN/Moddacc value for the records in the MatchTable
UPDATE MatchTable as M Inner Join YourTable as A
ON M.A_ID = A.ID
SET M.NSN = [A].[NSN/ModdAcc]

Now use the Matches table to update your table

UPDATE YourTable as A INNER JOIN MatchTable as M
ON M.B_ID = M.ID
UPDATE A.[NSN/ModdAcc] = M.NSN

You could probably combine steps 2 and 3 into one query. However, I
have had my second glass of Sunday wine and would rather break this down
into three steps. Hopefully, the second glass didn't allow errors to
creep into my proposed solution.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[DanBakerUSAF]

I probably did not understand the process. It is giving an error of "Invalid
SQL Statement; expected 'Delete', 'Insert', 'Procedure', or 'Update'."

I pasted your statement into a new query and change 'yourtable' to
[Availability Report] which is the source table.

SELECT Max(A.ID) as MatchTo, B.ID
FROM [Availability Report] as A INNER JOIN [Availability Report] As B
ON A.ID < B.ID
WHERE A.[NSN/ModdAcc] is Not Null AND
B.[NSN/Moddacc] is Null
GROUP BY B.ID

When it was ran it seemed to have the IDs matched up correctly.
When I run the second query it prompts for M.NSN

Do I need to have an empty table named MatchTable or can the query be named
MatchTable and it still work? I think it is very close I just don't know
enough about SQL.

If the IDs are always sequential.

Use the following as a query to populate a table - MatchTable with Three
fields A_ID, B_ID and NSN

/tchTable (A_ID, B_ID)
SELECT Max(A.ID) as MatchTo, B.ID
FROM YourTable as A INNER JOIN YourTable As B
ON A.ID < B.ID
WHERE A.[NSN/ModdAcc] is Not Null AND
B.[NSN/Moddacc] is Null
GROUP BY B.ID

Next Step
Get the NSN/Moddacc value for the records in the MatchTable
UPDATE MatchTable as M Inner Join YourTable as A
ON M.A_ID = A.ID
SET M.NSN = [A].[NSN/ModdAcc]

Now use the Matches table to update your table

UPDATE YourTable as A INNER JOIN MatchTable as M
ON M.B_ID = M.ID
UPDATE A.[NSN/ModdAcc] = M.NSN

You could probably combine steps 2 and 3 into one query. However, I
have had my second glass of Sunday wine and would rather break this down
into three steps. Hopefully, the second glass didn't allow errors to
creep into my proposed solution.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I am new to SQL in queries. I can ussually get what I want with forms and
the query wizard. This I cannot figure out and it seems like a SQL statement
might work. I import data from a spreadsheet automatically (a click of a cmd
button). I have most of the fields removed or appended to except one. I
need to duplicate a feild value to the empty values below it. Example:

ID NSN/MODDACC Description QTY UOM
1 7310-01-507-9310 (Stove) Modern Burner Unit LIN AA0084
2 20 EA
3 1005-00-550-8141 ACCELERATOR, MACHINE
4 25 EA
5 1010-01-043-2044 ACTUATOR FIRING PIN
6 5 RL

Records 1 & 2, 3 & 4 and 5 & 6 both need the same NSN/MODACC# respectively.
The ID#s change but are always sequencial. Basically if the NSN/MODDACC
field is null it needs to equal the previous records NSN/MODDACC. If it is
not null then move to next record.

End result looking like:

ID NSN/MODDACC Description QTY UOM
1 7310-01-507-9310 (Stove) Modern Burner Unit LIN AA0084
2 7310-01-507-9310 20 EA
3 1005-00-550-8141 ACCELERATOR, MACHINE
4 1005-00-550-8141 25 EA
5 1010-01-043-2044 ACTUATOR FIRING PIN
6 1010-01-043-2044 5 RL

Anyway this can be done with either an append or update query? I would even
be able to work with a make table if need be.

This is the last in a long string of firsts for me and would greatly
appreciate any input or suggestions. As I said before SQL and most VB code is
above me, I like to keep it simple. I am looking for a dumb down
explanation. Thank you in advance for your help.

 

[DanBakerUSAF]

Imeant to add that I removed "/tchTable (A_ID, B_ID)" which stopped the SQL
Statment error.

I probably did not understand the process. It is giving an error of "Invalid
SQL Statement; expected 'Delete', 'Insert', 'Procedure', or 'Update'."

I pasted your statement into a new query and change 'yourtable' to
[Availability Report] which is the source table.

SELECT Max(A.ID) as MatchTo, B.ID
FROM [Availability Report] as A INNER JOIN [Availability Report] As B
ON A.ID < B.ID
WHERE A.[NSN/ModdAcc] is Not Null AND
B.[NSN/Moddacc] is Null
GROUP BY B.ID

When it was ran it seemed to have the IDs matched up correctly.
When I run the second query it prompts for M.NSN

Do I need to have an empty table named MatchTable or can the query be named
MatchTable and it still work? I think it is very close I just don't know
enough about SQL.

If the IDs are always sequential.

Use the following as a query to populate a table - MatchTable with Three
fields A_ID, B_ID and NSN

/tchTable (A_ID, B_ID)
SELECT Max(A.ID) as MatchTo, B.ID
FROM YourTable as A INNER JOIN YourTable As B
ON A.ID < B.ID
WHERE A.[NSN/ModdAcc] is Not Null AND
B.[NSN/Moddacc] is Null
GROUP BY B.ID

Next Step
Get the NSN/Moddacc value for the records in the MatchTable
UPDATE MatchTable as M Inner Join YourTable as A
ON M.A_ID = A.ID
SET M.NSN = [A].[NSN/ModdAcc]

Now use the Matches table to update your table

UPDATE YourTable as A INNER JOIN MatchTable as M
ON M.B_ID = M.ID
UPDATE A.[NSN/ModdAcc] = M.NSN

You could probably combine steps 2 and 3 into one query. However, I
have had my second glass of Sunday wine and would rather break this down
into three steps. Hopefully, the second glass didn't allow errors to
creep into my proposed solution.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I am new to SQL in queries. I can ussually get what I want with forms and
the query wizard. This I cannot figure out and it seems like a SQL statement
might work. I import data from a spreadsheet automatically (a click of a cmd
button). I have most of the fields removed or appended to except one. I
need to duplicate a feild value to the empty values below it. Example:

ID NSN/MODDACC Description QTY UOM
1 7310-01-507-9310 (Stove) Modern Burner Unit LIN AA0084
2 20 EA
3 1005-00-550-8141 ACCELERATOR, MACHINE
4 25 EA
5 1010-01-043-2044 ACTUATOR FIRING PIN
6 5 RL

Records 1 & 2, 3 & 4 and 5 & 6 both need the same NSN/MODACC# respectively.
The ID#s change but are always sequencial. Basically if the NSN/MODDACC
field is null it needs to equal the previous records NSN/MODDACC. If it is
not null then move to next record.

End result looking like:

ID NSN/MODDACC Description QTY UOM
1 7310-01-507-9310 (Stove) Modern Burner Unit LIN AA0084
2 7310-01-507-9310 20 EA
3 1005-00-550-8141 ACCELERATOR, MACHINE
4 1005-00-550-8141 25 EA
5 1010-01-043-2044 ACTUATOR FIRING PIN
6 1010-01-043-2044 5 RL

Anyway this can be done with either an append or update query? I would even
be able to work with a make table if need be.

This is the last in a long string of firsts for me and would greatly
appreciate any input or suggestions. As I said before SQL and most VB code is
above me, I like to keep it simple. I am looking for a dumb down
explanation. Thank you in advance for your help.

 

[John Spencer]

Hmm, something happened to reformat part of my response. So you were correct
to remove the "bad line".

Yes, you do need the empty table. Access won't allow you to run an update
query that contains an aggregate (or Totals) query (Max, Min, Group, etc.) in
the SET Clause or in the UPDATE clause.

The best way to work around this is to use a work table for large datasets.
That work table will need three fields:
A_ID, B_ID, and NSN (or NSN/ModdAcc if you want to keep the same name for the
field). You use the first query to populate the table with the matching IDs.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

The first query should have read as follows. This will put the two IDs into
the table MatchTable.

INSERT INTO MatchTable (A_ID, B_ID)
SELECT Max(A.ID) as MatchTo, B.ID
FROM [Availability Report] as A INNER JOIN [Availability Report] As B
ON A.ID < B.ID
WHERE A.[NSN/ModdAcc] is Not Null AND
B.[NSN/Moddacc] is Null
GROUP BY B.ID

Now the next query adds the NSN/ModdAcc into the work table.

UPDATE MatchTable as M Inner Join [Availability Report] as A
ON M.A_ID = A.ID
SET M.NSN = [A].[NSN/ModdAcc]

And the final step is to update [Availability Report] table.
UPDATE [Availability Report] as A INNER JOIN MatchTable as M
ON M.B_ID = M.ID
UPDATE A.[NSN/ModdAcc] = M.NSN

At this point you can delete all the records in MatchTable if you wish.
DELETE
FROM MatchTable

One problem with this is that your database will bloat with all the adding and
deleting of MatchTable records. One solution to this is to use a temporary
database for the MatchTable. See Tony Toews website
http://www.granite.ab.ca/access/temptables.htm
for an example on creating a temporary database with work tables.


John Spencer
Capt USAF (Retired)
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I probably did not understand the process. It is giving an error of "Invalid
SQL Statement; expected 'Delete', 'Insert', 'Procedure', or 'Update'."

I pasted your statement into a new query and change 'yourtable' to
[Availability Report] which is the source table.

SELECT Max(A.ID) as MatchTo, B.ID
FROM [Availability Report] as A INNER JOIN [Availability Report] As B
ON A.ID < B.ID
WHERE A.[NSN/ModdAcc] is Not Null AND
B.[NSN/Moddacc] is Null
GROUP BY B.ID

When it was ran it seemed to have the IDs matched up correctly.
When I run the second query it prompts for M.NSN

Do I need to have an empty table named MatchTable or can the query be named
MatchTable and it still work? I think it is very close I just don't know
enough about SQL.

"John Spencer" wrote:

 

[DanBakerUSAF]

Once I change the last query, ON M.B_ID = M.ID, to ON M.B_ID = A.ID, it
worked!!

Unfortunatley I had to remove the autonumber from [Availability Report].ID
since it created a type mismatch in both update queries. Is there any way
around this problem? I obviously need to keep the autonumber there when I
import new data.

Hmm, something happened to reformat part of my response. So you were correct
to remove the "bad line".

Yes, you do need the empty table. Access won't allow you to run an update
query that contains an aggregate (or Totals) query (Max, Min, Group, etc.) in
the SET Clause or in the UPDATE clause.

The best way to work around this is to use a work table for large datasets.
That work table will need three fields:
A_ID, B_ID, and NSN (or NSN/ModdAcc if you want to keep the same name for the
field). You use the first query to populate the table with the matching IDs.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

The first query should have read as follows. This will put the two IDs into
the table MatchTable.

INSERT INTO MatchTable (A_ID, B_ID)
SELECT Max(A.ID) as MatchTo, B.ID
FROM [Availability Report] as A INNER JOIN [Availability Report] As B
ON A.ID < B.ID
WHERE A.[NSN/ModdAcc] is Not Null AND
B.[NSN/Moddacc] is Null
GROUP BY B.ID

Now the next query adds the NSN/ModdAcc into the work table.

UPDATE MatchTable as M Inner Join [Availability Report] as A
ON M.A_ID = A.ID
SET M.NSN = [A].[NSN/ModdAcc]

And the final step is to update [Availability Report] table.
UPDATE [Availability Report] as A INNER JOIN MatchTable as M
ON M.B_ID = M.ID
UPDATE A.[NSN/ModdAcc] = M.NSN

At this point you can delete all the records in MatchTable if you wish.
DELETE
FROM MatchTable

One problem with this is that your database will bloat with all the adding and
deleting of MatchTable records. One solution to this is to use a temporary
database for the MatchTable. See Tony Toews website
http://www.granite.ab.ca/access/temptables.htm
for an example on creating a temporary database with work tables.


John Spencer
Capt USAF (Retired)
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I probably did not understand the process. It is giving an error of "Invalid
SQL Statement; expected 'Delete', 'Insert', 'Procedure', or 'Update'."

I pasted your statement into a new query and change 'yourtable' to
[Availability Report] which is the source table.

SELECT Max(A.ID) as MatchTo, B.ID
FROM [Availability Report] as A INNER JOIN [Availability Report] As B
ON A.ID < B.ID
WHERE A.[NSN/ModdAcc] is Not Null AND
B.[NSN/Moddacc] is Null
GROUP BY B.ID

When it was ran it seemed to have the IDs matched up correctly.
When I run the second query it prompts for M.NSN

Do I need to have an empty table named MatchTable or can the query be named
MatchTable and it still work? I think it is very close I just don't know
enough about SQL.

"John Spencer" wrote:

 

[John Spencer]

You should be able to keep the ID in Availability Report as an autonumber.

In MatchTable the two id fields should be number fields with field size set to
Long Integer. They should not be autonumbers. In other words,
MatchTable.A_ID and MatchTable.B_ID should be long integer fields. The NSN in
MatchTable should be the same type and size as the NSN/ModdAcc field (text? 50?)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Once I change the last query, ON M.B_ID = M.ID, to ON M.B_ID = A.ID, it
worked!!

Unfortunatley I had to remove the autonumber from [Availability Report].ID
since it created a type mismatch in both update queries. Is there any way
around this problem? I obviously need to keep the autonumber there when I
import new data.

Hmm, something happened to reformat part of my response. So you were correct
to remove the "bad line".

Yes, you do need the empty table. Access won't allow you to run an update
query that contains an aggregate (or Totals) query (Max, Min, Group, etc.) in
the SET Clause or in the UPDATE clause.

The best way to work around this is to use a work table for large datasets.
That work table will need three fields:
A_ID, B_ID, and NSN (or NSN/ModdAcc if you want to keep the same name for the
field). You use the first query to populate the table with the matching IDs.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

The first query should have read as follows. This will put the two IDs into
the table MatchTable.

INSERT INTO MatchTable (A_ID, B_ID)
SELECT Max(A.ID) as MatchTo, B.ID
FROM [Availability Report] as A INNER JOIN [Availability Report] As B
ON A.ID < B.ID
WHERE A.[NSN/ModdAcc] is Not Null AND
B.[NSN/Moddacc] is Null
GROUP BY B.ID

Now the next query adds the NSN/ModdAcc into the work table.

UPDATE MatchTable as M Inner Join [Availability Report] as A
ON M.A_ID = A.ID
SET M.NSN = [A].[NSN/ModdAcc]

And the final step is to update [Availability Report] table.
UPDATE [Availability Report] as A INNER JOIN MatchTable as M
ON M.B_ID = M.ID
UPDATE A.[NSN/ModdAcc] = M.NSN

At this point you can delete all the records in MatchTable if you wish.
DELETE
FROM MatchTable

One problem with this is that your database will bloat with all the adding and
deleting of MatchTable records. One solution to this is to use a temporary
database for the MatchTable. See Tony Toews website
http://www.granite.ab.ca/access/temptables.htm
for an example on creating a temporary database with work tables.


John Spencer
Capt USAF (Retired)
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I probably did not understand the process. It is giving an error of "Invalid
SQL Statement; expected 'Delete', 'Insert', 'Procedure', or 'Update'."

I pasted your statement into a new query and change 'yourtable' to
[Availability Report] which is the source table.

SELECT Max(A.ID) as MatchTo, B.ID
FROM [Availability Report] as A INNER JOIN [Availability Report] As B
ON A.ID < B.ID
WHERE A.[NSN/ModdAcc] is Not Null AND
B.[NSN/Moddacc] is Null
GROUP BY B.ID

When it was ran it seemed to have the IDs matched up correctly.
When I run the second query it prompts for M.NSN

Do I need to have an empty table named MatchTable or can the query be named
MatchTable and it still work? I think it is very close I just don't know
enough about SQL.

"John Spencer" wrote: