Combining queries..

[Anthony88]

Hey there,

I need a little help trying to combine two similar queries to save time. The current SQL codes are as follows:

UPDATE cableconductor INNER JOIN [eMax Prices] ON cableconductor.[Item No] = [eMax Prices].[Item No]
SET cableconductor.Cablecostperfoot = [eMax Prices].[Unit Cost]*[cableconductor].[Multiplier];

and...


UPDATE cableconductor INNER JOIN [eMax Prices] ON cableconductor.[Neutral Item No]=[eMax Prices].[Item No]
SET cableconductor.Cablecostperfoot = cableconductor.Cablecostperfoot+[eMax Prices].[Unit Cost]*cableconductor.[Neutral Multiplier];



They are obviously very similar, just a couple of changes as to where the data comes from. Still new to Access, is there some sort of "reset" I can place in between the two? Thanks in advance.

[R_Badger]

Do you wish to run both update queries concurrently?

It is however easy to run both queries using vb, if you have them saved

docmd.openquery "Qry_update1"
docmd.openquery "Qry_update2"

I don't quite see how as you are joining each differently and updating the same field.

[Anthony88]

Not concurrently. I would like to run the first, then second. I wasn't sure if it was possible since I was basically re-updating the same cells. I will be using these queries quite oftern and just thought it would be easier, and faster to run one query instead of two all the time.

[R_Badger]

The easiest thing to do then assuming you have them saved as queries in access is insert the vb as described above into a command button (right click -> build event -> code builder).

You can also disable the run action query warning using:
Application.setoption "confirm action queries", 0


For more info on docmd.openquery:
http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx

The total should look something like

Private sub Command0_Click()
Application.setoption "confirm action queries", 0
docmd.openquery "qry_update1"
'remark here because i remember i had problems running them back to back, but that may just be im trying to run on an antiquated system
docmd.openquery "qry_update2"
'remark here same as above
Application.setoption "confirm action queries", 1
end sub

[Kirsti]

Another quick way to do it (and maybe easier if you're not too familiar with VBA) is to create a Macro. Action1: OpenQuery (Query Name: query1) Action2: Close (Object Type: Query, Object Name: query1). Action3: OpenQuery (Query Name: query2) Action2: Close (Object Type: Query, Object Name: query2).

Then you can just run the macro once, rather than having to run both queries.