Auto Lookup Query 1

[Renalt]

I am trying to populate fields in a table following the auto lookup query guidelines from the "help" text (Access 97)and it isn't working. I think I've done everything mentioned. I have one table (Table 1)with four fields, one of which is a primary key. That field is joined to a field of the same name in another table (Table 2). Table 2 contains has all the fields of table 1, and some more. I want to type data into the joined field from Table 2 and have it pull the corresponding data from table 1 into the fields of table 2.

The fields in the query are from the "many" table. The joined field is not indexed. Any idea why it doesn't work?

Thanks,

R

 

[lespaul]

Table 2 contains has all the fields of table 1

you mean you're storing the same data in multiple locations?

Shouldn't do that, read fundamentals document below to see why.



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

 

[Renalt]

Table 1 is a 2 record table containing standard settings that usually apply to the records in table 2. I'm just trying to save the user from some typing. If the standard settings didn't change from time to time, I wouldn't insert them at all.

Is this still a bad design? Would be nice to get the autolookup to work anyway, just to know how to do it.

More detail: Table 1 has fields: Location,RPM,Weight,Color
with the following standard values:
South, 10, 20, Blue
North, 12, 200, Red

Table 2 has a bunch of fields including, Location, RPM,Weight, and Color. I want to be able to insert the South or North parameters into the records of table 2 when I enter South or North.

I've followed the directions, i think, and it's not working. Is this clear enough to work with?

Thanks a bunch,
R

 

[lespaul]

since you have the same information in two places, yes, the design should be reviewed, that breaks normalization rules.

I'm not real clear on what you mean by Auto Lookup Query?

A look up table usually works more along these lines. I use one for the possible verdicts of jury trials:

tblVerdicts
VerdictCode
VerdictDescription


tblTrials
TrialNum
DivisionCode
etc.
VerdictCode

so I store the verdict code with the Trial, but I display the description to the user for choices and in reporting. With this design you have flexibility and ease in changing or adding choices to the Verdicts.

 

[PHV]

I don't know the auto lookup query guidelines.
But for me play with a combobox based on table1 and play with its Column property in its AfterUpdate event procedure.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Renalt]

I decided to use a combo box based on Table 1 and then allow the user to add lines to table 1, should the standard conditions change.

Thanks for the tips folks. I don't know visual basic, so this works and is fairly straight forward. Keeps things normalized, i think.

Auto lookup is still a bit of a mystery, but I will try it again some time. Maybe I'll try to learn visual basic too.

Thank you,

R