MS access queries running issue

[Sk123]

Hello Everyone,

I have 3 tables......table 1 is different organisation names and that is also primary key.........table 2 is 2015 Sales...........table 3 is 2014 sales

Now when i link table 2 and 3 to table 1 and i use sum option for Sales and group by for organisation names, my query just doesnt run. I mean i see query loading bar starts at the bottom right but as soon as it completes, nothing happens

Now if i remove table 3 or just use one table without linking anything, it works perfectly and i can see my numbers

Any idea why?

 

[Joe4]

I am guessing you have a join issue. If you are doing an inner join between all your tables, the only records that will be returned are ones that appear in ALL three tables. Try doing a Left Join from Table 1 to your other two tables (the one that says "include all records from Table 1 but only records form Table2 (3) that match".

Another tip when doing Aggregate Queries. Set up the queries as simple select queries first, and make sure that you are returning data before hitting the Totals button to make it an Aggregate Query.

 

[Micron]

Seems to me that a sales table based on the year is the wrong approach right from the get-go. It doesn't sound normalized at all.
Aside from that, your post doesn't give any clues as to what fields you are linking. You only say that you have a PK that is text, which likely means your organization names are repeated in each of the sales table as well - another normalization no-no.

 

[Sk123]

Seems to me that a sales table based on the year is the wrong approach right from the get-go. It doesn't sound normalized at all.
Aside from that, your post doesn't give any clues as to what fields you are linking. You only say that you have a PK that is text, which likely means your organization names are repeated in each of the sales table as well - another normalization no-no.

Yes i just started working on access and don't have much knowledge. I dont understand when you say normalization

I will try to give more details

Table 1 contains only organisation names and nothing else and this is my PK also
Table 2 contains organisation names plus 2015 sales numbers (also other 10 fields that i don't consider in my query). Now organisation names can repeat multiple times with different sales numbers that's why i use sum in my query design
Table 3 is exactly the same as table 2 except it has 2014 sales numbers

so when i design the query.....my first field is table 1 org names and i use group by function

and then i drag down table 2 and 3 sales number and use sum function

Now what doesn't make sense is when i use only 1 table and dont use link function it works perfectly

Also i never get any error. my query just keeps running forever

any idea why?

 

[Micron]

I dont understand when you say normalization

You need to research it; the topic is too deep to cover here, and it makes no sense to repeat what you can find using any search engine with 'ms access table normalization' as key words. You should discover that the organization names should not appear in both tables, nor should the sales be broken down into separate tables, one for each year (at least, not normally). What you have is a many to many to many relationship that you are trying to perform aggregate functions on. If it would work, you'd probably find it will take hours if there are enough records. If you are allowed, just for fun, leave it running overnight and see what you get. Do get some insight into normalization before trying to solve this further.

 

[james_lankford]

make a query
view it as sql, not design view

then use this sql (change the table names and field names to what you have in your database)

select 
  tbl.Organization_name, 
  sum(Amount) as Total_amount 
from 
(
select 
  tbl_2014.Organization_name, 
  tbl_2014.Amount 
from 
  tbl_2014


union all 


select 
  tbl_2015.Organization_name, 
  tbl_2015.Amount 
from 
  tbl_2015
) as tbl
group by 
  Organization_name