If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Duplicating values to empty field
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. |
#2
|
|||
|
|||
Duplicating values to empty field
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 wrote: 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. |
#3
|
|||
|
|||
Duplicating values to empty field
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: 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 wrote: 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. |
#4
|
|||
|
|||
Duplicating values to empty field
Imeant to add that I removed "/tchTable (A_ID, B_ID)" which stopped the SQL
Statment error. "DanBakerUSAF" wrote: 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: 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 wrote: 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. |
#5
|
|||
|
|||
Duplicating values to empty field
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 DanBakerUSAF wrote: 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: |
#6
|
|||
|
|||
Duplicating values to empty field
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. "John Spencer" wrote: 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 DanBakerUSAF wrote: 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: |
#7
|
|||
|
|||
Duplicating values to empty field
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 DanBakerUSAF wrote: 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. "John Spencer" wrote: 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 DanBakerUSAF wrote: 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: |
Thread Tools | |
Display Modes | |
|
|