Help with Query for Remedial Access User

[cjc155]

Hi there,

I apologize in advance as I am fairly new to access. I am in the process of reading a few books but help from the forum on this issue would help speed things along.

I have 3 tables

1) Mapping table that links ID's to Brand Name
2) Table for Product A sales by brand by month
ID
Brand
Sales
Month
3) Table for Product B sales by brand and by month
ID
Brand
Sales #
Month

What I am trying to do is sum the sales by Brand each month. Problem is some brands have only Product A and not B (vice versa).

Currently, my query only displays values where Both A & B have sales in a simliar month. I tried using the Nz to preface the query but it is not the query that is the issue as when I simply add Table B to the design view my results of the query are limited by the values in table B.

I hope I am explaining this well enough. Thanks for your help

 

[giacomo]

why are ProductA & ProductB sales in different tables?

 

[cjc155]

Why in different tables

Hi,

I used Product A & B sales as an example. Your question is a good one as I know putting them into same table will solve the problem (this is what I am trying to solve with the query).

Real issue is way I get the data. It was easier (atleast for me) to create a macro to load the data into separate tables.

If it is much easier to put them back into one table, I can do that, but wanted to see if there was an easy way to do it with a query.

 

[Andrew Fergus]

Hi

I agree the sales for all products should be in the same table. You may need a product field in your table to identify each product. How to get all of the data into one table? Use an 'append' query to transfer the data from one table to another.

HTH, Andrew

P.S. Have a read of the linked webpages in giacomo's signature - especially the links regarding normalisation. That will help with future database design.

 

[cjc155]

Thanks

Thanks for the help. I will put them in the same table. I appreciate the links too and I will check those out

 

[cjc155]

Stuck trying to create normalization with the tables

Hi again,

I have reviewed the links and I have gotten too a point but getting stuck on how best to combine the data I have. I don't think I am explaining my situation correctly so let me tell you the problem I am trying to solve then give you my current table setup.

To start, I have 10 organizations that have 1 or 2 clubs. The clubs do not have same start/end date (one club started in 1995, another in 1999) and there may be new entrants of organizations. I do not individual level membership, only summar information saying in October of 2006 ACME organizations had 100 members in Club A, EMCA organization had 200 members in Club A and it had another club, Club B, which has 35 members. I am trying to create a setup to keep club membership separate, but then also have the ability to sum membership by Organziation (i.e. how many total members does EMCA have in october)

From your advice, I am trying to combine membership into 1 table...so this is what I have done so far...

tbl_Organizations - This is a list of 10 organizations with col 1 being ID, and col 2 being name of organization

tbl_Club - col 1 being ID and col 2 being name of club (currently there are 2 clubs)

tbl _Membership- This is where I am stuck. I am not sure how to create this table so I can have the membership data be identified by Organization and by Club. Currently, I have something like this

ID, Date, Organzation, Club, Membership
1, 10/2006, ACME, 1, 100
2, 10/2006, EMCA, 1, 200
3, 10/2006, EMCA, 2, 35
4, 11/2006, ACME, 1, 110
5, 11/2006, EMCA, 1, 220
6, 11/2006, EMCA, 2, 40

But I think this is incorrect and there should be a more correct way to do this.

Thanks so much for your help. This novice greatly appreciates it

 

[Andrew Fergus]

Hi

Given each club is part of an organisation and an organisation can have more than one club (I hope I understood that correctly), then add an Organisation_ID field to your tbl_Club table. Then in your relationship screen (under Tools > Relationships) click and drag the organisation_ID from tbl_Organisations onto the same field in tbl_Clubs and set up a relationship.

In your tbl_Membership table remove the organisation field. If you link the club_id from tbl_Clubs to the club_Id in tbl_Membership (via Tools > Relationships) then your set-up should be ok.

HTH, Andrew

Late edit : can you clarify if 1 organisation can have many clubs, or if 1 club can have organisations? I thought it was the former, but after re-reading your post I think it might be the other way around - I'm not sure.