Using MS Access/Access queries
QUESTION: Hello Scott
I hope you can help.
I have a database with four tables:
tblMembers - MemberID (Auto)
tblMemberEvent- MemberID (Number)
tblEvents - EventID (Auto)
tblTypes - TypeID (Auto)
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.
---------- 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:
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.