Append query based on records in table

[cms370]

All,

I am trying to create an append query based on data currently in a table. For discussion purposes, lets assume 'Table1' contains (A,B,C) in 'Field1'.

I would like the append query to search for a value, 'B' in this case, and if present, create another record 'X'.

Here is the code I have been trying to get to work:

INSERT INTO Table1 ( Field1 )
SELECT IIf(Table1!Field1='B','X',0);

After executing the code, a text box appears requesting the value for 'Table1!Field1'. If I enter 'B', the append query executes successfully.

However, why is this query requeting input from the user? Shouldnt it be using the table?

Thanks.

 

[namliam]

Your inserting into table1, but where are you selecting from???

INSERT INTO Table1 ( Field1 )
SELECT IIf(Table1!Field1='B','X',0)
From Table1

That will resolve your problem I expect.

 

[cms370]

Yep...that was it...I am just getting started with SQL for Access.

 

[cms370]

It is probably worth me adding that this is a better way to do what I am trying to do as it doesnt leave extra '0' in places that do not match 'B'.

INSERT INTO TABLE1 ( Field1 )
SELECT 'X'
FROM TABLE1
WHERE Field1='B';

 

[namliam]

Worse, which I didnt want to add but will now that you started it...
Something like this really really sounds like a design issue which can/should be solved in a different way than this.

It might well be worth looking over your design or looking into UNION queries and see if that helps your cause.

 

[cms370]

namliam...

ok, so help me out here? Why is this a bad approach?

Here is part of what I need to do:

I will be importing a text file into a table. The table will contain a field with values that are either A,B,C (once again, keeping it simple). If the field is C, I need to create two copies of the record and change two of the fields in the copied records.

My thought was to use the code above to determine if 'C' exists in the record. If it does, I then plan on copying the record twice and making the appropriate changes to the copied records.

Any thoughts or suggestions on how to do this would be appreciated.

 

[speakers_86]

Copying the record doesn't sound like a good idea. Why not just add two more fields to your current record?

 

[cms370]

Essentially records containing 'C' need to be "decomposed" into a record containing 'A' and record containing 'B'.

Records containing 'C' do not fit into the next step of the process. They need to contain information oriented in the same way as 'A' and 'B'. Hence the reason I am first determining which records contain 'C', then creating two new records, then updating 'A' and 'B' so that they equate to one record of 'C'.

All records containing 'C' will then be deleted. After doing this, data will then be in the appropriate format to move to the next step.

 

[namliam]

Well like I said, a union query can do that...

The steps:
1) Make a select query to alter your one C record into one A record
Save this query as qryMakeCintoA

2) Make a select query to alter your one C record into one B record
Save this query as qryMakeCintoB

3) Make a Select query to select all non-C records
Save this query as qryAllNonC

4) Now make a union query
Select * from qryAllNonC
Union all
Select * from qryMakeCintoB
Union all
qryMakeCintoA

Save this query, and without actually saving your records you have still managed to 'create' your additional records.

 

[cms370]

The other thing I forgot to mention is that when creating the A and B records I need to reference another table in order to appropriately create the values for the A and B records. The original C record contains some unique "tracking number" that will correlate with the tracking number in the other table.

Steps:
1) Find C records
2) Identify "tracking number" from C record
3) Look for tracking number in "other table"
4) From other table, extract "scale factor" the corresponding tracking number row
5) Multiply "scale factor" times the original C record, and create A record.

I have been trying to do this using VBA code and SQL statements. I would think that I would need to use a "IF...ELSE" statement however, I have had some trouble getting this to work.

Right now I am just trying to get a simple example working so that I can then scale this up to the actual work I am trying to accomplish.

Here is what I am testing:

'Set db
Set = CurrentDb

'Create SQL statement
strSql = "IF Table1.[Text1] < Table1.[Text3] BEGIN INSERT INTO Table1 (Text1) VALUES (9999) END;"

'Run SQL Command
db.Execute strSql

So as a simple example, I am trying to us an IF statement to look at Text1 and determine if it is less than Text3, if it is I then want to insert another record into Text1 as 9999.

I have yet to use the IF statement and get it to work. If somebody has a simple example that could show me how to implement this, that would be great. Thanks for your help.

 

[namliam]

Again, why create hard records? Why not do the UNION?

Now an if like that is simply a where clause...


INSERT INTO Table1 (Text1) Select 9999 from table1 where Table1.[Text1] < Table1.[Text3]

Seriously look into the UNION thing... it is MUCH better than duplicating records.