Links..?

[asmodius]

O.k. So the following problem has probably already been solved on these forums a million times, so if you know what I should be searching for I have no qualms doing the work myself. So here goes:

I have a form for customer data, and a sub-form for complaint data that customer gives. Each customer must be able to submit more than one complaint. But I want to have a link between customers and their complaints. The customers are autonumbered an ID. The complaints also receive an auto number for the complaint ID. I want each customer ID to be linked to all of their complaints. How can I do this? Perhaps I could have a drop down box on the customers for that lets you select their complaints as well? Where would I begin to search for this answer?

Thanks in advance.

Andy

 

[Rich]

You need two tables with a one to many relationship between them. You can then use a Form/SubForm linked by CustomerID which is FK in your complaints table. Search here for normalise

 

[asmodius]

one to many relationship....

So as it turns out I can't create a one to many relationship between the autonumber customer ID and the autonumber complaint number. I can only guess that has something to do with the fact they are both auto number. However I had a stroke of genious to just ad a customer ID field to the subform and second table that could have the one to many link. So now there can be a direct link between one customer and many complaint id's because the customer ID is repeatable in the customer service form and table, now I want to know the following.

Is there a way to have that customer ID field automatically pull the customer ID the parent form. In other words when joe the customer service guy puts a name into the customer data fields that report to the customer data table he gets an auto number for that customer that auto number is then automatically copied to the customer id field in the customer service subform which reports to the customer service table, every time the customer id number is changed up top a new customer service complaint is initiated and associated with the customer id in the parent form. Whew I hope this makes sense.

If anyone is willing I would be more than glad to send this stuff to them to take a look at. Its too big to post already though. I've tried.

thanks.

 

[asmodius]

Sorry to be a dork but where would I find that?

Take a look at the northwind.mdb that shipped with Access. It shows how a one-to-many form/subform works.

On the media, in help, or where?

Thanks.

 

[asmodius]

Almost got it....

O.k. so I figured out my own way to connect these forms but it doesn't seem to be going as well as I planned. Heres what I did:

The customerinfo form up top contains a customer number. In the customer service (complaints) subform I created a box for that customer number. That way one customer ID can be on as many different complaints as I want. However when trying to make a relationship with these fields in a query I get a type mismatch error. And also is there any way so that in the customer info form if instead of putting in a new number I bring up an old one I could have a cascading combo box that would list all of the existing complaints that include that customer number, and then if I were to select a complaint from that list the subform would change to the one I selected?

Please, I am reading these forums like crazy trying to catch up to where I should be on this stuff but it gets a little difficult when you don't know what you're looking for. Thank you.

By the way I've checked the normalization of my tables, the only stuff that repeats in the tables are the linking fields, so I think I've got a good set of tables here. There is only two output tables. The ones described above. all the other tables are just lists for the drop down boxes. T

Thanks.

 

[asmodius]

I think I found a thread that will help..

I found this and am going to try working it in. It seems to be exactly what I'm looking for.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=56642&highlight=foreign+key