Way to lookup Name based on ID?

[karmacable]

Hi Guys,

New to the forum and semi-new to Access (programmed intermediate level a decade ago). So my database is setup using lookup columns extensively, which as I've read in many different posts in this and other forums is genuinely looked down upon (I too have come across the '8 evils of lookup columns' list many people refer to).

I will in the near future rebuild my database to try and eliminate lookup columns, but for the time being I was wondering if there was a semi-painless way to program / figure out an answer to my problem. Here's the basic scenario as detailed as I can describe, two tables and one form;

tblCamera
CameraID-------CameraName -------CameraPrice
1 --------------Sony ---------------$10
2 --------------Panasonic -----------$12
3 --------------Nikon ---------------$14

tblProject
ProjectID --------ProjectName -------1stCameraName--- 2ndCameraname
1 ----------------Outdoor---------- (awaiting 1stcbo) ---(waiting for 2ndcbo)
2 ----------------Indoor------------ (awaiting 1stcbo) ---(waiting for 2ndcbo)
3 ----------------Everywhere --------(awaiting 1stcbo)-- (waiting for 2ndcbo)

frmProject (contains Project Name and two choices of Camera)
txtProject --> (User inputs name, essentially the unique identifier per record)
cbo1stCamera --> (User selects 1st Camera)
cbo2ndCamera --> (User selects 2nd Camera)
txtTotalPrice --> (Sum price of above selections)

In tblProject you can see that I've created two textboxes to hold the camera selections from the user. Right now my tables are not connected in any way, but my form's working great in doing a lookup for the individual Camera Names (via a VBA column lookup in the AfterUpdate properties for each). If i were to connect the tables, then whatever I choose in one combo box is the same in the other, hence why I cannot successfully join my tables.

So although the form properly displays and records the camera name selection for each record, in the data view only the CameraID shows for the 1st and 2ndCameraName fields. So when I attempt to run a report showing which cameras were chosen, only the ID's show up.
So although my above example is for a tiny set of info (my actual database form has more than 40 combo box selections for Camera possibilities), is there a generally painless at this point to simply translate / lookup the corresponding Camera names recorded in the
Camera Fields and have that show up in my report?

I realize this question might be better suited for the 'Forms' instead of 'Reports' forum category, and the moderator should feel free to move it there if he/she deems that to be the case.

Thanks for any advice, and feel free to go beyond my question scope and propose a solution for not working with lookups!

 

[pbaldy]

To clarify, lookup fields are looked down on. That means the Lookup tab in table design view.

As to your question, the normal method is to base the report on a query that joins the 2 tables together on the ID field, which allows you to include the other fields from the lookup table.

By the way, the two fields for cameras (perhaps more in the actual db?) is a normalization issue. I would likely have a related table for the camera(s) related to a project. Two cameras would be two records in that table.

 

[karmacable]

I would likely have a related table for the camera(s) related to a project. Two cameras would be two records in that table.

Ahhhh....I see the logic in that. I'll give it a shot when I get a chance today.

 

[pbaldy]

No problem; post back if you get stuck. Welcome to the site by the way!

 

[karmacable]

Thanks for the welcome!

So here's the current problem scenarios I'm having with your suggestion, and feel free to let me know exactly where I'm going wrong. All scenarios have three tables, similar structure to my initial post;

SCENARIO 1 - NO LOOKUP FIELDS
tblCamera
**CameraID____Autonumber
**CameraName_Text
**CameraCost__Currency
tblProject
**ProjectID____Autonumber
**ProjectName__Text
tblCombo
**ComboID_____Autonumber
**ProjectID____Number --> Joined in 'Relationships' to tblProject
**CameraID_____Number--> Joined in 'Relationships' to tblCamera
frmCombo --> Recordsource set to tblCombo
**txtProjectID
**cboCamera1 (control source is CameraID) --> Used the wizard that pops up when you create a combo box. Selected 'Get values from table', chose my table, chose all my fields and ordered by CameraName.
**cboCamera2 (control source is CameraID) --> Same steps as above.

In this scenario;
--I'm not sure how to get the ProjectName to show for input, as I'm only allowed to choose ProjectID for txtProjectID's recordsoure.
--In cboCamera1, my CameraName shows up fine for selection, but in tblCombo only the CameraID gets saved.
--cboCamera2 shows the same selection as cboCamera1, which is most likely because my control source on both is set to CameraID. The only way I've found to fix that is creating a duplicate tblCamera for it's own selection, but then I'll start having hundreds of duplicate tables (something I'm sure isn't the right way to do it).

SCENARIO 2 - LOOKUP FIELDS
tblCamera
**CameraID____Autonumber
**CameraName_Text
**CameraCost__Currency
tblProject
**ProjectID____Autonumber
**ProjectName__Text
tblCombo
**ComboID_____Autonumber
**ProjectID____Number --> Used 'Lookup Wizard' to link to tblProject
**CameraID_____Number--> Used 'Lookup Wizard' to link to tblCamera
frmCombo --> Recordsource set to tblCombo
**txtProjectID
**cboCamera1 --> (control source is CameraID) --> Used the wizard that pops up when you create a combo box. Selected 'Get values from table', chose my table, chose all my fields and ordered by CameraName.
**cboCamera2 --> (control source is CameraID) Same steps as above.


In this scenario;
--Same issues with ProjectName and setting the same control source on my combo boxes.
--In cboCamera1, my CameraName is now saved in tblCombo, which is exactly what I want!


So these are my questions;
1. Do I still even need a tblCombo if my tblProject keeps track of each record and it's name?
2. Is there a better way to keep track of each record's camera(s) selections and names than what I'm trying above with the lookup wizard?

Thanks for all suggestions!

 

[pbaldy]

tblCombo's purpose is to track the cameras used for a project, so in my mind you do need it. It's the alternative to camera1, camera2, etc that you had before. In the lookup field scenario, this statement is not true:

In cboCamera1, my CameraName is now saved in tblCombo, which is exactly what I want!

How could it, since the field is numeric? This is what lookup fields do, they disquise what's really going on. You see the name in the table, but the ID is actually what's saved. In your combo, you can simply hide the ID column so the user sees the name, or use this technique:

http://www.baldyweb.com/Autofill.htm

In the form, there would be no combo for the second camera. You would create a new record for it. The typical setup would be a form/subform, where the form was bound to the projects table and the subform, in continuous or datasheet view, was bound to the combo table.