Merging two queries? Help!

[AccessChap]

Hi guys,

I've got two queries, one for a Peugeot franchise and one for a Citroen franchise. Apart from the table name they are the same query. But can I merge the two queries into one for a graph of grand totals? Nope! Anyone got any ideas? The two queries are listed below and I basically want to return the sum of these two queries i.e. one row of results to give me a "total" graph to accompany my Peugeot and Citroen graphs...

SELECT Format(Date_Logged,"mm " & "mmm " & "yy") AS [Month],
Count(tbl_Security_Request.Record_ID) AS [Security Requests]
FROM tbl_Security_Request
WHERE (((tbl_Security_Request.Date_Logged)>Now()-365))
GROUP BY Format(Date_Logged,"mm " & "mmm " & "yy"),
Right$([tbl_Security_Request]![Date_Logged],4) &
Mid([tbl_Security_Request]![Date_Logged],4,2)
ORDER BY Right$([tbl_Security_Request]![Date_Logged],4) &
Mid([tbl_Security_Request]![Date_Logged],4,2);

SELECT Format(Date_Logged,"mm " & "mmm " & "yy") AS [Month],
Count(tbl_Security_Request_Citroen.Record_ID) AS [Security Requests]
FROM tbl_Security_Request_Citroen
WHERE (((tbl_Security_Request_Citroen.Date_Logged)>Now()-365))
GROUP BY Format(Date_Logged,"mm " & "mmm " & "yy"),
Right$([tbl_Security_Request_Citroen]![Date_Logged],4) &
Mid([tbl_Security_Request_Citroen]![Date_Logged],4,2)
ORDER BY Right$([tbl_Security_Request_Citroen]![Date_Logged],4) &
Mid([tbl_Security_Request_Citroen]![Date_Logged],4,2);

As you can see the queries aren't rocket science and I suspect I'm getting stuck on the syntax as much as anything. Any help or suggestions much appreciated

Thanks,

Andy

 

[Atomic Shrimp]

You could create a union query - something like:

SELECT * FROM tbl_Security_Request_Citroen
UNION
SELECT * FROM tbl_Security_Request

Then save that query as, say, 'qryBanana'

Then run a single summary query on it, similar to the ones you already have - something like:

SELECT Format(Date_Logged,"mm " & "mmm " & "yy") AS [Month],
Count(qryBanana.Record_ID) AS [Security Requests]
FROM qryBanana
WHERE (((qryBanana.Date_Logged)>Now()-365))
GROUP BY Format(Date_Logged,"mm " & "mmm " & "yy"),
Right$([qryBanana]![Date_Logged],4) &
Mid([qryBanana]![Date_Logged],4,2)
ORDER BY Right$([qryBanana]![Date_Logged],4) &
Mid([qryBanana]![Date_Logged],4,2);

BUT

You're facing this problem because you're storing the same kind of data in separate tables. You really shouldn't do that - you could just have one big table called 'security requests', with an extra column to define the car manufacturer for each row's data - then you can run queries against the whole table to get summary data for all manufacturers, or put in criteria if you want a summary of just one.

 

[AccessChap]

Thanks for the swift reply, I'll give that a go. Re the data storage, yeah I know but the data is only residing in this table temporarily. Ultimately it will be merged but that process is still some way off...

Thanks again,

Andy

 

[AccessChap]

OK tried that and it works a treat

Nice piece of lateral thinking that! Thanks again for the help,

Andy

 

[DCrake]

I know you said that the merging of the data was a long way off, but have you considered how much development will take place between now and then. What will be the implications of trawling through all your app to make the changes. Why not simply do it now whilst the app is in its infancy. Less work to do. Fail to prepare and prepare to fail.

David