Display grouped results with and without a WHERE clause (1 Viewer)

Hi

First time on here. I’m not an expert coder or programmer, merely dabble with the application, but I’m trying to write a query that can output grouped results from the original query and with a WHERE clause together. It seems like you can have one or the other but not both together in the same query.

Is it even possible?

Thanks.

SpA said:

Hi

First time on here. I’m not an expert coder or programmer, merely dabble with the application, but I’m trying to write a query that can output grouped results from the original query and with a WHERE clause together. It seems like you can have one or the other but not both together in the same query.

Is it even possible?

Thanks.

Click to expand...

Need a bit more background information. Why are you grouping? Is it for a report? Is it for summing particular groups, returning a subtotal per group? Something else?

Oh, BTW welcome to AWF!

Cheers Tony...

Hi
Welcome to AWF.
Yes its not only possible but a common type of query to GROUP with WHERE. Here's one random example



If for some reason you want both with and without the where clause in one query, you could make both queries and UNION them together... but I can't see any logical reason for doing that as the filter is effectively ignored and you will get duplicates

SpA,

W3Schools has several tutorials and examples on many programming topics.
Here is one on SQL Group By showing Where, Group By and Order By syntax with examples.

Welcome to the forum and good luck with your project.

Thanks all for the very quick replies.

To add some more to my question, I’ll try to give a simplistic example.

I have four fields in the same table; Date, Number, Miles & Yes/No

The date field is self explanatory, the Number field is a series say from 1 to 10, Miles is the amount of miles recorded against a number for a given date and Yes/No is particular to the date.

For example, the table would look something like this;

Date Number Miles Yes/No

01/01/19 2 5 Y
02/01/19 2 3 N
03/01/19 3 3 Y
03/01/19 4 4 Y
04/01/19 4 8 N

The formatting probably won’t work well with spaces but I’m trying this with my iPhone.

The first query is without a WHERE clause and would sum and group each number so, for example, number 2 would equal 8 miles, number 4 would equal 12 miles, ignoring the yes/no field and creating a cumulative result for each number. This is fine.

The second query does the same as query above but includes a WHERE clause that only sums the miles if record has a Y in it. For example, number 2 would now equal 5 miles, number 4 would equal 4 miles and so on. This is also great for what I want.

The challenge is to to create a query that can show both results in separate columns side by side.

Hope that makes a little more sense.

Thanks again for any advice.

Date and Number are reserved words in Access and should not be used for field or variable names. Also, Access does not like special characters (the "/") i n Yes/No.
I suggest you review the link I provided in earlier post and try some of the SQL Group By and Where examples.
You may want to investigate UNION query.

Here's how you can help us, help you: Provide us 2 sets of data--starting data from your tables and expected results from you query.

2 sets of data:

A. Starting data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results of the data in A. Show us what data you expect to end up with when you feed in the data from A.

Again, data; 2 sets of it.

Plog
I think the OP has effectively supplied that in post #5

Spa
As already stated you really need to rename three of your fields.

Make two queries similar to that in my first reply. Group by Date and Number and Sum the Miles field
Add the where filter to one of them.
Save both queries as Q1 & Q2 or similar

Now make a third query Q3 and add the first two queries joining by the two grouped fields.
Add the Sum field from each query and save it.

Note that if the filtered query has fewer records, use a left join in Q3 selecting all records from the unfiltered query.

Thanks again all

Some interesting things there to explore. I'll have a dabble over the next few nights or so and will report back on findings.

Hi. Welcome to AWF!


You could try the following steps:

1. Query1 is the one without the WHERE clause
2. Query2 is the one with a WHERE clause
3. Query3 joins both Query1 and Query2 on Date and Number fields

In Query3, you can pull the Date, Number, and Sum fields from Query1 and then add the other Sum field from Query2 as another column.


Hope that helps...

DBG
See post #8

Spa
Reading your earlier post again it appears the date can be ignored in the grouping.
If so, the attached should do what you want

isladogs said:

DBG
See post #8

Click to expand...

Ah, got ya! Sorry.