I am preparing the database for keeping the track of task interface.

My question is regarding my main table [tblMaster] - it has below listed fields:

1. RecordID / Type: AutoNumber
2. ProjectID / Type: Text [Lookup from (tblProject)]
3. InterfaceCode / Type: Text [Lookup from (tblInterfaceTitles)]
4. Activity / Type: Text [Manual data entry]
5. Party-1_Code / Type: Text [Lookup from (tblParty)]
6. Party-2_Code / Type: Text [Lookup from (tblParty)]
7. Party-3_Code / Type: Text [Lookup from (tblParty)]
8. Comments / Type: Memo

Note that the [tblMaster] is appropriately related to [tblProject] & [tblInterfaceTitles]. I have done the lookup in Field Properties area of table design mode for all lookup data.

So far so good.

Now, I am trying to make a query based on [tblMaster] to get related data from lookup tables. For example I want my query to pull the Project Name when ProjectID is selected, and this applies to all the lookup fields. I get want I want until the InterfaceCode.

But the problem is with the PartyCodes. This is the un-related table and I am not able to get the corresponding data on my query (e.g. PartyName). Note that I am trying to pull the data for Party-1_Code, Party-2_Code, and Party-3_Code from the single table (i.e. [tblParty]). I would appreciate if someone can help me do this correctly.

Thank you in advance. Your help is much appreciated.

Regards,
Iqbal

Is your Part-1_Code, Part-2_Code, Part-3_Code lookup a multivalue?

There are two problems here.

First, you have used Lookup fields in the tables. Lookup fields are a bad design choice in all cases except one. If this is going to be a web-based database that you will publish to SharePoint, you have no other choice for defining relationships. Otherwise, they are a trap for the unwary and should be avoided.

Replace the lookups with properly defined relationships using the relationship window.

Second, you have a repeating field: Party-1_Code ,Party-2_Code ,Party-3_Code .

This is also an inappropriate table design. These fields belong in a separate table. Call it something like "tblMasterParty". Create it with its own Primary Key, a Foreign Key field to this table and a Foreign Key to the tblParty table. Do not use lookup fields for these foreign keys.

Now, in your query, add the "tblMasterPary" table and the "tblParty" table.

No it's not multivalue

Hi! Grover,

Thanks for your feedback.

I think i didn't explain well. These are not the lookup fields. I have done the lookup in the properties area of table design.

The attached relationship snippet should help understanding my problem better...

 InterfaceDB_RelationshipSnippet.JPG ( 40.13K ) Number of downloads: 10


Regards,
Iqbal

This post has been edited by access_iqbal: Apr 10 2012, 11:41 PM

"I have done the lookup in the properties area of table design"

Yes, that is what I mean by lookup fields in tables. Does the "lookup" property of your table look like this?
 4_10_2012_9_35_20_PM.png ( 31.58K ) Number of downloads: 7


If this is what you did, then, that is NOT a good choice for tables. If that is not what you meant, of course, we might want to get more details.


While it seems like a good idea, it will usually end up cauing more problems than it's worth.

The bigger problem, though, is the use of fields in a repeating group: Party-1_Code , Party-2_Code , Party-3_Code

This design is okay for an Excel spreadsheet, but not for a Relational table in a database.

Here are some good readings on table design. Pay special attention to Roger Carlson’s Blog on Normalization. It describes your table design problem, and how to correct it.

Once you've done that, the query you want will be quite easy.

• Database planning tutorial Part I
• Database planning tutorial Part II
• argeedblu's FAQ on Design Strategies
• Noah's FAQ on Normalization
• Jerry's FAQ on Normalization
• GroverParkGeorge's PDF on Normalization
• Jerry’s FAQ on Fully Qualified Naming
• Jerry's FAQ on Glossary
• Danny's FAQ on Reserved Words
• Autonumbers - What They Are / Are Not
• Primary Keys - Natural vs Surrogate
• Naming Conventions - Hungarian Notation
• Crystal's ACCESS Basics for Programming
• MSKB 283878: Description of the database normalization basics
  
• Presentation and sample db
• Crystal's ACCESS Basics for Programming
  
• Roger Carlson’s Blog on Normalization

Thanks again...

But this is how it looks:-

 InterfaceDB_RelationshipSnippet2.JPG ( 124.59K ) Number of downloads: 8

Ah, perhaps we are just using different terms then. You have shown a screenshot of the relationship window with relattionships defined between various tables. That's the appropriate approach.
Sorry for the misunderstanding.

That said, your repeating group is inappropriate and needs to be broken out into its own table. THen your query will be relatively simple to do.

I didn't quiet get you George...

Please will you be able to help me on this... (i.e. the design change)

Regards,
Iqbal

you will need to add the party table into the query 7 times and link each Party-x_Code to a seperate party table

see below short example



HTH

Hi

What George is saying is that this part of your table

5. Party-1_Code / Type: Text [Lookup from (tblParty)]
6. Party-2_Code / Type: Text [Lookup from (tblParty)]
7. Party-3_Code / Type: Text [Lookup from (tblParty)]

Should be removed from your Master Table

You then need to create a NEW Table called:-

tblMasterCodes
-MasterCodeID - PK - Auto
-MasterID - Number - FK (Linked to tblMaster on MasterID PK)
-PartyCodeID - Number - FK (Linked to a NEW Table which is the List of Codes)

tbluCodes
-CodeID - PK - Auto
-Code

I'll change the DB design.

Thank you everyone for your help.

Regards.