Lookup tables and Query

[rproactive]

Hello everyone-

Would greatly apprciate any ideas on the following delema.

I have normalize dmy database and as a result have about 17 lookup tables and about a dozen main tables. With some many small parts its hard to see whats happening.

Each lookup table has a primary key shich is an Autonumber together with a number of other fields one usually being a text name field.

My main tyables have a Autonumber primary key field amd other fields. Some of these fields "point at" the lookup tables primary key.

my confusion :
1. Should the Main lookup tables fields allways be related?? Some of my tables are but not all- is this OK?
2. In a query how do I show the text name rather than the numeric primary or foreign key.
I am having trouble with non updating of forms based on querys and querys not doing what i expect.

Any tips on how to deal with all the small pieces???

 

[Remou]

You shoukd not use look-up fields in tables. This may be one reason your queries are not working as expected:
The Evils of Lookup Fields in Tables

http://www.mvps.org/access/lookupfields.htm

To show, say, Description, from your look-up tables, add the look-up table to the Query Design Window. If you have created a relationship in the relationship window, which is a good idea, a join will automatically appear, if not, create a join by dragging the join field from one table to the other in the Query Design Window. You can now select Description as a field. However, the main table will expect to be updated with an ID, not a Description. To this end, it is a good idea to use comboboxes in forms.

 

[lespaul]

Remou,
I don't think rproactive is using lookup fields.

As far as your 2nd question, read the SQL Join article below to see how to get the "name" from the lookup field. You basically join together the tables based on the keys:

SELECT * FROM MainTable
INNER JOIN LookupTable on MainTable.ForiegnKey = LookupTable.PrimaryKey

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[rproactive]

thanks so much for your response-

OK let me take it one at a time.

I have no “Evil” lookup fields- I do have lookup type TABLES as comapared to Main tables. I did in fact have lookup fields and become aware of the problems so this effort is the fallout of removing all the field Lookups in my tables.

I also have relationships between all the tables except:
Two tables L_RR and L_RRSub which are related to each other (this is a primar and sub category type relationship) these tables in turn are related to a main table M_DepositSlip. The problem is when I try to set up a relationship to the main tabel, Access states the relationship type is indeterminante. I can select one of 3 choices:
1. Only include rows where the joined fields from both tables are equal

2. Include ALL records from ‘L_RRSub’ and only those from ‘M_DepositSlip’ where the joined fields are equal.

3. Include ALL records from ‘M_DepoistSLip’ and only those records from ‘L_RRSub’ where the joined fields are equal

I think the answer is 3??

The query that’s not working

SELECT 
M_FundPaid.FundPaidSelForDeposit, 
M_FundPaid.FundPaidDepositStatus, 
M_FundPaid.FundPaidAmount, 
L_TypeFund.TypeFundName, 
L_Bank.BankName, 
M_FundPaid.FundPaidDocumentNumber, 
Q_DepositRR!RRName AS Expr1, 
Q_DepositRR!RRSubName AS Expr2
FROM 
L_TypeFund 
INNER JOIN 
(L_Bank INNER JOIN M_FundPaid ON L_Bank.Bank_ID = M_FundPaid.FundPaidBanks_IDs) ON L_TypeFund.TypeFund_ID = M_FundPaid.FundPaidTypeFunds_IDs
WHERE 
(((M_FundPaid.FundPaidSelForDeposit)=True) AND (([Q_DepositRR]![RRName])=[Forms]![F_DepositSlip]![cboRentRollMonth]) AND (([Q_DepositRR]![RRSubName])=[Forms]![F_DepositSlip]![cboDepositNumber]))
ORDER BY M_FundPaid.FundPaidTypeFunds_IDs;

The Query Q_DepositRR

SELECT 
L_RR.RR_ID, 
L_RR.RRName, 
L_RRSub.RRSub_ID, 
L_RRSub.RRSubName
FROM 
L_RR INNER 
JOIN 
L_RRSub ON L_RR.RR_ID=L_RRSub.RRSubRRs_IDs;

So in short
1. is the relationship I set up correct one?
2. Why is query returning a blank record set?

Thanks for your comments & suggestions.

 

[lespaul]

Two tables L_RR and L_RRSub which are related to each other (this is a primar and sub category type relationship) these tables in turn are related to a main table M_DepositSlip.

can you show me these tables like this?
[tt]
L_RR
FieldName (PK)
SubFieldName (FK)
other fields

L_RRSub
SubFieldName (PK)
other fields

MainTable
Fields
[/tt]

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[rproactive]

thanks for your reply-

L_RR
RR_ID (PK)
RRName
RRAbbrev
There is no (FK)

RRName typical values: 2006 01 JAN; 2006 02 FEB; 2006 03 MAR

L_RRSub
RRSub_ID 	(PK)
RRSubName
RRSubAbbrev
RRSubRRs_IDs (FK)

RRSubName typical values: DEPOSIT #01; DEPOSIT #02; DEPOSIT #03

M_DepositSlip MainTable
DepositSlip_ID  				(PK)
DepositSlipDateDeposit
DepositSlipDateConfirm
DepositSlipAdjustmentDollar
DepositSlipAdjustmentExplaination
DepositSlipRRs_IDs  			(FK)
DepositSlipRRSub_IDs			(FK)
DepositSlipSites_IDs		(FK)from table L_Site

L_Site table
Site_ID	(PK)
SiteAbbrev
SiteName
SiteAddress
SiteCity
SiteState
SiteZIP
There are no FK

SiteName typical values: ORION; TOBIAS; RAMP

 

[rproactive]

Following are relationships of the 3 tables- though many help

RELATIONSHIPS
[b]L_RR[/b] RR_ID (PK)            1 to many 
[b]L_RRSub[/b] RRSubRRs_IDs

[b]L_Site[/b] Site_ID             1 to many 
[b]L_SiteSub[/b] SitSubSITES_IDs

[b]L_RR[/b] RR_ID                 1 to many 
[b]M_DepositSlip[/b] DepositSlipRRs_IDs

[b]L_Site[/b]Site_ID              1 to many 
[b]M_DepositSlip[/b] DepsoitSlipSites_IDs

[b]M_DepositSlip[/b] DepsoitSlipRRSub_IDs ----> 
[b]L_RRSub[/b] RRSub_ID

the last relationship Iam not sure about. Join type: Include ALL records from M_DepositSlip and only those records from L_RRsub where the joined fields are equal