Setting field value based on 2 other field values?

[Soulbait]

Disclaimer: I am really bad at programming

With that out of the way, I am looking to setup a field value to be a Description, based upon the value of of 2 number fields
the 2 number fields are as follows

1,1
1,2
1,3
1,4
1,5
1,6

And then there is 2,1 etc..., 3,1 etc and 4,1 etc.

The fields for those are ID1,ID2 depending on what those are I need it to fill in the Descript column with a short preset description.

Is there a way to do this, and how would I do it if possible

Soulbait
"Despite the cost of living, its still quite popular"

 

[WildHare]

Yes there is and it does not require ONE LINE of programming.

Set up a look up table that has your two IDs and the description. Link to this table from your main guy where you just have the two ID fields.

There is a trick to setting up the links - when you create the first link from ID1 in lookup to ID1 in main, add the other field in the "Edit Relationship" dialog. You should end up with TWO lines, linking ID1 and ID2 in the lookup table to ID1 and ID2 in the main table.

Your query can then display the description that goes with each 'pair' of IDs.

Jim

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[Soulbait]

Thank you I will give that a try in the morning.

 

[Soulbait]

Well I think I'm close but my statement seems to be a little off.

Here is the statement its using:
SELECT [CardType Lookup].Question, * FROM [CardType Lookup], [Card 1] WHERE ((([CardType Lookup].[Card Type])=[Card 1]![Card Type] And ([CardType Lookup].[Question #])=[Card 1]![Question #]));

It is pulling the same Question for any question number.

I can't see where my statement is wrong but I'm not that good at this.

 

[WildHare]

I just did this in about 3 minutes - here's what my query SQL looks like:


SELECT MainTable.Field1,
MainTable.ID1,
MainTable.ID2,
IDLookup.Description,
MainTable.OtherStuff
FROM IDLookup INNER JOIN MainTable
ON (IDLookup.ID2 = MainTable.ID2)
AND (IDLookup.ID1 = MainTable.ID1);


If you post an email addy, I can shoot you my sample file.

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[Soulbait]

soulbait @ softhome.net

Thanks

 

[Soulbait]

Actually I just got it to work for the most part, however it has a memory of ones I've chosen before, I do not want it to keep this memory, I want it to ONLY show the one that it is suppose to be.

 

[Soulbait]

Thanks for the email, however we seem to have a little miscommunication.

I'm not trying to setup a query to search 2 tables.

What I have here is a table with roughly 12 fields with no primary key. Named Card1
The fields are:
Provider ID, Card ID, Card Type, Question #, Question, SA, A, D, SD, NO, Comment

and in another table named CardType Lookup i have the fields
Card Type, Question #, Question

the CardType Lookup Table will not be changing it is a set table

Now In Card 1 Table, I would like for the Question field to autopopulate when I enter the data into Card Type and Question Field.

The statement you gave me almost works for that, except that it I have it set to a drop down box which I don't want, and it has only worked for a few of the Questions.

In advice you could give would be really helpful, as you have already gotten me further then I got on my own.

 

[WildHare]

Well, first off, unless I misunderstand, your CARD1 table should NOT be storing the "Question" data - it's being duplicated by the same information in the CardType Lookup table.

In this case, what you could use is a DLOOKUP function to return the QUESTION, given the CARD TYPE and QUESTION #

=Dlookup("Question", "CardType Lookup", "CardType ='" & CardType & "' and [Question #] = " & [Question #])

The confusion will come in to play with your duplicated field names and all. It might be easier to rename your fields to something like CTYPE and QNUM in one or the other tables, to make it easier to keep things apart.

The DLOOKUP function works by taking ONE or more VALUES, and finding the MATCH(s) in another table, and returning a third value:

Dlookup("SomeGUY", "SomeTable", "SomeTable-KEY = {value}&quot

You have to watch out for text keys that need to be surrounded by quotes, so you end up with a bunch of single quotes inside double quotes and all. It's a pain in the buttocks if you're not careful.

I think this should do what you want.

If necessary I can send over another example.

Jim



Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[Soulbait]

for some reason this is just not working for me, I tried th Dlookup as suggestiong but it says there is either a misspelling or that it doesn't exist.

I've double checked the format and am about to change the fields to without spaces but that means redoing about 6 forms.

If you wanted to send the example that might be best...

Again, thanks for your patience and help


Soulbait
Now with a dented wall near his head...

 

[WildHare]

Ok give me a min or two - I have a VP breathing down my neck.. LOL

J
Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm