Unmatched query on 1 table

[tmcrouse]

I have one table that is supplied monthly to me. I did not build this table. IT has it built already and I am just querying from it. So I cannot chng the table layout. The table consists of the following information: year/month, site/skd, category, quantity, charge. I need to create a query that views what happened in a prior month, let's take jan2010 and see if anything changed in current month, let's take feb2010. They want information if a new site/skd, category was added and if so the charge. I have never done an unmatched query on 1 table. So I did it like this:



Make table:

Query1:

select [year/month], [site/skd], [category], sum([charge]) as sumofcharge
into Chrg1
from table
group by [year/month], [site/skd], [category]
having (([year/month])=[Enter State Date]);

Query2:

select [year/month], [site/skd], [category], sum([charge]) as sumofcharge
into Chrg2
from table
group by [year/month], [site/skd], [category]
having (([year/month])=[Enter End Date]);

Now I have two tables to run the unmatched to:

Query 3:

Select Chrg2.[year/month], Chrg2.[site/skd], Chrg2.category, Chrg2.sumofcharge
from Chrg2 left join Chrg1 on (Chrg2.cateogry = Chrg1.category) and (Chrg2.[site/skd] = Chrg1.[site/skd])
where (((Chrg1.[site/skd]) is null) and (Chrg1.category) is null))
order by Chrg2.[site/skd];

The reason I am posting is because this is a 3 step process and there are other tables they want the same thing done which are not affiliated with this table and I wanted to find something that might be a 1 query process so the queries are not so much. I did give explanations in the properties so they would understand the process I set up. I just thought there might be an all in one query that I could accomplish the same thing.

Thanks!!!!

[June7]

You could try nesting the queries.

Select Chrg2.[year/month], Chrg2.[site/skd], Chrg2.category, Chrg2.sumofcharge
from

(select [year/month], [site/skd], [category], sum([charge]) as sumofcharge from table
group by [year/month], [site/skd], [category]
having [year/month]=[Enter End Date])
As Chrg2

left join

(select [year/month], [site/skd], [category], sum([charge]) as sumofcharge
from table
group by [year/month], [site/skd], [category]
having [year/month]=[Enter State Date])
As Chrg1

on (Chrg2.category = Chrg1.category) and (Chrg2.[site/skd] = Chrg1.[site/skd])
where (((Chrg1.[site/skd]) is null) and (Chrg1.category) is null))
order by Chrg2.[site/skd];

[tmcrouse]

Well that will not work because the main table is not chrg1 and chrg2. That ends up the table names after I do a make table query off the one main table called table. There is only 1 table that exists. The table name I am using for this example because this is work related and do not want to violate privacy stuff. So the original table name is chngd as well as actual column names so I dont get into trouble.

I have 1 table. It's name we will say is table

table has 5 columns they are year/month, site/skd, category, quantity and charge.

I wanted to do unmatched query on the above and the only way I can do it is by creating 2 make table queries and doing unmatched on them.

If I go ahead and nest I still have to do the 2 make table queries so I might as well stick with what I got.

[June7]

I don't understand the need to make tables. Queries can be used in queries just like tables.

[ssanfu]

Well that will not work because the main table is not chrg1 and chrg2. <snip>

Before you say it won't work, did you try it??? Change "table" to your table name, then execute it.

Instead of using "make table" queries (ugh - can you say massive bloat?), June7 has given you an all query solution.

BTW, this nested query cannot be constructed in the query design grid. You must enter it using the query SQL view.

The query uses aliases - chrg1 & chrg2 - for the sub queries. If you really analyze the query June7 provided, you will see that it is the same unmatched query as yours, but without the make table.

And if you have a form and button to execute the query, you could have two controls to enter the start and end dates before you click on the button.

OK, so now it is time for and

[June7]

Thank you ssanfu for expanding my suggestion. I should have mentioned how to build the nested query.

When I want a nested query, I will build the inner queries first and save them. Then I build the outer query using the first queries. So far the query designer does the job. Now to get the nesting, I switch to SQL View and copy/paste the first queries sql as shown in my example.

[tmcrouse]

Thanks but I think that no one understands what is going on. I only have 1 table. The table is called Monthly Charges. It houses all charges beginning 1-2010 to present. So to compare 2-2010 charges to 1-2010 charges I cannot do the nested query above because there is only 1 table. The column names are and supplied example data:

Year/Month Site/Skd Category Quantity Charge

2010/01 VRGHM1 Postage 12 $15.00
2010/01 VRGHM1 Print 100 $502.00
2010/02 VRGHM1 Postage 15 $17.00
2010/02 VRGHM1 Print 105 $507.00

I am trying to create 1 query only. Just 1. This query is used to compare what the charge difference is between the current month and prior month. I could be comparing Feb 2011 data to Jan 2011 data. The table is massive with so far 109000k rows. So I need the user to be able to input the start date, end date and compare it all. This is why I did it the way I did it above. I have tried this nested query but it does not work because there is only 1 table, not 2.

[tmcrouse]

BTW I did try it June 7's way but it still means 3 steps. My way is 3 steps. I was trying to do it all in 1 step. Just 1 query that does it all from the beginning. With no need to create query1 that is chrg1 and then query2 that is chrg2 and then nesting the queries. That equals 3 steps. I wanted to take the main Monthly Charges table and do all of this in 1 SQL query. 1 step only. That was my goal.