Using MS Access/Access queries


QUESTION: Hello Scott

I hope you can help.

I have a database with four tables:

tblMembers   -   MemberID (Auto)
        MemberLName (Text)
        MemberFName (Text)

tblMemberEvent-   MemberID (Number)
        EventID (Number)

tblEvents   -   EventID (Auto)
        Start (Date/Time)
        End (Date/Time)

tblTypes   -   TypeID (Auto)
        Type (Text)

After setting up the appropriate relationships I have created a query which gives me the following fields when I run it:

  Member (concatenated MemberLName and MemberFName)

Everything works OK and I can create relevant data entry forms and reports using this query.

What I now want to do is expand my database with an additional table, or tables, which allows me to further define the activities within the various types. I have created an additional table as:

tblDetails   -   DetailID

I thought that, by simply adding an additional field, DetailID (Number), to the tblTypes and adjusting the relationships between the tables. This however proved not to be the case.

Perhaps you can help me?


ANSWER: First, a comment on your structure. You should not be using that query for data entry. I'm actually surprised you can enter data using it. You should have a main form bound to tblEvents and a Subform bound to tblMemberEvent linked on EventID. From there you can add members to an event. Similarly, the TypeID should be entered using a combobox that is bound to the TypeID field but looks up the Type from the tblTypes table.

Second, for me to answer you, I need to understand what the Types and Details are and how they relate to events.

[an error occurred while processing this directive]---------- FOLLOW-UP ----------

QUESTION: Hello Scott

Back again with further information.

The main form contain

Looking at the properties of my data entry form, the record source of the main form is:

SELECT tblEvents.*, tblTypes.DetailID FROM tblTypes INNER JOIN tblEvents ON tblTypes.TypeID = tblEvents.TypeID;

For the subform, the source object is:

tblMemberEvent Subform

The controls on the main form are EventID which is an autonumber, Start and End being Date/Time, and Type which is selected from a combo box with the following row source:

SELECT tblTypes.TypeID, tblTypes.Type FROM tblTypes;

The subform allows entry of members attending an event and are selected from a combo box also. In this case the row source is:

SELECT tblMembers.MemberID, [MemberLName] & ", " & [MemberFName] AS Expr1 FROM tblMembers ORDER BY [MemberLName] & ", " & [MemberFName];

This seems to work OK and I think is what you refer to in the earlier part of your reply. Is this so?

As for the second part of your reply, Events may be of several Types, at the moment three. Say, for example, they are Type A, Type B and Type C. Up to this time data entry and reporting have presented no problem. For example, I can get a report for the time spent by each member at each type of event.

I now want to expand the database to allow more specific information for the Types of Events using a further table, tblDetails. This could be achieved using a combobox listing the various details. I want to be able to place this combobox into the main data entry table.

This is where I am having problems.

I hope I am being more explicit here.

Thank you Scott


Ok, I still need to understand details more. For example are details applied to types? For example, Type A might have Detail 1, 3, & 5, Type B might have 2, 4 & 6? Or could Detail 3 apply to both Type A and B?  Or dopes detail just apply to an event not to a type. So Detail 3 would apply to Event 123 and 456, even though 123 is Type A and 456 is Type B.

So you need to clear that up.

You shouldn't include tblTypes.DetailID in the Recordsource of your form. If its a FK in the Events table, then you should have it there, If not, then it should be on a Subform for the Types table.  

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2017 All rights reserved.

[an error occurred while processing this directive]