General discussion

Locked

Microsoft Access help

By Navjun ·
I'm making a guest list for my wedding and I have done the follwoing. one table with all the names and address and how many people are in each family and a field that connects to a second table with just table numbers in it. so this way I could go to this certain family and choose a table number for them to sit , so I'm able to choose the same table number for more than one family. Now I would like to make a query that would tell me how many people are at each table and warn me if I go over a certain limit.
Thanks in advance

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by Navjun In reply to Microsoft Access help

Also it would be helpful if the query show me the names of the people in each table and then tell me the number of people in that table.

Collapse -

by Navjun In reply to Microsoft Access help

Point value changed by question poster.

Collapse -

by ESchlangen In reply to Microsoft Access help

You will have to write two queries. I setup a table with columns for the family name, address, number of family members and table assignment. Then I created a table with columns for the table numbers and table capacities.

In the first table, I set the table assignment column to lookup from the second table using a combo box. This allows me to assign a table to each family from the table view using a mouse click. After the assignments were complete, I wrote one aggregate query based on a join of the two tables by table number = table assignment and included the table number as a group by and the number of family members as a sum. Then I wrote a second query using the first query joined to both of the tables. This query shows each table number, the family name(s) of those families assigned to that table, the total family members in each family and an iif statement that displays "OVER CAPACITY" if the summed number of family members exceeds the stored capacity of that table. I know this is difficult to follow. If you will email me, I will send you the test db that I used to test the query.

Collapse -

by Navjun In reply to

Poster rated this answer.

Collapse -

by Mark W. Kaelin Editor In reply to Microsoft Access help

This is hard to explain in this format. I asked Ramon Padilla to write up a solution for you - look for it next week. I'll post a link when it publishes.

Oh, and congratulations on the wedding

Collapse -

by Navjun In reply to

I actually got it by puting some time in to it.
I'll appriciate the link also
thanks

Collapse -

by Navjun In reply to Microsoft Access help

This question was closed by the author

Back to Software Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums