Solved Combining two filtered queries dont show uncommon data (1 Viewer)

[Ihk]

Hi,
I have two queries originating from same table.
which perform following (as shown in picture below)
1) filtering
these two queries bring specific filtered data, for example based on status "Processing" or "Done"
2) grouping
but the rest of similar data is grouped in each query.
3) Price in total , summed up
Note: Fields (Field1, Field2, Field3) are grouped and they are must have to be linked as per row. That's why they have relationship.

Problem:
as shown above, in combined query (where I want total of price, based on groups of Fields),
Each query has 4 rows (grouped data), but in combination is only three rows data, because the two are not common in these. But I want those as well.
How is possible? As shown in red rectangle.
Thanks

 

[theDBguy]

Hi. Check out UNION queries. Hope that helps...

 

[plog]

I think you should do this with just 1 query:

SELECT Field1, Field2, Field3
    , SUM(iif(Status="Processing", Price, 0)) AS ProcessingPrice
    , SUM(iif(Status="Done", Price, 0)) AS DonePrice
    , SUM(Price) AS TotalSum
FROM YourTableNameHere
WHERE Status="Processing" OR Status="Done"        
GROUP BY Field1, Field2, Field3

 

[Ihk]

Hi. Check out UNION queries. Hope that helps...

Thank you for this Idea, I tried this with "UNION" alone, inspite of this , fields were not grouped because of last column for "SumofPrice" which made each record double.

 

[Ihk]

I think you should do this with just 1 query:

SELECT Field1, Field2, Field3
    , SUM(iif(Status="Processing", Price, 0)) AS ProcessingPrice
    , SUM(iif(Status="Done", Price, 0)) AS DonePrice
    , SUM(Price) AS TotalSum
FROM YourTableNameHere
WHERE Status="Processing" OR Status="Done"       
GROUP BY Field1, Field2, Field3

Thank you very much, It is very kind of you. It really worked like amazing in a single query. Wish you best wishes.

 

[theDBguy]

Thank you for this Idea, I tried this with "UNION" alone, inspite of this , fields were not grouped because of last column for "SumofPrice" which made each record double.

Hi. Sorry to hear the UNION idea didn't work out, but I'm glad @plog came to the rescue for us.

Good luck with your project.

 

[Ihk]

Hi. Sorry to hear the UNION idea didn't work out, but I'm glad @plog came to the rescue for us.

Good luck with your project.

Thank you very much for you support

 

[Ihk]

@plog @theDBguy
As I was working, I came up with another question extactly related to above. @plog answer rescued me, it was perfect for my above question.
Now additional question is.
Above "table1" was related to expenditure price.
Now I have another table2, which has funds related to exactly those fields, which are grouped in above. and want to substract for the same group level. (because "Field1, Field2, Field3" are common in both tables)

Explanation below::
This "Table2" which has Same fields as above "Field1, Field2, Field3" but with an additional field "Funds" (that means money available)
Question::
Separate query for available funds for above fields, is okay, it can tell me "totalFunds" as per group level.

NOW I want to combine these two tables, to have idea How much is available now. Meaning From above code "TotalSum" will be susbtracted from new field "totalFunds" in a new Field "Balance"
When I combine these two queries to make another query, those data disappear which is not common in both.
ie
If funds have not been entered for any group "Field1, Field2, Field3" , meaning data does not exist in Table2, then All data will not appear.
But I want here automatically "TotalFunds" Should appear zero and new field "Balance" should show negative values.
Hope I was able to explain.
Thank you.

 

[plog]

Its starting to sound like you have improperly set up your tables. Usually credits and debits are stored in the same table and the same field and you simply sum up that one field.

Can you post a sample of your database?

 

[Ihk]

Its starting to sound like you have improperly set up your tables. Usually credits and debits are stored in the same table and the same field and you simply sum up that one field.

Can you post a sample of your database?

Thank you for your reply, In about 10-20 minutes. I will post sample database.

 

[Ihk]

@plog Thank you
Please find attached sample database.
This has three queries,
All these queries have different number of record rows. but have common three fields (Field1,Field2,Field3) as group.
-ExpenseQuery is working perfect.
-FundsQuery as well good.
But
in BalanceQuery when I want to substract the difference between Expense and Fundsavailable. It does show only the matching records, but it does not show extra records available in either of the query (reocord which is not common).

NOTE: there are two different tables
One for Products (MainTbl) and their prices, and
Other for funds available (FundEntryTbl) under each category (Field1,Field2,Field3)
These three fields have relations. (More better view under relationship). Thank you

 

[plog]

You have a table named Maintbl, a field named Field1 and a date time field named DateTime; That is some damn fine vague naming you've done there. You really need to name your tables better. What date and time does the DateTime represent? Prefix or suffix the field name with that (e.g. SalesDateTime). You should do this with your ID fields as well, prefix them with what table they are from (Funds_ID, Main_ID, etc.). It will help with coding and querying later on.

I do see a big fundamental error that is going to have repercussions for this issue:

Tables with the same structure. The data in Field1tble, Field2tble and Field3tble should all be housed in just 1 table. That table will have the same structure as 1 of the existing tables with a new field to help distinguished which Field number the data is a part of.

When you fix that it will mean you probably change Maintbl as well, it might mean a new table to keep track of whatever those FieldXTables represent.

Since I can't glean it from your table and field names, I am going to need a few sentences about what this database is for. What is its purpose? What are all those FieldXtbles for? Every table has a USER and a DateandTime field--is that to keep track of who added/edited the data?