Query Using Combo Box Information from Table

[LuckyLeo]

I'm a SUPER novice so am looking for surface-level...easy solutions. I'm building a database to track estimates/quotes that my company generates. One of my fields in each Estimate/quote record is "SalesmanName". I have a table that stores our Salesman names that is linked to our Estimates table. In the Estmates table, I have SalesmanName Data Type as "Text" - in Field Properties - Lookup - Display Control=Combo Box, Row Source Type=Table/Query, Row Source=tblSalesman...



So...I want a query to return all estimates for a specific Salesman...simple, right? Well...it seems that, since that field is a combo box - the query only returns 1 record of a blank combo box for SalesmanName? If I delete the relationship and change the display control to "Text Box" and simply type in each Salesman's Name, the query works perfectly. Please help...I'm sure there's a simple solution...

[aytee111]

You have it - the simple solution is to NEVER use lookups in tables. Also, don't put the salesman's name in any other table except its own. That table should have an autonumber as a primary key and then store that number (as a Long number) on all other tables.

[LuckyLeo]

Great...moving forward...

I have and autonumber key "SalesmanID" in tblSalesman. I also have a foreign key "SalesmanID" in tblProjEst. When establishing the relationship between tblSalesman and tblProjEst...should I connect the "SalesmanID"? Or SalesmanID from tblSalesman to SalesmanName in tblProjEst?

I've tried both...when running the query for "Brian Klock" it return nothing...because there is no text in the field that I'm searching...

[aytee111]

What you do is always name the fields the same - so on the salesman table the field is called "SalesmanID" and then wherever you need it you call it by that same name. Then your linking becomes a simple matter of connecting fields with the same name. MUST be the same datatype, that is why I mentioned that the field will be a Number/Long type on all other tables.

[LuckyLeo]

Wow...awesome...you've contributed to a mini-"Eureka" moment...thank you so much for that. This tiny barrier is now behind me! I'm sure I'll be back on with another remedial question soon enough!

[aytee111]

Glad to be of service! Please mark this as solved, it will help others.