asked
UNION ALL on append queries?
I have to write an append query that will append to two different table it seems. I'm using access. The problem: All Season has just placed an order for 10 cordless drills, for a price of $115.00. Use 21624 for the order number and the current date for their order date. Modify the database. The Item number, price, order number, price, and number ordered go on one table; OrderItem. The Order Number, date, and Customer number (all season) go on another table; Orders How can I append to these two tables without making two separate queries? Is that even possible? Thanks in advance!
Is this a school/college homework question? It's not a problem if it is but we want to support students by helping them understand problems and solutions rather than just give out answers. Let us know what you have tried and what you think the solution you need to create should look like.
No. One query in Access cannot insert in to multiple tables. This is where VBA is useful. Sounds like you would be best to create a process in VBA that did this for you. Something like docmd.runSQL("Insert into Orders....") followed by docmd.runsql("Insert into OrderItem....") There are many other ways to fire SQL in Access. If you do this and us you use variables just be aware of SQL injection. If you can, I will always recommend you move away from Access. Are you using the Access ACE Engine or are you using SQL Server? If SQL Server then you have a lot more avenues to explore. In particular use a stored proc and get Access to just call the SP. Hope that helps.
16 People are following this question.
