Combine data from two tables

[risenshine85]

Hi
Need help on an assignment here
I have two tables:

Table: Department/Manufacturer Sales Info
- dept no, primary key
- dept name
- manufacturer no.
- manufacturer name.
- sales

Table: Department/Store Info
- dept no, primary key
- dept name
- store no.
- store location

How do I design a query where I can have one table to combine all this info
store no/name, dept no/name, and manufacturer into one table

 

[JamesMcS]

Welcome to the forum!

What is the relationship between these two tables? Specifically manufacturers and stores - can one store have many manufacturers?

You might want to consider changing the structure of this DB a bit. The problem I can see straight away is that there's no relationship between stores and manufacturers at the moment, except for department, which is going to be a bit of a vague way of grouping the results together....

 

[risenshine85]

James
there is no direct relationship between manufacturer and store. That is what I m trying to achieve. One table gives me dept and manufacturer info. Other gives me dept and store info.

I need to combine them such that:

Store, dept, and manufacturer fields
What I did so far is made dept no. the primary key in both tables. This is a unique number. Created a one to one relationship between the tables using dept no. And running a cross tab.

Any ideas?

 

[JamesMcS]

The problem that you've got is that you need to have the definition between stores and manufacturers before you can combine them, otherwise you're going to end up with almost a cartesian result from your query (if you just join the two tables on department).

What you need to do is consider the relationship between the fields and design your database around that. Also you need to think about store, manufacturer, department and so on as different subjects, with one table for each subject, and a table combining them all. So:

Table Stores:
Store_ID
Store_Name
Store_Location

Table Manufacturers:
Manufacturer_ID
Manufacturer_Name

Table Departments:
Dept_ID
Dept_Name

Table Sales:
Dept_ID
Store_ID
Manufacturer_ID
Sales_Period (day, month etc.)
Sale_Value

Create your relationships between the tables (so one department to many stores, one store to many manufacturers etc.) and you'll have a lot more flexibility in terms of showing sales for department, store and manufacturer. The way you've got it at the moment, I can't see a way to do what you need - the department link between the two tables is too ambiguous.

 

[risenshine85]

James,

Thanks for the prompt response.
See I didn't design the the two tables. This is how the data was given to me.
The dept./store already has all the departments for all the stores listed.
Store to dept is one to many relationship.

The next table is dept to manufacturer.
This one again is one to many and is already laid out.

If I separate the data fields isnt that one step backwards? Maybe I am not following

 

[risenshine85]

Essentially I am trying to create the table sales as you mentioned above. If I separate the data. How would I connect dept to stores? That info is already in a corresponding table?

 

[JamesMcS]

So you can't change the structure of the DB? I may be being blind myself but I can't see how you'd get the result you want. Imagine that you've got the following in your tables:

Dept/Manufacturer
Dept 1, Mfr 1, £50
Dept 1, Mfr 2, £50

Dept/Store
Dept 1, Store 1
Dept 1, Store 2

When you combine them as you want, there's no way for Access to know how to combine Dept, Store and Manufacturer as they're all Dept 1. You'll get a result like this:

Dept 1, Store 1, Mfr 1, £50
Dept 1, Store 1, Mfr 2, £50
Dept 1, Store 2, Mfr 1, £50
Dept 1, Store 2, Mfr 2, £50

As there's no association between store and manufacturer, Access will give you every possible result - this is called a Cartesian Result. If you had ten stores and ten manufacturers with the same department, you'd get 100 results. If you had a hundred stores and a hundred manufacturers, you'd get 10,000.

Let me check with one of the senior guys to see if I'm barking up the wrong tree here.

 

[JamesMcS]

Re: stores, it would depend on the relationship. If one department can have many stores, put a dept_ID in the stores table.

 

[risenshine85]

Ah I see.
So how would I connect this
See what I see it is like a tree
Store (store Id and location) has departments (dept no and description) and then each dept has manufacturers.
But at the moment I data in two tables: dept and store, and dept and manufacturer
The way I thought about is that if I have the correct relationship between dept and store I use the dept no. As key to lookup the manufacturer in the other table.
So in simple words how can I add the manufacturer info to table 1?

 

[JamesMcS]

Ah sounds like I got it the wrong way round a bit, I thought department was the top level identifier. But yes, you're right, it is like a tree. In this case store would be the root. So structure your tables and relationships accordingly:

Stores:
StoreID (PK)
Store Name

Departments:
DeptID (PK)
StoreID
Dept Name

Manufacturers:
MfrID (PK)
DeptID
MfrName

Sales
SaleID (PK)
MfrID
Sale Period
Sale Value

If you want to group sales by, say, Store and not just Manufacturer, add in the manufacturer ID to the sales table. That way you've always got something to join the tables by.

 

[risenshine85]

Okay. Maybe I am falling a bit better now
So technically this is how I see it:

To get to a table with store, dept, and manufacturer
This is what I need to do
Stores:
StoreID (PK)
Store Name

Departments:
DeptID (PK)
StoreID
Dept Name

Manufacturers:
MfrID (PK)
DeptID
MfrName

Run a crosstab between store and department,.
And then run a crosstab between that result and manufacturers table?

 

[JamesMcS]

Rather than a crosstab I would have left joins from the Store table to the department table, then another from there to the manufacturer table (i.e. join arrow goes from store to department, then from department to manufacturer), and do a standard select query.

 

[JamesMcS]

This is the sort of thing I'm talking about.; Have a look at the relationships and have a play with the queries, see how flexible it is this way

 

[risenshine85]

james that helps... but here is an isssue
dept # are not unique ...hence i cannot have them as primary key!
can i assign primary key to a combination of two things?

 

[HiTechCoach]

james that helps... but here is an isssue
dept # are not unique ...hence i cannot have them as primary key!
can i assign primary key to a combination of two things?

The actual dept code should not be the primary key.

The primary key should be system assigned, like a auto number. This way it is always unique.

What is unique is the combination of Store and department code.

You will need to create a compound index on store ID and then department code.

Table: Store Departments
- dept ID, auto number primary key - used in relationships
- store ID - * - foreign key to the store table.
- dept code - * -
- dept name
- dept location within the store

* A compound no dups index will be created on these fields:
- store ID
- dept code

 

[JamesMcS]

The department number should be unique in the departments table I think. You don't want to have duplicates in any of those tables otherwise you'll find extra rows in your queries that you don't want...

 

[HiTechCoach]

The department number should be unique in the departments table I think. You don't want to have duplicates in any of those tables otherwise you'll find extra rows in your queries that you don't want...

JamesMcS,

The department numbers are only unique by store. This mean that two stores can have the same department number. The same depart number could possible be for different department in each store.

 

[JamesMcS]

What I was getting at was that there shouldn't be duplicates in the department table, referring to the table in the DB I posted the other day - a store can have many departments, and the same department more than once (why you'd have that I'm not sure) but if there's two departments with the same number in the main departments table it'll all go crazy when trying to run queries grouping by department, won't it?