seeking solution to creating either a form/report in Access

[sk88]

I was wondering if I can build something like this in Access.
I have a table which contains field like:
City l Province l NumberPopulation l QuarterYear
Los Angeles l California l 300 l Q1, 2008
Denver l Colorado l 300 l Q4, 2009
Columbus l Ohio l 300 l Q2, 2008
Charlotte l North Carolina l 200 l Q3, 2009
Charlotte l North Carolina l 200 l Q1, 2008
Columbus l Ohio l 300 l Q3, 2009

and so on.


I want to create a report maybe or form which will give me output with two tables. The first table summarizes the data of all record. Example.
I want to have something like
City l Province l NumberPopulation l QuarterYear
but only show the City or province or quarters that I choose
Example,
If I choose Denver or Q4 2009, it should give me output like
Denver l Colorado l 300 l Q4, 2009

Now the next table should have similar headings
City l Province l NumberPopulation l QuarterYear
but gives me an output of ALL minus Denver
example:

All l All l 500 l Q1, 2008
All l All l 300 l Q2, 2008
All l All l 0 l Q3, 2008
All l All l 0 l Q4, 2008
All l All l 0 l Q1, 2009
All l All l 0 l Q2, 2009
All l All l 500 l Q3, 2009
All l All l 0 l Q4, 2009
Over here you would see Q4, 2009 is 0 because I chose Denver in my first table.

I know it is confusing. I hope someone might come up with some bright ideas.
Thank you

[Robeen]

Create your Table and put all your data in.
Then you can create either two queries that will give you the two different Datasets you want

OR

you can create one Union Query that displays first the records WITH the City you want and then the records WITHOUT the City that you displayed in your first part.

Then you can use the Query to create a Report that will Show Denver as one Group and 'All' as the next Group.

Let me know if you need more detail or if you have any questions.

[rpeare]

It sounds to me like you're trying to make a table out of summarized data which I really would not recommend. Storing calculated values is almost never a good idea, particularly if the data changes frequently. You can do what you want with queries. Here is a simple example of doing it on a form. The queries running each of the list boxes can be used to run reports or to export etc.

[sk88]

Thanks to you two.. I will definitely try them out soon and see which one...