Database advice needed please.

[RobertSmith101]

Hello friends,

I am in need of advice with my database

I have a customer table that has the primary key as CustomerID attribute and all other attributes such as customer name, Address etc.

And another cargo table with a primary key of CargoID and other attributes however I wish to generate a report so that when it shows up it shows which customer has bought which cargo according to to the cargo ID and the customer ID the customer has bought for example I wish the report looks like this (bracet stuff is attributes not needed in the report)

REPORT
(customerID)778963 (cargoID)789456
(name) Joe bloggs (weight) 12.4KG
(address1) 17 danes avenue (description) spare parts
(city) paris
(country) france

What I need to know is should how do I achieve this (I know how to make a report) I have the customer ID in the cargo Id should it be a foreign key or am I completely off the point please give me some advice its imperative that I figure this out. Thanks Robert.

 

[LarryJ]

Your solution is going to depend on the relationship between the two tables. Is it a one-to-one, one-to-many, or many-to-many?

In other words if one customer can have one cargo and that cargo is owned by only one customer, then you have one type of solution.

If one customer can have multiple cargo while the cargo belongs to one customer, that is another solution.

If you have the ugliest (many to many) then one customer has many cargo, while a single cargo can have many customers. That requiers an intirely different solution.

In the first or second case, one solution would be to add the CustomerID to the Cargo table, and make it part of the key. This will create a one to many relationship, which is probably what you need. This way, you can find all cargo for any customer and report it that way.

Now, if you have a many-to-many, that's going to require a new table be created, so I won't go into that, unless you have this situation.

 

[RobertSmith101]

one customer can have multiple cargo while the cargo belongs to one customer,

Thats what I've got do you know how to help me with this? I would appreciate some help I'm having terrible problems with working this out,

Thanks,
Robert.

 

[LarryJ]

That's what I thought it would be.

So, adding the CustomerID into the Cargo table and making it part of the key (along with CargoID) would give you a master/detail, or parent/child, or one-to-many relationship (all different names for the same type of relationship).

My best suggestion would be to look at one of the databases Access can create for you by using the Access wizard. The "Order Entry" database has a very similar situation.

This creates a Customers table and an Orders table, with the same type of situation. You can see in the design of the Orders table that the CustomerID is there (but not part of the key). Making it part of the key is based on your need, so it can go either way.

Hope this gives you some ideas.

 

[Aladin Akyurek]

Larry,

It's really similar to

CUSTOMERS(CustomerID,CName,CAddress,CCity,CPhone)
ORDERS(OrderID,CustomerID,ODate,OTotal)

with as relationship...

A customer has one or more orders; For every order there is customer to whom the order belongs.

So we get...

CUSTOMERS(CustomerID,CName,CAddress,CCity,CPhone)
CARGO(CargoID,CustomerID,CDate,City,Country,CWeight)

Thus CustomerID is not part of the key in CARGO, but the 1:N relationship must be established via CustomerID.

The OP could create a query (using visual query language or SQL) and build the report on that query.