Lookup name in a query on a non-joined table

[karmacable]

I am attempting to have a query show the name of a product rather than it’s ID. This would be easy if the table used as reference were joined with the main table, but due to the necessary design they cannot be. So I’m trying to figure out a way to reference the column lookup through a query, which I know I cannot do. However, someone suggested that coding it as a public function and referencing it might be the answer. Here’s what I’ve tried and where I’m stuck;

Public Function Reference()
Dim Rigname As Variant
Rigname = Me.cmb1stCamARig.Column(1)
End Function

And for the query;

Field: Project__________1stCamARig______ Camera A Name: ????
Table: tblProject _______tblProject

What syntax do I used to have ‘Camera A Name’ call the Reference() function and show the 1st_CamA_Rig name?

Thanks

[ssanfu]

Would you provide some examples of the Id and product name? 3 or 4 would help.
What is the table name?
Is the ID a number or a string?
What is the name of the field that has the ID?
What is the name of the field that has the product name?

[karmacable]

Okay, let's give a simplified example;

TABLES

tblCamera
**CameraID------Autonumber (Primary)
**CameraName---Text
**CameraPrice----Currency

tblProject
**ProjectID------Autonumber (Primary)
**ProjectName---Text
**CameraA-------Text (will hold value of 1st combo box selection)
**CameraB-------Text (will hold value of 2nd combo box selection)

FORM

frmProject (set to tblProject as it's source)
**txtProjectName (user input of new Project's name)
**cboCameraA (combo box set to tblCamera as it's Rowsource, and stores CameraID into field CameraA as it's Controlsource)
**cboCameraA (combo box set to tblCamera as it's Rowsource, and stores CameraID into field CameraB as it's Controlsource)

There's a few things I've discovered in setting this up;

- If I create a relationship with the table by creating a CameraID foreign key in tblProject, then both CameraA and CameraB will show the same selection if either one is selected. This is obviously because both combo boxes have the 'same' table as their Rowsource.

- If I create duplicate Camera Tables (exact same information but just copied for both cameras), then I can create foreign keys into the Project Table and CameraA / CameraB will have individual selections. However, I don't have just two combo box selections on my form....I have 50! So of course, making 50 copies of the same table is not only a generally bad idea, I don't even think a query would allow it.

So as you can see, I am limited to the tables not being joined, and my combo boxes looking up their list in the Camera table, so they can individually save their selections to the record.

Now we're back to my original question;

How can I setup a query with non-joined tables like this where the Camera's Name will show up?

Thanks and let me know if you want other info...

[ssanfu]

<snip>
There's a few things I've discovered in setting this up;

- If I create a relationship with the table by creating a CameraID foreign key in tblProject, then both CameraA and CameraB will show the same selection if either one is selected. This is obviously because both combo boxes have the 'same' table as their Rowsource.

- If I create duplicate Camera Tables (exact same information but just copied for both cameras), then I can create foreign keys into the Project Table and CameraA / CameraB will have individual selections. However, I don't have just two combo box selections on my form....I have 50! So of course, making 50 copies of the same table is not only a generally bad idea, I don't even think a query would allow it.

So as you can see, I am limited to the tables not being joined, and my combo boxes looking up their list in the Camera table, so they can individually save their selections to the record.

Now we're back to my original question;

How can I setup a query with non-joined tables like this where the Camera's Name will show up?

Thanks and let me know if you want other info...

Attached is an Access2003 mdb. I *think* you should be able to open it. I set it up as per your post - except that I linked the tables and set CameraA & CameraB field types a long.
I only added three cameras. Add more and test the form.

Is this what you are trying to do??

[karmacable]

Thanks for taking the effort to setup the DB example (I guess I should have just forwarded it to you to save the time).

But if you look at your CameraID foreign key relationship, it's not doing anything. If you set it as the Control Source of CameraA & CameraB on the form, then both will be linked to the Camera table, but both will show the same selection as well.

Also, if you currently try to run a query to get the camera name the way you have it setup, it will return nothing (again, because it's not linked to anything).

If anyone else wishes to view his DB, it's pretty much setup the way I currently have mine. So the problem persists, how to show the camera name from non-joined 'fields' (I guess I should have been more specific the first time)?

[ssanfu]

But if you look at your CameraID foreign key relationship, it's not doing anything. If you set it as the Control Source of CameraA & CameraB on the form, then both will be linked to the Camera table, but both will show the same selection as well.

Also, if you currently try to run a query to get the camera name the way you have it setup, it will return nothing (again, because it's not linked to anything).

It took me a while - I finally understood what you meant.

Actually, in the relationship window, you don't need to set a relationship between the two tables. Since you are using combo boxes to display the camera name, then storing the PK, it doesn't matter if the tables are related.

When you create a query, add two copies of "tblCamera". Link the first table to tblProject.CameraA and copy 2 to tblProject.CameraB. In the query grid, drag "CameraName" from "tblCamera" to a column so it looks like "CamA:CameraName" and drag "CameraName" from "tblCamera_1" to a column so it looks like "CamB:CameraName.

Here is the query:

SELECT tblProject.ProjectName, tblCamera.CameraName AS CamA, tblCamera_1.CameraName AS CamB
FROM tblCamera AS tblCamera_1 INNER JOIN (tblCamera INNER JOIN tblProject ON tblCamera.CameraID = tblProject.CameraA) ON tblCamera_1.CameraID = tblProject.CameraB;

[karmacable]

Again, I truly appreciate your efforts / enthusiasm, but unfortunately if you recall my original post;

- If I create duplicate Camera Tables (exact same information but just copied for both cameras), then I can create foreign keys into the Project Table and CameraA / CameraB will have individual selections. However, I don't have just two combo box selections on my form....I have 50! So of course, making 50 copies of the same table is not only a generally bad idea, I don't even think a query would allow it.

I already learned that, and it's exactly what I'm trying to avoid. The small DB example was meant to illustrate a much larger issue I'm having, and I know there's a coding solution to it, just not sure about the syntax, steps.

[ssanfu]

Yep, I totally spaces the part about "50 combo boxes".

If these are all (or mostly) camera names/brands/models, then your structure is not normalized. So the first step would be to normalize the tables. Otherwise, you probably will have to store the camera name rather than store the camera FK