Table design and relations logic problem...

[Nalgene]

Hi everyone,

I'm new to Access; I'm trying to learn it by myself at the moment and trying to apply some of the concepts from the example database that comes with access (Northwind).

So I've decided to try to develop a software for my small business. I have tables for products, customers, invoices, etc... its all working really well except for one thing.

Right now I have a form for invoices with a sub form and I pick my customers from a dropdown menu that is populated using a query. What I would like to add next is another dropdown menu that would let me pick the customer’s shipping address. In my case each client has more than 1 shipping address and I figured it’s a waste of time to add an entry in the clients table for each separate shipping address.

So I've decided to make a separate table with the shipping information. I've created a relation between the tables: customers and shippingAddress using the customerID.

My problem now is that it won’t let me create a query to do my invoice. It’s kind of hard to explain but here's what my database look like right now:

Tables :
customers (contain a customerID set as AutoNumber)
shippingAddress (Relation 1-to-many with customers.customerID)
invoices (relation 1 to many with customers.customerID)
invoiceDetails
products

Both invoice forms are created from queries...now since I've added the table shippingAddress I can’t make a new query that would bring the information to the form invoice. It let me create it but it’s always empty.

What I want is a form where I pick the customers (that I can do) and then a dropdown menu is populated with that customers shipping addresses.

Thanks in advance for the replies, don't hesitate to ask if you need more details. Pointers in the good direction would be nice, this is proving to be quite frustrating at the moment

--
Sorry this post is so long, I just wanted to include as much details as possible so that you could understand my logic which is obviously not good

 

[Nalgene]

Ok, had some time to draw a quick representation in Paint its not pretty but shows what I have so far, all relations are 1-to-many :

http://img123.imageshack.us/my.php?image=untitled1tw.jpg

 

[Lilliabeth]

Have you actually entered data into the shipping address table? Could it be just that you need to change the join type to an outer join?

 

[Nalgene]

I have at the moment 1 entry in the shipping address table for testing purpose. I've tried with different join type like you said but I'm not really sure how those work so maybe I did it wrong.

Right now as soon as I do a Query that include shipping address Access let me save it but its not being displayed (if I run it all the columns headers are displayed but no actual field. If I try to create an auto-form it generate an empty form.

I'm trying to do it using two different queries right now. Not sure how that will go since I've never used anything but the auto-form wizard before. Its really strange (for me) as my logic seems correct but the problems seems to be coming from the customers.cID being in relations with two different tables (shippingAddress.cID and invoice.cID)

 

[Nalgene]

Alright,

I've tried to dumb the query down to something very simple and still no go. I did it using SQL this time. I have this :

SELECT invoice.*, customers.cName, customers.cFirstName, customers.cTel, customers.cEmail, customers.cStreet, customers.cCity, customers.cState, customers.cZIP, shippingAddress.saCId, shippingAddress.saFirstName
FROM invoice, customers, shippingAddress;

I havent included every fields for shipping address since I just wanted to test it. cId is PK for customers. Its present as a number in invoice and shippingAddress. I have a 1 to many relation between customers.cId--invoice.cId and customers.cId--shippingAddress.cId

When I use the wizard I get INNER JOIN in the query, which is what I ultimately will need but I've tried to simplify it to help me debug and I'm still getting the same result, a query with no fields, not blank field like 0 records I just see the headers as if Access is telling me it will never be able to find records matching those criterias...

I don't know what's wrong it's driving me crazy.

 

[Nalgene]

Well I think I've found another way to get it to do what I want. Instead of doing invoice by customers.cID I do it using shippingAddress.saID...

I then pick the shipping address in a drop down menu that is populated from a query consisting of saID, cID and the name of the customer. I hide the first 2 and its working.

It seems to be a quick fix though so if anyone know why my previous attempts didn't work I would like to hear from you and hopefully be able to do it the "right" way.

 

[Lilliabeth]

When I use the wizard I get INNER JOIN in the query, which is what I ultimately will need[\quote]


An inner join will only display records that have matching values in the join field. But you want to display ALL customer records, including the ones that don't have a matching CustomerID in the Invoice table and the ones that don't have a matching CustomerID in the ShippingAddress table.

Take a look at outer joins.

 

[lespaul]

Here's a link that will help with the joins Understanding SQL Joins.

I would expect that if you're creating invoices you must have a Billing Address? Or do you bill to the shipping address?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Nalgene]

Hi lespaul,

Yeah if I do an invoice I expect a shipping address. In the end that is the solution I implemented. I pick a shipping address when I start a new invoice which is working quite well so far. I populated a dropdown box with the records from a query which display the client and the matching shipping address.

Thanks to everyone for the help and more importantly thanks for the link, I'll definitly make sure to read it and modify my app. according to the new stuff I learn.

 

[barrymelson]

Dear Nalgene:
I am having the identical problem, trying to use multiple ship address for any one customer. I would be interested in finding out more about how you have resolved this problem. I am not sure about the rules here on how to commuincate out of this forum. If you have any ideas on how we may exchange applications I would be most appreciative.

Thanks,
Barry M. Elson