Append Through a Query?

[DanG]

Not sure if my approach is right, but here goes...

I haev to tables:
1. tblMSR (PK is MSRID (autonumber))
2. tblPrntReq (FK is FKMSRID) (tblPrntReq is sub of tblMSR)

I want to automatically add records in bulk to these two tables. So the first thing I want to do is add one record to the tblMSR table and then if possible add one record to tblPrntReq (only one record and never more). I would prefer to do this in one pass, but it seems you can only append records to a TABLE and not a QUERY. I was thinking appending to query because then I could combine both tables since I was just adding 1 record to each table.

I have all the information to feed the two tables in one query, but break it into two if needed.

Any ideas would be appreciated.

Thank you

 

[SOS]

1. You can append records to queries (if they are updateable).

2. By joining the two tables your query may not be updateable and

3. Unless they are a one-to-one relationship and not one-to-many, you should do them separate anyway with the parent record first and the child record second. In fact, you might have to do that anyway just due to that one record may need to be in the one table before you can add a record in the related table.

 

[MSAccessRookie]

Not sure if my approach is right, but here goes...

I haev to tables:
1. tblMSR (PK is MSRID (autonumber))
2. tblPrntReq (FK is FKMSRID) (tblPrntReq is sub of tblMSR)

I want to automatically add records in bulk to these two tables. So the first thing I want to do is add one record to the tblMSR table and then if possible add one record to tblPrntReq (only one record and never more). I would prefer to do this in one pass, but it seems you can only append records to a TABLE and not a QUERY. I was thinking appending to query because then I could combine both tables since I was just adding 1 record to each table.

I have all the information to feed the two tables in one query, but break it into two if needed.

Any ideas would be appreciated.

Thank you

In order to update the Tables together in a single Query, there must be a common Column to Join the Tables on, and the situation satisfies that requirement (MSRID = FKMSRID). The two tables must also have a 1:1 relationship, and the situation satisfies that requirement (only one record (in tblPrntReq) and never more). Therefore you should be create an append Query that satisfies your request.

NOTE: I see that SOS beat me to the post, and agrees with me (sort of).

 

[DanG]

When I combine my to two tables in a query, it appears to be updateable. It's just that in the QBE where you choose your query type and it ask you to choose an append table, the choice is only a table and doesn't offer queries?

 

[SOS]

When I combine my to two tables in a query, it appears to be updateable. It's just that in the QBE where you choose your query type and it ask you to choose an append table, the choice is only a table and doesn't offer queries?

That is a limitation of the "wizards" but if you create an append query to a "table" you can go into the SQL view and change it to use the query.

 

[MSAccessRookie]

When I combine my to two tables in a query, it appears to be updateable. It's just that in the QBE where you choose your query type and it ask you to choose an append table, the choice is only a table and doesn't offer queries?

Near the top of the QBE window there is an icon (Query Type), that looks like two overlapping datasheets. Choosing that Icon will allow you to do what you want most of the time. Note that Access is often imperfect in its coding choices and you may need to go to the SQL Mode if the Query is not exactly what you are looking for.

 

[DanG]

Thanks guys!
I was thinking I could go into the SQL and just change the table reference to the query of my choice, you just confirmed it.

Thanks!