Can you link tables via "autonumber" or "lookup"

[Neup44]

I don't know if what I am trying to do is possible. I have 4 tables:

Table 1 - Contains customer information.

Table 2 - Contains Warranty information
Warranty ticket number (autonumber)
Customer ID (lookup in table 1)

Table 3 - Contains Warranty Repair for a cetain type of repair. For example workstation repairs. Included in this table is the warranty ticket number and the various types of repairs.

Table 4 - Same as table 3 but for a different type of repair. For example notebook repairs. Included in this table is the warranty ticket number and the various types of repairs.

Form 1 - Input customer data (table 1)

Form 2 - Inputs data into tables 2 & 3.

Form 3 - Inputs data into tables 2 & 4.

In order to input data on forms 2, 3 & 4, the tables must be linked. If I link the tables via warranty ticket number (autonumber)it doesn't work. If I change the warranty ticket number from autonumber to text (table 2)and manually input the warranty ticket number, the form works. I then left the warranty ticket number autonumber and link tables 2, 3 and 4 via the customer id but it did not work either.

I would appreciate any input. Thanks.

Neup44

 

[GymRatPA]

Yes you can lookup (i.e. SQL select) to determine the correct row, but your dependant table will need the autonumber. An example is CUST and ORDER, ORDER_LINE from northwinds. ORDER will need the autonumber of CUST to link to that data, and ORDER_LINE will need the autonumber of ORDER to link together. In some apps, due to size, the CUST autonumber might be in both dependant table of ORDER and ORDER_LINE.

However, AUTONUMBER should not be changed or accessed directly by users. It can be confusing, since they access data via business keys.

 

[Neup44]

Hi GymRatPA,

Thanks for your response. Please foregive me for not completely understanding your answer but I have limited (but growing) experience with Access.

I was trying to link tables 2, 3 & 4 either by warranty ticket number (which is an autonumber in table 2) or by customer ID (established in table 1). If I understand you correctly, I should be able to keep the warranty ticket number as an autonumber and still link my tables together. I'm just not sure how to accomplish this task.

Neup44

 

[TheAceMan1]

How are ya Neup44 . . . .

Ya need to give good thought to your table relationships. This could easily make or break the design later on. Just linking because it fits is ambiguous.

For instance: Can one Warranty Ticket have many Warranty Repairs and many other repairs as well?

Or is it, as we say one to one, one Ticket for one Warranty Repair or Other repair?

If you cite the relationships as you need them to be (not by linking tables, but in your mind) we can help you better.

Below is a little critique on relationships:

http://www.microsoft-accesssolutions.co.uk/identify_table_relationships.htm

See Ya! . . . . . .

 

[Neup44]

Good morning TheAceMan1,

Thanks for you insight. I will read the article and give some thought to the relationships. I'll let you know if I am successful this evening. Take care and have a great weekend.

Neup44

 

[Neup44]

Good Evening TheAceMan1,

The link was helpful and I spent most of the day thinking about the table relationships. This is what I came up with and did.

I will have two types of tables. The first set of tables will relate to the customers while the second set of tables will relate to warranty items. I need to link the second set of warranty items to a customer (I need to know what customers are complaining and what are they complaining about). This is why I used a look-up table in the warranty table to identify the customer.

The first set of tables will relate to the customers. One table will have the Customer ID Number, while several other tables will have various customer information (customer name, address, DOB, product purchased, etc). In each of the tables, I will have the Customer ID Number. I will link each of the tables via Customer ID Number. The Parent Table will be the table containing only the Customer ID Number while the various other tables will be the Child Table. The relationship here will be one to one. The reason instead of putting all the customer information into one table, I will be spreading the customer information into several tables. There will be only 1 set of data for each customer, thus having a one to one relationship. I understand this and it works. Thanks.

Now the hard part (at least for me). The second set of tables will relate to warranty items. One warranty ticket will be given for each complaint (I would like the system to automatically assign a number instead of inputting one manually). Instead of listing all the various types complaints on one table, I want to separate the complaints by major category. For example, the workstation table will have 5 different complaints with an Y/N answer. That is
"Workstation will not power up.... Y/N" or
"Workstation is processing too slow.... Y/N" or
"Workstation froze up...Y/N".

While another table may be the notebook table with another set of possible complaints with yes no answers. If a customer calls about several complaints about his/her workstation, only warranty ticket will be issued. However, if a customer calls and has a complaint about both the workstation and the notebook, two warranty tickets will be issued (one for the workstation and one for the notebook. Now I'm guess but it seems to me that the relationship for the Warranty items should be one to one. I might be wrong because each warranty ticket may have several complaints within one major category.

If I link each of the warranty tables via the warranty number (autonumber) I cannot input data into the tables via the forms. However, if I change the warranty number to a text and manually input a warranty number, I can link the table and input data via the forms.

Warranty Table (Parent Table)
Warranty Number (autonumber)
Customer ID (lookup)

Workstation Table (Child Table)
Warranty Number
Computer won't power up

Notebook Table (Child Table)
Warranty Number
Complaint

I not sure what to do next. Any suggestions?

Neup44

 

[TheAceMan1]

Neup44 . . . .

There's a little too much to go over here and give you specifics. Some seems right, alot seems wrong.

Can you send me a copy of what ya have? (see my profile)

If you can, all correspondence is to be carried on here at Tek-Tips . . . . .

See Ya! . . . . . .

 

[Neup44]

Happy 4th of July TheAceMan1,

I do appreciate your help and will be happy to send you a copy of the program that I am working on. I have several versions so give me a day or two to clean it up and I'll be happy to send you the final copy.

I'm not sure how to post my work since this will be my first time.

Neup44

 

[Neup44]

Hello TheAceMan1,

I'm having some trouble posting my work for you to review. If you don't mind, please give me some instructions on how to post my work. Thanks for all your help

Neup44

 

[TheAceMan1]

Did you see my profile?

See Ya! . . . . . .

 

[Neup44]

Good evening TheAceMan1,

I know this makes me look stupid but I could not find your profile. After spending several hours searching, I decided to email Tek-Tips for help but no return e-mail. I could use some help posting my program. Sorry for the trouble.

Neup44

 

[willir]

Neup44

Some light reading that may help add clarity...

http://www.abcdataworks.com/fundamentals.htm

http://r937.com/relational.html

http://www.15seconds.com/issue/020522.htm

Richard

 

[Neup44]

Richard,

I will read and post. Thanks

Neup44

 

[TheAceMan1]

Neup44 . . . .

Relax . . . . just click my name!. Send it as an attachment!

See Ya! . . . . . .

 

[Neup44]

TheAceMan1,

I'm taking you advise and relaxing.....the program was just sent. I do appreciate all your help. I'm looking forward to your comments.

Neup44

 

[Neup44]

TheAceMan1,

Just checking in to see if you received my program and if so, did you have a chance to look at it. Thanks again for you help. I hope you have a great weekend.

Neup44

 

[TheAceMan1]

Hay Neup44!

Did'nt forget ya. I'm sure you realize you gave me alot to look at! The problem is your a novice, and most of what I came up with requires higher knowledge. Trying to chop it all down so it will make sense to ya. Intend to post results tomorrow.

See Ya! . . . . . .

 

[Neup44]

Hi TheAceMan1,

Thanks for the update. I enjoy working in Access and would like to know what I did wrong. I'm hoping to take what I learn here and apply it to other programs I would like to write in Access.

Thanks again for all your help. Take care,

Neup44

 

[TheAceMan1]

Neup44 . . . . .

Currently making a final assessment. . . . . .

See Ya! . . . . . .

 

[TheAceMan1]

Ok Neup44 . . . . .

Main problem is, there's very little that can be changed without [blue]cascading changes[/blue] through a majority of the rest of the DB.

Although I see a number of changes I would make, there tentative, since I have no Idea what you want to really see in your forms.

There are eight relationships of which I can makes sense of only four (considering I'm not in construction). Not knowing how the others fit is very [blue]retarding[/blue].

With your current state of affairs, its gonna take skill far greater than what you have to even make a dent (not trying to be demeaning). If this were given to me, [/purple]redesign would be my only thought[purple].

Wether your gonna make improvements or redesign another DB or ask for help with this, here's my [blue]synopsis[/blue]:

You have nine discrete entities that are somehow related:

[purple]Costruction Supervisor
Lot Contractors
Lot Supervisor
Customers
Ticket Number
Lot Number
Service Tech
Contractors
Repairs[/purple]

You need to sit down and [purple]plot the acutal relationships between these items[/purple]. Figuring One-To-Many, One-To-One, and Many-To-Many relationships. With this as a foundation a new design would easily rise to the surface. [blue]This is also going to be the foundation of all your questions, so its that important[/blue]. Spend all your time getting this down if you do nothing else.

The following are references to material you need to know better and relates to all the above.

Table Normalization:

http://support.microsoft.com/default.aspx?scid=kb;en-us;283878

Table Relationships:

http://support.microsoft.com/default.aspx?scid=kb;en-us;304467

[purple]These are the items that can make or break any DB design[/purple]. As you get to know them well, you'll start to see everything in a different light. When you reach that point, let me be the first to say . . . . . [blue]Welcome To Access![/blue]

See Ya! . . . . . .