populate column based on entry - novice needs help

Question

populate column based on entry - novice needs help

hsb2009mail

SSC-Addicted

Points: 426
More actions
February 26, 2010 at 7:53 am

#219453

I have a table (Table1) with multiple columns, name, address etc. I have a column named offcodeID which uses a drop down box to select one of 10 office codes. The codes correspond to another table (table2) which lists the codes along with the office name and address.
In table1, I currently have three more columns that have drop down boxes with the office names, phone numbers and addresses...the info contained in table 2. Currently the user selects the code, the name , the phone number etc which is silly but I haven't figured out how to populate the office names, phone numbers and address columns when the office code is selected rather than having the user select the information.
I have successfully executed this in ACCESS but trying to move away from ACCESS. I am a complete SQL Server novice and I bought a book to get me started with this but I'd appreciate a third grade level explanation. Thanks very much.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

There are a number of ways to achieve that, but the simplest is to join the two tables together in your query. Are you familiar with how to do that?

If not, can you provide the definitions of the two tables? Then I can help you write the query.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

hsb2009mail SSC-Addicted Points: 426 More actions · Answer 2

i've actually made some progress.

I created the querry:

SELECT cbasagentroster.Lname, cbasagentroster.CellPh, cbasagentroster.AgentEM, cbasagentroster.offcodeID, officeinfo.offcodeID AS Expr1, officeinfo.office,

officeinfo.officePh

FROM cbasagentroster CROSS JOIN

officeinfo

Getting there but the CROSS Join is returning all the rows of the table2, the (officeinfo table)

So for the office codes: DBS MD and PI regardless of the selected code I get three rows, two fo which are returning the wrong information, IE address and phone of the wrong office.

I know its the CROSS but so far I haven't figured out the correct syntax for any other type of join.

Ed-586976 SSC Eights! Points: 903 More actions · Answer 3

you should be using left join and not cross join. Left join will give you all the values from Table 1 and only the matching corresponding values from tabe 2.

hsb2009mail SSC-Addicted Points: 426 More actions · Answer 4

Thank you. I've tried everything including 'left" join but I get a syntax error for any value except "cross"

LutzM SSC Guru Points: 107049 More actions · Answer 5

Please post your query so we can help you to get the syntax right.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

hsb2009mail SSC-Addicted Points: 426 More actions · Answer 6

SELECT cbasagentroster.Lname, cbasagentroster.CellPh, cbasagentroster.AgentEM, cbasagentroster.offcodeID, officeinfo.offcodeID AS Expr1, officeinfo.office,

officeinfo.officePh

FROM cbasagentroster CROSS JOIN

officeinfo

Thanks, baisically the idea is to poplulate the office address and phone number fields based on the entry of the office code froma drop down box.

Ed-586976 SSC Eights! Points: 903 More actions · Answer 7

Try this and let me know if you get an error.

SELECT cbasagentroster.Lname, cbasagentroster.CellPh, cbasagentroster.AgentEM, cbasagentroster.offcodeID, officeinfo.offcodeID AS Expr1, officeinfo.office,

officeinfo.officePh

FROM cbasagentroster

left outer join officeinfo

on cbasagentroster.offcodeID=officeinfo.offcodeID

group by cbasagentroster.Lname, cbasagentroster.CellPh, cbasagentroster.AgentEM, cbasagentroster.offcodeID, officeinfo.offcodeID, officeinfo.office,

officeinfo.officePh

hsb2009mail SSC-Addicted Points: 426 More actions · Answer 8

Absoulutely 100% perfect...no errors and everything where it should be.

Thanks very much.