Relationships

[Artavius]

Hello,

Here is another silly question for those out there. We have 8 tables that we are working with and have column 1 in those tables set for autonumber so it will give our forms an ID each time we create a new one. What I am trying to do is setup a relationship so as when we go to create a form in one table it wont duplicate the number from another. In essence each form we create will always be a new number and never be duplicated in any of our other tables. I've been trying to do the relationship establishing but so far it does some tables but others its duplicating. I have checked to make sure these tables are set to not duplicate and they are but im still lost as to what do. Any help would be greatly appreciated.

 

[KenHigg]

Sounds like you have normalization issues...

Care to post the basic table structure(s)?

 

[Artavius]

Sure - not sure how to do that though - if you or something can tell me how to do it i'll get them on here right away. I am sure that my whole database is probably not the best for this is my first attempt at it but im trying.

 

[KenHigg]

Just do something like:

myTableNameOne
myfieldNameA PK
myfieldNameB
myfieldNameC
...

myTableNameTwo
myfieldNameX PK
myfieldNameY FK
myfieldNameZ
...

Where PK and FK are primary and foreign key fields...

Make sense?

 

[Artavius]

That I can do.

I have 12 Tables which have alot of fields in them. Not sure if you need all the fields are not. The main field im trying to work with is Column A which is the ID Field. All the tables have pretty much the same column information.

Table 1 (Customer Error)
Column A (ID) which is AutoNumbered
Column B (Date)
Column C (Customer Name)

Table 2 (Theft)
Column A (ID) same as above PK

Table 3 (Vendor Error)
Column A (ID) same as above


There is only one table currently that has a primary key which is theft. None of my tables have a foreign key that I am aware of. In the Relationship Building area I have tried to make Theft go to all of my tables. I even tried to setup all of my Column A's as primary keys but did not work as well. Does this help any? Let me know if you could use more information.

 

[KenHigg]

Hum... Tell me in the real world, exactly what each table is supposed to be tracking...

 

[Artavius]

Basically we are a wood shop and we are wanting to track any events that may cause us extra work. IE we have a form if a customer of ours lost their product at a jobsite, we another if the item was stolen from their jobsite, we have a form if the extra order was caused either by them or by one of our sales team, and then we have a form if its caused by data error, shop error or a delivery error. The purpose is to gather enough data to find out where our holes are at and find out ways to improve our customer service and accuracy. THe main reason we would like to see the ID # not duplicate in the different forms is mostly for filing purposes. Currently we have them do all the paperwork outside of access and then we key in the information. With the ID we are hoping that we can put that # at the top of the original form so when we file we can go by the ID # list and then if we need to find it all we need to do is search access to find the form, get the ID # and then we know where to search. Does this make any sense?

 

[ScottGem]

I agree with Pat's suggestion. To add something however, a primary key needs to be unique within a single table. There is no reason to keep a PK unique across different tables. Using an autonumber provides that uniqueness within each table.

 

[KenHigg]

>sigh< Why do I bother...

 

[KenHigg]

I never suggested anything...

I was trying to get a clear understanding of what the database was doing...

 

[Artavius]

Thanks for the advice. I think I know what your saying. I'll try to create an event table / make a column that is for the auto number and then relate my other tables to it and see if that does the trick. I will then set the other tables to not auto # and try what you suggest below and see if things turn out to be beautiful. I'll let you know my results.

Thanks again

 

[KenHigg]

Never mind...

 

[Artavius]

Okay - tried to do it but it didnt see to take. I am not to sure on what you mean by foreign keys. That may where I am falling short on.

I have created a table called:

Event Data
Field Name: Event ID
Data Type: AutoNumber
General
Field Size: Long Integer
New Values: Increment
Indexed: Yes (No Duplicates)

Then on my exisiting table I have done this:

Customer Error
Field Name: ID
Data Type: Number
General
Field Size: Long Integer
Decimal Places: Auto
Required: No
Indexed: Yes (No Duplicates)
**All the other fields in the General Tab are blank**

I didnt use the key button the the Event Data Table - maybe that is causing the issue? I did go into the relationship area and dragged the ID field from the Event Data Table to my other tables and there is a line going from all the other tables to the Event Data Table. Guess im lost now as to what I am missing.

Thanks

 

[ScottGem]

I think you misunderstood what Pat was saying. The Event table is a combination of all your other tables. You should not have separate tables for customer Error, Vendor Error etc. You should have ONE table with field defining the type.

The only reason to have separate tables for the types is if you have several fields that are unique to each type and you don't want to have blank fields in your table. In that case you set up a one to one relation between ther Events table (which has the fields common to all events) and the individual tables having the unique fields.

A foreign key is just a field that holds the value of the primary key of the related record.

 

[Artavius]

Okay - I think im getting it then - basically - combine all my tables into 1 table and then go from there. That makes sense - I may try that then and create a combo box to display what type of error it is. Then my next quest is to figure out how to create a search / query page.

 

[ScottGem]

Okay - I think im getting it then - basically - combine all my tables into 1 table and then go from there. That makes sense - I may try that then and create a combo box to display what type of error it is. Then my next quest is to figure out how to create a search / query page.

Exactly! You might want to create a lookup table fot the error types to use as the source for your combo. This will make it easier to add error types later.

Creating a search page depends on what you want to search on. I like to give my users instructions on using the Filter by Form feature. This allows them to enter criteria in one or more controls on the form that can be used to create a filter.

 

[Artavius]

Thanks everyone - I removed all the tables that I had created and formed it one table and now things are working alot more smoothly. Now its off to create the seach function so I can allow others to be able query the different error types.