Relating ID field to Name field (1 Viewer)

[kmb]

Hi all,
I am sending this out to find out the best practice when it comes to fields design. I was under the impression that the best way to have the most upper level table was to have fields populating it with the primary keys from other tables.

Let me be more specific: I have an organization table that is joined to other tables like contacts, pix, category and subcategory. The problem arises when I have two fields for categories IDs and two for their respective subcategories IDs in the organization table, where they wouldn't be the same in an individual record. The values that are in the two categories fields should come from the categories table and the same for the subcategories.
When I go to query for a specific category, how do I use the category table to link the name associated with the ID for that specific record. Remember, there needs to be two of each and when I query, I use the OR criterion. If this is not clear enough let me know, and I can be more specific or send a sample dB.
Thanks,
kMb

 

[Mr. B]

kmb,

You will need to add your categories table multiple times in your query. Link one instance of the categories to each of the foreign key fields in your primary table. This will allow values from the Categories table to display a different value for each of the fields.

 

[Galaxiom]

I'm not sure I fullly understand the current structure but this sounds like there could be a case for another related table instead of the multiple category fields in the Organisation table.

The new table would have multiple records related to the main table by OrganisationID. Then the subcategories table would be linked once via the subcategoryID

 

[kmb]

I thank you for your replies, but I need to figure out how to implement the suggestion. From Mr. B's reply, I like the idea of adding two copies of the categories table, but a new question arises: If I store the values for category 1 and category 2 in the cat1ID and cat2ID fields of the organizations table, how, specifically, can I link the category name from the category table to the query and thereby to a report? should I link either from the relationships window and/or in the query design?

Thanks for any more help.
kMb

 

[Mr. B]

kmb,

As Galaxiom as indicated, first be sure that you have your data structured correctly. It does seem a little strange that Categories and Sub Categories would be the same, but, where you are completing records and recording organizational structrue it does not seem that far from what you are already doing. Someone else may have a "better mouse trap" for dealing with this type of situation, but it is much like having a field in a table where you record the father of a child in the "people" table. It becomes a self-referencing table.

In this case, you are mearly making selections from a list of optons, with each one specifying a different level of performance.

If you place a copy of your categories table in your query for each of the catId fields and place a link from each catID field to one of the copies of the categories table, the query will return the specific category name from the categories table for each catID field. This can seem confusing at first but give it a try and you will quicly see how it works. You do not need to define the links between these field in the relationship window, because you are only using the categories table as a lookup table.

 

[kmb]

Thanks...I am going to give this a try...for clarification, for each organization record, there are 2 categories and 2 subcategories. For example: The fairgrounds for the county would be listed as:
Cat1: Agriculture
SubCat1: Ag Support Org
Cat2: Cultural Resource
SubCat2: null
The above info would be stored in the ID fields in the orgs table and the name would be referenced from the categories table and the subcategories table for the query and/or report.

If this changes any suggestion thus far, let me know. Otherwise, I'll try your current suggestions and see how that goes.
Thanks,
kMb

 

[kmb]

I did figure it out, thanks very much. You were a great help.
Thanks again,
KmB

 

[Mr. B]

Glad to help.

By the way, let me clear up one thing. I would go ahead and create a reference to the categories table in the references window. To depict this correctly you will need to again add the table once for each link you need to the table.

Glad you got it working. Feels good when you make things happen like you want them to.

 

[kmb]

Ok...thanks