Result changing when tables added to FROM (1 Viewer)

harshadborde

Registered User.
Local time
Tomorrow, 01:06
Joined
Apr 10, 2017
Messages
17
Cartesian Join: Result changing when tables added to FROM

Hello Friends,

Below are 2 quries, both are same with only difference is added table in FROM

SELECT [TableA].Storename, Sum([TableA].[Sum Of Total]) AS [SumOfSum Of Total], Sum([TableA].[Sum Of Q1 Revenue]) AS [SumOfSum Of Q1 Revenue]
FROM [TableA]
GROUP BY [T&M Table].Storename;

SELECT [TableA].Storename, Sum([TableA].[Sum Of Total]) AS [SumOfSum Of Total], Sum([TableA].[Sum Of Q1 Revenue]) AS [SumOfSum Of Q1 Revenue]
FROM [TableA],[TableB]
GROUP BY [T&M Table].Storename;

Both results are coming different even though I am fetching same field from same table, even though there is same table in Select. Kindly help.
 
Last edited:

Ranman256

Well-known member
Local time
Today, 15:36
Joined
Apr 9, 2015
Messages
4,339
No,it is not the same table.
You threw in an extra table,so results are now dependent on both tables.
 

plog

Banishment Pending
Local time
Today, 14:36
Joined
May 11, 2011
Messages
11,611
You have 2 different queries, you should expect 2 different results. The second query is called a cartesian join. Its effect is to multiple the total number of rows you have in A by the total number of rows you have in B. So the result of your second query should be larger than the first query.

The really odd thing is your GROUP BY clause. It should actually cause your query to fail to run. You are grouping by a field in a table that isn't part of your query.
 

harshadborde

Registered User.
Local time
Tomorrow, 01:06
Joined
Apr 10, 2017
Messages
17
You have 2 different queries, you should expect 2 different results. The second query is called a cartesian join. Its effect is to multiple the total number of rows you have in A by the total number of rows you have in B. So the result of your second query should be larger than the first query.

The really odd thing is your GROUP BY clause. It should actually cause your query to fail to run. You are grouping by a field in a table that isn't part of your query.

Sorry, You got 'group by' clause right.
Regarding cartesian join, How can I avoid it when I want to fetch 2 fields from 2 different tables in one query ?
 

plog

Banishment Pending
Local time
Today, 14:36
Joined
May 11, 2011
Messages
11,611
Generally you link your tables via a JOIN:

https://www.w3schools.com/sql/sql_join.asp

Perhaps if you'd like to post sample data to demonstrate what you hope to achieve. Provide 2 sets of data:

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

B. Expected results of A. Show what data you hope to end with when you start with the data in A. Don't explain what you hope to achieve, show what data you expect.
 

harshadborde

Registered User.
Local time
Tomorrow, 01:06
Joined
Apr 10, 2017
Messages
17
You have 2 different queries, you should expect 2 different results. The second query is called a cartesian join. Its effect is to multiple the total number of rows you have in A by the total number of rows you have in B. So the result of your second query should be larger than the first query.

The really odd thing is your GROUP BY clause. It should actually cause your query to fail to run. You are grouping by a field in a table that isn't part of your query.

Here is the original query to be specific

SELECT DISTINCT FTE.[A], FTE., FTE.[C], FTE.[D], FTE.[E], FTE.[ProJECT CODE], FTE.[F], FTE.[G], Sum(FTE.APRIL) AS [Sum Of APRIL], Sum(FTE.MAY) AS [Sum Of MAY], Sum(FTE.JUNE) AS [Sum Of JUNE], Sum(FTE.Total) AS [Sum Of Total]
FROM FTE LEFT JOIN [PDR Project & Account] ON FTE.[PROJECT CODE] = [PDR Project & Account].[Proj Code]
GROUP BY FTE.[A], FTE., FTE.[C], FTE.[D], FTE.[E], FTE.[ProJECT CODE], FTE.[F], FTE.[G]
HAVING (((FTE.[PROJECT TYPE])='FIXED PRICE'));

Results of Sum(FTE.APRIL),Sum(FTE.MAY),Sum(FTE.JUNE) changing due to table [PDR Project & Account]
 

plog

Banishment Pending
Local time
Today, 14:36
Joined
May 11, 2011
Messages
11,611
That doesn't help me understand what you are trying to achieve at all. In fact, it makes me believe you need to take 2 steps back and properly structure your tables.

When I see the field names of FTE I see that you are storing values in your field names (April, May, June, etc.). You shouldn't do that. Instead, you should have a field in that table where you store what month the entire record is for. For example, this is wrong:

Sales
SalesMan, Jan, Feb, March, ...
Tim, 39, 43, 55, ...
Steve, 13, 22, 18, ...

Instead of those 2 rows, you should instead have 6 rows of data like this:
Sales
SalesMan, Month, TotalSales
Tim, Jan, 39
Tim, Feb, 43
Tim, March, 55
Steve, Jan, 13
...

You need to properly configure your tables before you move on to solving this issue. The process of properly structuring your tables is called normalization (https://en.wikipedia.org/wiki/Database_normalization), read up on it and apply it to your database.
 

jdraw

Super Moderator
Staff member
Local time
Today, 15:36
Joined
Jan 23, 2006
Messages
15,364
harshadborde,

You are showing us HOW you did something in Access. We need to know WHAT you are trying to achieve before we can offer comments on HOW it might be done.
As plog has said, you have some data structure issues.

Good luck
 

Users who are viewing this thread

Top Bottom