Here's hoping someone can help - I've scanned thru all the old topics and don't see a solution to this yet.

I have an Add_Client form that includes radio buttons (No, Yes, and Unknown) - these are all pulled from tblYN. When I go to the details version of the Client page (see www.northcoastdogs.com/records/detail_clients.asp?Client_Number=13 for an example), I can't seem to get "No", "Yes" and "Unknown" to show up - instead it's 1, 2 or 3.

I think I'm just missing something basic like including the tblYN in the SQL statement calling on the client info...but I'm not sure?? If it helps, the SQL statement getting the client info is:

SELECT *
FROM tblClients
WHERE Client_Number = MM_ColParam

(Also, I'm assuming whatever fixes this problem can also be used to take care of the same issue with my House/Apt, Yard, and Veterinarian field problems! Fingers crossed!)

Thanks - Jeph

Assuming that tblYN has two columns, a uniqueID (ynID in the SQL below) and the label and there are 3 records in there that are these:
1 Yes
2 No
3 Unknown

then you will want to JOIN these tables on the detail_clients.asp page.
Because you only want the entry that is related to THIS customer we use a INNER JOIN, like this:

SELECT *
FROM tblYN INNER JOIN tblClient ON tblYN.ynID = tblClient.ynID
WHERE Client_Number = MM_ColParam

Joining tables is really usefull, you could achieve same result by altering the page but that would require a few if then else thingies. What happens now is that we draw out info out of 2 related tables in one recordset, by experimenting with more joins you can also draw in the labels for your other values that need to show not as values.
Just don't use this join on the page where client changes his info with the radios again !

With kind regards,

Dennis van Galen
Webmaster KPN Services
Financial and Information Services

Dennis-

Thanks for the prompt feedback, but I guess I'm still missing something here... I understand the INNER JOINS and have been using them elsewhere in the site, but the example you list (below) still doesn't seem to do the trick.

SELECT *
FROM tblYN INNER JOIN tblClient ON tblYN.ynID = tblClient.ynID
WHERE Client_Number = MM_ColParam

While I DO have the tblYN.ID field, I don't have a specific tblClient.ynID type of field...and this is where I might be causing my own problem. I have several different fields (Travel_Fee, Difficult_Case, and Discount) that refer to the one Yes/No/Unknown table (which, yes, has two columns - the ID and the word). I don't have a problem building the entire new client form and getting those specific words from the other table to show up.

input type="radio" value="%=(qryYN.Fields.Item("ID".Value)%" name="Travel_Fee"

(minus greater/less than signs by % signs)

But I don't know how to get the specific label again later on the detail page... Your SQL statement refers to a tblClient.ynID, but I don't know if that's something I need to add, or if we're on different tracks here?

Thanks again for all advice!!
Jeph

No problem Jeph,

Ummm, let me see what we have...

We have a client table, three fields in that client table are related to one single table... Hmmm, I would have created specific yes no for all three since it won't complicate things that much. MS-Access will not cooperate with this, it tells me to create a query and include that query in my SQL statement. So if you want to save yourself ALOT of trouble in the SQL department then I suggest setting up additional tables with yes no unknown for your other two fields. My SQL example assumed you allready had specific tables for each of the YNU options.

Even with these 3 seperate YNU tables our SQL ends up looking like:

SELECT tblClient.clientID, tblClient.clientName, tblYN.YNlabel, YN1.YN1label, YN2.YN2label
FROM YN2 INNER JOIN (YN1 INNER JOIN (tblYN INNER JOIN tblClient ON tblYN.ynID = tblClient.YN) ON YN1.yn1ID = tblClient.YN1) ON YN2.yn2ID = tblClient.YN2;
WHERE Client_Number = MM_ColParam

For speed i labeled the tables for what they are, YN table 1, YN table 2 and YN table 3.
My YN relates to your Travel_Fee
YN1 relates to Difficult_Case
YN2 would be Discount

You could also consider making them textfields and have the radio insert the text into them, yes would insert yes in the right place, etc... This would make the YNU tables a thing of the past.

Hmmm, i find myself writing confusing SQL statements at 1.30am ?!
It's a good thing i allready have week-end <img src=../images/dmxzone/forum/icon_smile_wink.gif border=0 align=middle> A bad thing that I left my SQL book at the office. And ashame that Macromedia didn't supply our country with enough FlashMX upgrade for windows, now i need to wait :'(

With kind regards,

Dennis van Galen
Webmaster KPN Services
Financial and Information Services

Dennis - Again, thanks for the prompt reply, and now get yerself to bed!! Being something of a newbie to alot of this database, Ultradev, SQL, etc stuff, I think I got the idea that you were supposed to be as efficient as possible, reusing tables, not having too much repetition, etc... So I figured I was being super-efficient in having various fields refer to one YN table!

Seems I just made a mess... A friend at work suggested I try creating a function that would just display the text for each of the numbers. A half hour with my Beginning ASP book and I've fixed both the Yes, No, etc prob as well as created a nice way to keep all my phone numbers as series of unformatted digits (ex: 3305555555) and then just have a function print it all tidy (ex: (330) 555-5555).

This is fun - there's always a way to get this code to do what you want! (I'm probably making a bigger mess for myself down the road!)

Thanks again - I appreciate your help!

-Jeph

I try helping where i can, i also noticed your phone nr post earlier and i want to give some thought on that.

What if, you store your numbers in one way and display them accordingly, I sign up at your website and enter my phone nr?
cell phone: +31-650 277 ... (local: 06-50 277 000)
home phone: +31-152 564 ... (local: 015-256 40 00)
work phone: +31-704 466 ... (local: 070-044 66 00)

How would you go about storing and retrieving / displaying the phone-nr's now ?
Does your expression and/or function still work ?
Or is it back to the drawing board...

I'll let you think on that and i'll go bug my bed and give my computer some rest now.
And just to show you were right, i sometimes throw efficiency out of the window for ease of use. I'm spoiled developing for a LAN intranet that sits ontop of one of our Backbones.

With kind regards,

Dennis van Galen
Webmaster KPN Services
Financial and Information Services

Ah HA! Nice try! Yes, there are so many different ways people could enter their phone numbers, but I'm doing this for the owner of the business. THEORETICALLY he should be the only guy working on this.

And, yeah, I know, I know, even he's likely to goof it up and put stuff in the wrong format.

Actually, let's be honest, he's VERY likely! Heh heh...

But now that I've got the format working as is, I'm going to figure out other tricks, like checking to see if the first number is a 1 (for long distance over here in the States), check the quantity and guess what format he's using, etc.

It's like a puzzle! Fun! I'm a geek - great....