Help with Lookup Fields on Form

[shathaway]

I am designing a database to track election machine activities at different vote centers. I have a form (frmActivity) where the user enters Activity information for a specific machine at a specific location. There needs to be 2 different fields for 2 users to "sign off" on each activity...one Republican and one Democrat. I have a tblUsers table that includes UserID, Initials, First_Name, Last_Name and a Party (Republican or Democrat) field. The user fields on frmActivity need to be combo boxes that pull initials from tblUsers. I need to somehow relate tblUsers to tblActivity so there can be a report that pulls the First_name and Last_name. I'm not able to relate tblUsers directly to tblActivity because then I'm not able to use 2 different initials from tblUsers. Someone suggested I create another table tblUsersActivity with the fields UsersActivityID, UserID, ActivityID...but, I'm not sure how to relate this to tblUsers and tblActivity so that it will work correctly for the form. I attached a copy of the database. Does anyone have any suggestions on how I can get this to work properly? Any help would be greatly appreciated!

Thank You,
Shannon

 

[Tim L]

I am not at all sure why you want to pull initials from tblUsers into the Activity form.

If an activity must have one Republican AND one Democrat 'sign-off' then there are two 1:n relationships that need to be modelled (each activity can have none or one member of each party sign-off).

The best way to do this would be to create two fields in Activity to hold the primary key information for each member of the respective parties.

Create two queries, one listing all Democrats, the other republicans (to be honest you'll probably only need the primary key (ID) field and the First and Last name fields combined into a single field) plus (not showing) the party field. The SQL for the Republican query might look something like this (giving us two columns).

SELECT tblUsers.UserID, tblUsers.First_Name & " " & tblUsers.Last_Name AS Name
FROM tblUsers
WHERE (((tblUsers.Party)="Republican"));

(You can adjust the 'Name' column to include the initials as well, or you could even include the party information but I don't think that it is necessarily relevant for this task.

Now add two fields to tblActivity, one for Democrats, the other for Republicans (both of type number, Long Integer). Create lookup references for both of them (ensuring that the bound column is the ID column, column 1 in my example above) to the respective queries and adjust the column widths so the the first column (the ID) width is zero, so that it does not show and when you select a person from the drop-down it shows their whole name.

If there MUST be a record of who has signed-off (and that information MUST be recorded when the activity is recorded in the database, then you could set the Required property for both fields to Yes. If the information may be recorded at a later date then leave it set to No.

Go back to the activity form and add the two new fields, Access should format the combo boxes automatically for you but will probably still need to adjust the width.

HTH

Tim

 

[shathaway]

Thanks for your reply, Tim. I created the two queries and added a Republican and Democrat field to tblActivity. I decided to add the lookup fields through the form instead of adding them to the table. The fields pull the full name and then store the UserID in the respective fields in tblActivity. However, I'm still not clear on how (or if I even need to) to relate tblActivity to tblUsers. I will need to design a report that pulls the full name for each user that signed off on an activity, so I will need to design a query that uses both tblActivity and tblUsers. I tried this and wasn't sure how to relate the UserID field from tblUsers to both the Republican and Democrat fields in tblActivity so I can pull the name. How would I go about doing this?

Thanks again,
Shannon

 

[Tim L]

...I decided to add the lookup fields through the form instead of adding them to the table. ...

That should not make any difference, however by adding the lookup fields into the actual table whenever you add the field to a form the work will be done for your automatically, rather than you having to repeat the work each time you create another form.

I created the two queries and added a Republican and Democrat field to tblActivity....The fields pull the full name and then store the UserID in the respective fields in tblActivity.

Good, this is the basis of how tblUsers is related to tblActivity.

However, I'm still not clear on how (or if I even need to) to relate tblActivity to tblUsers.

Use the same method that you used to create the relationships between tblEquipment_Type and tblEquipment, and tblEquipment and tblActivity to create the relationships. You should drag UserID from tblUsers once each for the Republican and Democrat listings in tblActivity. If you do it in the Relationships view whenever you add both tblActivity and tblUsers to a query Access should automaticaly replicate the relationship within the query. You can build the relationship in a new query each time you create one, but why create work for yourself?

Now, when you create the query, leave the 'republican' and 'democrat' fields from tblActivity behind and bring in the data from tblUsers. Access will automatically bring in the correct record from tblUsers.

Tim

 

[shathaway]

Thank you again. I tried what you suggested and I don't get any results when I try to run the query. I added the relationships under Relationships by dragging UserID over to Republican (enforcing Referencial Integrity) and then again for Democrat. Then I designed a query (qryUserNames) with both tblActivity and tblUsers. I added the fields ActivityID, Activity_Date, Activity_Description from tblActivity and First_Name, Last_Name from tblUsers and it returns no records...there are 9 records in the Activity table. What am I doing wrong here? I attached the database that includes the query.

Thanks,
Shannon

 

[Tim L]

Try this:

SELECT tblActivity.ActivityID, tblActivity.Activity_Date, tblActivity.Activity_Description, [tblUsers].[First_Name] & " " & [tblUsers].[Last_Name] AS Republican, [tblUsers_1].[First_Name] & " " & [tblUsers_1].[Last_Name] AS Democrat
FROM (tblUsers RIGHT JOIN tblActivity ON tblUsers.UserID = tblActivity.Republican) LEFT JOIN tblUsers AS tblUsers_1 ON tblActivity.Democrat = tblUsers_1.UserID;

 

[shathaway]

That worked! Thank you so much for all your help!

Shannon

 

[neileg]

That should not make any difference, however by adding the lookup fields into the actual table whenever you add the field to a form the work will be done for your automatically, rather than you having to repeat the work each time you create another form.

Tim, please don't reccomend that users apply a table level lookup. It only works in limited circumstances and causes all sorts of problems

http://www.mvps.org/access/lookupfields.htm

 

[Tim L]

Tim, please don't reccomend that users apply a table level lookup. It only works in limited circumstances and causes all sorts of problems

http://www.mvps.org/access/lookupfields.htm

Point taken.

However it looks like most of the points are irrelevant if good relational work is implemented to start off with and when a relational connection exists between the table using the look-up and the looked-up table, as is the case in this thread. Which leaves us with points seven and eight, both of which can be alleviated by removing the look ups once the initial design work has been done, an action that should take less time than the time saved by not having to potentially create the lookups on multiple forms.

I agree there are issues, and if they are known the designer can assess how much impact they are likely to have on their database and take action according to their situation. Thank you for highlighting this point.

 

[shathaway]

I have another question on the design of a different form and was hoping someone would be able to help me with it. I need to desin a form for tracking Election Day Problems. The user will need to enter the equipment serial number and then the date and have the Problem, Resolution, and Note fields for that piece of equipment (from tblActivity) come up on the form for data entry. I'm guessing I will need to link a subform to the master form somehow, but would like some advice on how I should go about it exactly. I designed a query (qryElectionProblems) using tblActivity and tblEquipment that pulls the ActivityID, Problem, Resolution, Notes, Activity_Date fields from tblActivity and the Serial_Number field from tblEquipment. I have a main form (frmProblemsMain) with 2 unbound textboxes, txtSerial and txtDate. I also have a subform (frmProblems_subform) with qryElectionProblems as the record source. I'm not sure exactly where to go from here. The user will need to type in the serial number, type in a date on frmProblemsMain and then be able to type in the Problems, Resolution, and/or Notes for that activity. Does anyone have any examples and/or suggestions on how I can get this to work?

Thank You,
Shannon

 

[Tim L]

I think that you need to use Access's built-in help file and search for items relating to subforms.

It would probably be worth your while checking out the sample databases that come with Access. Personally I've not looked at them, but then I know how to use a subform... They may be able to help.

One quick way to create a subform link would be create both the sub and master forms, then, with the master form in design view, click and drag the subform on the master form. Access will then guide you through a process to link the two.

With regards to your unbound textfiles, from their names it would appear that you intend to store the data entered into them but being unbound that can't happen unless you specifically transfer their contents into a field through code, or until you bind them.

HTH

Tim

 

[shathaway]

Thanks, I will do some more searching. Actually, the way I want this to work is the activity will have already been entered. For example, there's a record in the activity table for a piece of equipment with serial # ABC123 with a date of 7/20/2007. The scenario might be that someone calls on election day and says that a piece of equipment with serial # ABC123 has an error. The user would need to be able to open the Election Day Problems form and be able to enter the serial # ABC123 and the date (7/20/2007) and the problem, resolution, notes fields would activate so they could fill them in for that record. If anyone has any examples of how to do this, please let me know. It seems simple because it would be like opening the activity table, searching for a specific record and then filling in the problem, resolution, and notes fields...but, I need to design a user friendly form ...I'm just not exactly sure how to create it.

Thanks,
Shannon

 

[Tim L]

Create your subform first, i.e: the one into which the specific details about the actual problem are to be recorded.

Next create a form to show the equipments, leaving enough space for the above subform.

If you wanted to take it a step further, so that records have the heirarchy of:

Election Day
___+Machine ID
_______+Problem

then the equipments form would become a subform of the date form (but you probably don't need or want to do this).

Ideally you would have the user select the machine serial number, probably from a drop-down box limited to list (they could still type in the serial number, but the limit to list will ensure that they at least pick a valid one).

If the problems form is related correctly to the machine machines table (to which the forms are repectively associated via recordsource) you could use the combo-box wizard on the main form to go to a machine serial number, all of the problems that had been experienced with that machine could then be displayed automatically in the subform. You could also have the subform go automatically to a new record, rather than default to the first record in the set, but you're getting into the realms of using VBA here and I'm not sure how much you know. If you're not sure what to do search the forum for some likely examples.

The actual design of the forms would depend on what you/the customer wants. You could have the problem details as continous records or in table layout, for instance. The Machine ID form would, however, need to be in single form view to prevent confusion on the users part. It all depends on what you want to see/achieve. If you're still unsure of what you want then you probably need to do an analysis of how users are expected to work and what functionality you expect from the forms. Try sketching ideas on a piece of paper first and working in through in your head before leaping in directly to the creation phase.

HTH

Tim

 

[shathaway]

I think I have an idea of what I need to do...thanks for all your help!

Shannon