query in form

[xomich16xo]

hi, im relatively new at access....but i need to figure this out...

i need to make a database to record the number of people going on a visit. now i have given each visit a unique id as there will be multiple people on each visit.

now i have a form where the visit id will need to be chosen and each visit can have a maximum of 10 people.

my question is once the visit id eg "ABC123" is chosen...i would need to make a query to count the number of "ABC123" in the table to check if the max amount has been reached...is there a way that once a press a command buttonon the form....the id "ABC123" is chosen, count function performed on it and its result presented in the form....

nt: there are more than one ids so i have to find a way for the query to be run on the id previously chosen on the form.

thanks

 

[jzwp22]

i need to make a database to record the number of people going on a visit. now i have given each visit a unique id as there will be multiple people on each visit.

Since a visit will have many people that describes a one (visit)-to-many (people) relationship. I would assume that a person may go on many visits over time which describes another one-to-many relationship. When you have 2 one-to-many relationships between the same two entities (people and visits in your case) you have a many-to-many relationship which requires a junction table.

tblVisits
-pkVisitID primary key, autonumber
-txtVisitNumber
-dteVisit (date of visit)


tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName

tblVisitPeople
-pkVisitPeopleID primary key, autonumber
-fkVisitID foreign key to tblVisits
-fkPeopleID foreign key to tblPeople

You would need a query to count the number of people assigned to a visit

SELECT fkVisitID, Count(fkPeopleID) as CountOfPeople
FROM tblVisitPeople
GROUP BY fkVisitID

You can then create a second query that joins the visit table to the query above. You can then use a combo box on your form that references this second query. I've attached a sample database that illustrates the table structure and the queries. Please take a look at the form named frmFormWithComboBox to see it that is what you are after.