How to construct a subquery to make the difference between queries?

[gbab]

Hi everybody !

I am new with Access and I hope I can find some help here with my issue.

I have 3 queries :
1. One to select data from the current month
2. One to select data from the current month - 1
3. One to make the difference between these 2 queries/tables

I have to insert the last query in one global query without having to create the 3 queries. The idea will be :

Select data from table where date=Month(Now())
MINUS
Select data from table where date=Month(Now())-1

I don't find the way to do this MINUS. Can you help me please? Is it better to construct subqueries?

Feel free to contact me for clarification.

Thanks a lot
Guillaume

 

[ByteMyzer]

Feel free to contact me for clarification.

Okay, for clarification, then, is this for a school assignment? The indicator to me that this might be the case is your statement: "I have to insert the last query in one global query without having to create the 3 queries."

 

[gbab]

No this is not a school assignment. I am working on a marketing desk to create a database that performs queries on imported data to follow-up the commercial activity. I have therefore to find a solution to calculate the monthly result by the difference of the YTD results, i.e. Monthly resullts = YTD - YTD(n-1) with n the current month. This query will be inserted in a "report" query that calculate other results.
How can I do it? My first idea was this code :

Select results from table where date=Month(Now())
MINUS
Select results from table where date=Month(Now())-1

What do you think about it?

Thanks
Guillaume

 

[ByteMyzer]

I think more specific information is needed from you before we can offer you an answer that will effectively help you toward a working solution.

Could you post the exact SQL syntax of the Queries you have so far?

 

[gbab]

Hi there,

Here is my existing SQL syntax :

1. Query Monthly data

SELECT [YTD data].[RM code], [YTD data].[Net new cash_YTD]-[YTD(n-1) data].[Net new cash_YTD] AS [Monthly NNC]
INNER JOIN [YTD(n-1) data] ON [YTD data].[RM code] = [YTD(n-1) data].[RM code]
GROUP BY [YTD data].[RM code], [YTD data].[Net new cash_YTD], [YTD(n-1) data].[Net new cash_YTD]
ORDER BY [YTD data].[RM code]

This Monthly query is based on the 2 following queries :

2. Query YTD data :
SELECT [Monthly Renta - Onshore and Offshore].[RM code], [Monthly Renta - Onshore and Offshore].[Net new cash_YTD]
FROM [Monthly Renta - Onshore and Offshore]
WHERE (((Month([Processing month/year]))=Month(Now())-1))

3. Query YTD(n-1) data :
SELECT [Monthly Renta - Onshore and Offshore].[RM code], [Monthly Renta - Onshore and Offshore].[Net new cash_YTD]
FROM [Monthly Renta - Onshore and Offshore]
WHERE (((Month([Processing month/year]))=Month(Now())-2))
ORDER BY [Monthly Renta - Onshore and Offshore].[RM code]

How can it be possible to group these queries in one global query ?

Thanks a lot for your help
Guillaume

 

[ByteMyzer]

See if this works for you:

SELECT T1.[RM code],
Sum(Iif((T1.[Processing month/year]-Date() < 365) And (Month(T1.[Processing month/year])=Month(Date())-1),T1.[Net new cash_YTD],0)) -
Sum(Iif((T1.[Processing month/year]-Date() < 365) And (Month(T1.[Processing month/year])=Month(Date())-2),T1.[Net new cash_YTD],0)) AS [Monthly NNC]
FROM [Monthly Renta - Onshore and Offshore] AS T1
GROUP BY T1.[RM code];

 

[gbab]

Thank you very much it works very well to calculate the monhtly results of June. But what changes are needed in this code to calculate the monthly results of another month, i.e. how can I calculate the Monthly resullts = YTD - YTD(n-1) with n the processing month/year and not the current month ?

We are very near to find the solution. Thanks for your help.

Guillaume

PS : sorry for the delay in my replies. I am working in Dubai and there is +8h of jetlag compared with US.

 

[ByteMyzer]

In the SQL string, replace all instances of Date() with CDate([Input Date]). Then you can specify the date against which you wish to run the Query.

 

[gbab]

Hey,

It works perfectly.

Thanks a lot
Guillaume

 

[ByteMyzer]

You're very welcome. I'm glad it worked for you