Group by Query (1 Viewer)

[AlexN]

I have a table (tblSales) like the one shown.

I want to group it by SalesDate, SalesPerson and Product, summarising the Amount, not necessarily using all criteria at the same time, but criteria given by a form (or 3) with a combobox.


I want to be able to chart results in a report, and if possible, to be able to compare results (by all three parameters) on the same report.
Since my poor mind has become short of ideas, and knowledge is limited, any help would be appreciated.

Thank you​

 

[JHB]

...I want to group it by SalesDate, SalesPerson and Product, summarising the Amount,​

​

So far I can see you already have it group like you want it.

 

[AlexN]

[/LEFT]
[/CENTER]
So far I can see you already have it group like you want it.

Maybe I didn't exress it right. I want to be able to show the total Amount for every SalesDate, the total Amount for every SalesPerson, and the total Amount for every Product, each one separately, and/or all together at the same time.
Like answers to questions, how much did we sale on this Date (or Month), how much did SalesPersonA sale last month, or this month or that specific date, or how much of Product1 did we sale this quarter.


All this information should be presented by charts on a report.


Thank you

 

[JHB]

Create a form in which you can choose the different parameters you want to show in the graph.
Create a query which take in account what you've choose in the form.
Then create a report, place a graph in the report and based the data in the graph on the query.

 

[ConnorGiles]

Sorry to but in - But wouldn't a Crosstab query do the trick?

Then create a report to make it look nice

Crosstab queries can be used to show for specific dates/months/quaters/years

Depending on what you need.

 

[AlexN]

Sorry to but in - But wouldn't a Crosstab query do the trick?

Then create a report to make it look nice

Crosstab queries can be used to show for specific dates/months/quaters/years

Depending on what you need.

Sounds a good idea but……I think crosstab queries can’t handle three different parameters , (or at least that’s how far my experience goes), and then again how do you put the parameters in a crosstab query, using a form, being able to use one or more parameters. That’s where I lose it.

Create a form in which you can choose the different parameters you want to show in the graph.
Create a query which take in account what you've choose in the form.
Then create a report, place a graph in the report and based the data in the graph on the query.

Yes, I know the procedure but I just don't know how to create a query that depends on multiple criteria given by a form, and at the same time giving the opportunity to choose one or more criteria simultaneously.
Meantime, I created some queries but there’s something wrong. Every one of them has the same number of records the underlying table has. Shouldn’t it group the SalesDates for instance, or the SalesPerson or even Product? It’s just so confusing….



Thank you both

 

[ConnorGiles]

Sounds a good idea but……I think crosstab queries can’t handle three different parameters , (or at least that’s how far my experience goes), and then again how do you put the parameters in a crosstab query, using a form, being able to use one or more parameters. That’s where I lose it.

That's when you create a CrossTab for each individual parameter (one for date/month/year/etc...)

The same way you would a usual query - Go into design view, Make parameters (tab at the top inside design) - then use them as criteria

 

[AlexN]

That's when you create a CrossTab for each individual parameter (one for date/month/year/etc...)

The same way you would a usual query - Go into design view, Make parameters (tab at the top inside design) - then use them as criteria

Yeah but in that case, how do I get to show in a graph, how much sales of Product1 (or of Product1 and Product2) did SalesPersonA in first trimester for example, and in comparison with SalesPersonB. Please don’t say it’s impossible.


Edit
Meantime Access doesn't let me use more than 6 fields to create a chart from a crosstab query, making it impossible to get full information

 

[ConnorGiles]

Unfortunately, You will just have to create multiple cross tabs relating to different information.

Long process, But naming them accordingly would help a lot

Graphs can be made in any form/report - relating the graph to a crosstab query with parameters would do that for you. (I think! )

Would you be able to post a screen shot of what you mean here Alex?

 

[AlexN]

Unfortunately, You will just have to create multiple cross tabs relating to different information.

Long process, But naming them accordingly would help a lot

Graphs can be made in any form/report - relating the graph to a crosstab query with parameters would do that for you. (I think! )

Would you be able to post a screen shot of what you mean here Alex?

Well, that is a major problem since this project will be used by people other than me, more unfamiliar with Access than I am. How do I explain this to them.


Unfortunately, I use Access 2013 and I cannot create graph forms so I settle with reports only.

Screenshot of what?

 

[ConnorGiles]

you could of course change the formatting of the columns in the crosstab query design.

This would just take a bit of learning the formatting of dates and so forth.

In 2013 you can choose add chart, Do a bit of exploring - (If what you mean by graph is chart of course).

Screenshot of your 6 fields I'm not quite sure what you are describing to me.

 

[AlexN]

you could of course change the formatting of the columns in the crosstab query design.

This would just take a bit of learning the formatting of dates and so forth.

In 2013 you can choose add chart, Do a bit of exploring - (If what you mean by graph is chart of course).

Screenshot of your 6 fields I'm not quite sure what you are describing to me.

Yes by Graph I meant Chart, excuse my poor English.

Here's the message I get when I try to put all the fields in the chart

 

[ConnorGiles]

http://www.pcreview.co.uk/forums/inserting-more-than-6-fields-access-chart-t1692483.html

This should sort you out

Google is your friend

 

[AlexN]

http://www.pcreview.co.uk/forums/inserting-more-than-6-fields-access-chart-t1692483.html

This should sort you out

Google is your friend

Thank you so much!
I finally managed to add rest of the fields, and I even discovered the "AddChart" wizard in a form, which somehow wasn't there before.

You've been so helpful.

Still needing multiple crosstab queries and multiple charts though....

 

[ConnorGiles]

Anytime Alex,

Always here to help

You don't NEED multiple ones , but as you said

You aren't the only one using these forms. Making multiple and naming them with the functions they do may help such as "SalesDays""SalesMonths""SalesYears"

May help them to understand.

Or you could teach them how to reformat the box which refers to the formating of your crosstab :

"yyyy" (years) to "mmm" (Months) "dd/mm/yy" (all) etc.... - Ill try researching for you to see if there is an easier way post back soon

 

[Brianwarnock]

Yes, I know the procedure but I just don't know how to create a query that depends on multiple criteria given by a form, and at the same time giving the opportunity to choose one or more criteria simultaneously.

I know that this is no longer relevant to this thread but thought that you may find this useful to know.
I will use shorthand notation for clarity,
ie params1 is equivalent to forms!myform!txtbox1

To enter the following criteria switch to SQL view and NEVER save the query from design view as it will become unreadable.

Where ((myfielda = Param1) or param1 is null)
And ((myfielda = param2) or param2 is null)
And etc

For any criteria that is null then the field is not filtered as the criteria is met for all records. Some of the bracketing can be omitted but I put it in to help clarity, I think.

Brian

 

[AlexN]

I know that this is no longer relevant to this thread but thought that you may find this useful to know.
I will use shorthand notation for clarity,
ie params1 is equivalent to forms!myform!txtbox1

To enter the following criteria switch to SQL view and NEVER save the query from design view as it will become unreadable.

Where ((myfielda = Param1) or param1 is null)
And ((myfielda = param2) or param2 is null)
And etc

For any criteria that is null then the field is not filtered as the criteria is met for all records. Some of the bracketing can be omitted but I put it in to help clarity, I think.

Brian

Everything is relevant! I'm totally lost, been trying to put criteria in the crosstab query and getting nothing but confusion.
Don't thing the problem was null values though, form the messages I get, it has to do with the fact that my criteria is the column heading, or something like that if I understand it properly.
Fact is I'm done with ideas and ready to quit trying.
Thanks anyway, you've been e[FONT=&quot]nlightening[/FONT]

 

[AlexN]

Well, I finally managed to create a Chart based on crosstab data, chart form opens regularly with no parameter in, but, it's impossible to open chart form, after putting the parameter in the crosstab query. I keep getting a message saying :

The Microsoft Access database engine does not recognize " as a valid field name or expression

no matter what I do.
Help says I should use the proper data type for the parameter, but tried everything and had the same results

Any ideas?
Thank you

 

[ConnorGiles]

A screenshot would help us solve this Alex

 

[AlexN]

A screenshot would help us solve this Alex

Well,
There's a screenshot of what I'm getting, as well as sample database for anyone willing to help.

Thank you