Subquery?

[Xina5280]

Im very novice at SQL and Access in general and I am having difficulty doing a subquery from a union query. Here is the code I have so far in my union query:

select Phase, [Date], Price
from [Corner Star Tickets]
UNION select Phase, [Date], Price
from [Corner Star Invoices];

*The reason I have [Date] is because nothing else worked and I kept getting syntax errors any time I tried to change the column name.

Ok now for my problem....I have been trying multiple code to Sum the Price from both tables together. I would need to have it sort by phase, group by month, sum totals for grouped month then do a total sum of all months...Does that make sense? If you could supply the code that would be fabulous and make my life much easier (well at least until I move on to the next step)! Thanks

 

[pbaldy]

I wouldn't bother with a subquery, as it can get hard to read. Save the union query as is, and base another query on it, grouping and summing as appropriate. I would simply do the overall total on a form or report.

 

[Xina5280]

Now why didnt I think of that?

That worked beautifuly! Except for the fact that the SQL code is showing up in random sections on the report. Is there any way to run the report without having that show up?

 

[pbaldy]

I'm not sure what you mean. The SQL itself shouldn't show up anywhere in the visible report. Can you post a picture or sample db?

 

[Xina5280]

Ok looking at it again isnt code per se but its the "summary for 'date by month'= april 08" etc... I attempted to do a screenshot but i have no idea how to get it down to the necessary 100 kb.

 

[pbaldy]

Did you make it a jpg instead of bmp? Those are usually small enough.

 

[Xina5280]

Haha! Genius!

 

[pbaldy]

Ahh; that looks like something the report wizard did. It should be in a textbox, allowing you to modify it to what you want.

 

[Xina5280]

Yes it was and I deleated all the junk out. I was hoping that there was a way to not have it show up like that....if not I can live with deleting it out Thank you so much though!