Solved Problem limiting to unique records in Query (1 Viewer)

Bean Machine

Member
Local time
Today, 00:43
Joined
Feb 6, 2020
Messages
98
Hi all! I have two queries, one detailing the sum of revenue over three years and the other detailing the sum of expenses over three years. I want to have a query that shows the two sums beside each other along with the years. The two queries both only have three entries, but when I combine the fields in a new query the results are tripled for whatever reason. I am not sure what is causing this issue but I only want to see the unique values within the combined query. I tried using the "SELECT DISTINCT" expression in the SQL but to no avail. I will include images to hopefully give some more background on what I'm trying to accomplish.
 

Attachments

  • query 1 revenue.png
    query 1 revenue.png
    5.6 KB · Views: 294
  • Query 2 Expenses.png
    Query 2 Expenses.png
    5.4 KB · Views: 293
  • Query 3 Combo query with repeat values.png
    Query 3 Combo query with repeat values.png
    13.9 KB · Views: 308

GinaWhipp

AWF VIP
Local time
Today, 00:43
Joined
Jun 21, 2011
Messages
5,901
I would say it what you are joining on (or not joining on). Please post eh SQL of Query 3.
 

Bean Machine

Member
Local time
Today, 00:43
Joined
Feb 6, 2020
Messages
98
Here is the SQL for Query 3
 

Attachments

  • SQL Query 3 Combo Query.png
    SQL Query 3 Combo Query.png
    6.7 KB · Views: 293

Gasman

Enthusiastic Amateur
Local time
Today, 04:43
Joined
Sep 21, 2011
Messages
14,048
Please post actual code and NOT a pretty picture, which is tiny anyway? :(
No one can amend that to help you?
Surely it is easier to copy that code than produce a picture?
 

GinaWhipp

AWF VIP
Local time
Today, 00:43
Joined
Jun 21, 2011
Messages
5,901
Did you just drop them in the window? Where is the JOIN?
 

Bean Machine

Member
Local time
Today, 00:43
Joined
Feb 6, 2020
Messages
98
The JOIN is what I was missing, lol. Sorry I'm operating on negative amounts of sleep right now, just been working on this personal project on the side with bogus data. Thanks for the help, sorry I'm stupid 😓.
 

plog

Banishment Pending
Local time
Yesterday, 23:43
Joined
May 11, 2011
Messages
11,613
You're building queries on top of queries and then throwing SQL keywords at it just hoping to come up with something. I suggest you give us starting data from your tables and what you expect the results to be. So please provide 2 sets of data:

A. STarting data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what data you expect the final query to produce when you feed it the data from A.

Again, 2 sets of data, not SQL and a description. STarting and expected data.
 

GinaWhipp

AWF VIP
Local time
Today, 00:43
Joined
Jun 21, 2011
Messages
5,901
The JOIN is what I was missing, lol. Sorry I'm operating on negative amounts of sleep right now, just been working on this personal project on the side with bogus data. Thanks for the help, sorry I'm stupid 😓.
Nope, not stupid, been there and done that. ;) Get some sleep or some coffee... your choice.
 

Users who are viewing this thread

Top Bottom