Append query that adds records to multiple tables

[tyson9699]

Hello everyone, I'm new to Access and I would like to ask if there is a way you can create a single query that adds new records to at least 2 tables?





To be more specific, I have 2 tables: Bookings and Course Schedule (which contains a "Number of bookings" field).
I want to make a query that allows users to add a new record in the Bookings table, and by doing so, they are also adding 1 value to the "Number of bookings" in the Course Schedule table.



It now seems to me like query design only allows you to add records to one table at a time, and you can't have multiple query functions (eg, append and update) in one query.

[kd2017]

Correct, you can't execute multiple sql statements in one saved query in access.

What do your tables look like? Is it incrementing a record or adding a new record to the schedule table?

How is the user executing the query to begin with? If it's a button then just have the button call the second query. Or you could call it on a form's AfterInsert event. Or a number of other ways like a table macro for example.

[tyson9699]

Thank you for the response. I thought of another way to do it, which is by somehow making the fields in the "Number of bookings" somewhat like the =SUMIF function in Excel. Is there an equivalent function in Access?

[Minty]

If you are able to calculate the number of bookings from other data, then you shouldn't store this information, simply calculate and display it when you need it in a query.

A picture of your tables design or some sample data would assist in giving you the correct advice.

[tyson9699]

If you are able to calculate the number of bookings from other data, then you shouldn't store this information, simply calculate and display it when you need it in a query.

A picture of your tables design or some sample data would assist in giving you the correct advice.

This is what my relationship is looking like:

So this is a school project that I have to design a database for a gym. This gym has this many members and each member has a Member ID. The gym offers a few courses, each course is identified with a Course ID and has a limited capacity.
The gym members can book for a course, and the Bookings table will link the members with the course schedules. What I am trying to do now is creating a query that adds a Booking ID in the Bookings table when the user assigns a member to a course, and at the same time, the "No of bookings" field in the Course Schedule table updates whenever there is a new booking in the Bookings table.
I would also like to restrict the number of bookings to not exceed a course's capacity. For example, course A has a capacity of 10, and when the number of bookings is 10, the user cannot add any more bookings to course A because the capacity is reached.

[Bob Fitz]

This is what my relationship is looking like:

So this is a school project that I have to design a database for a gym. This gym has this many members and each member has a Member ID. The gym offers a few courses, each course is identified with a Course ID and has a limited capacity.
The gym members can book for a course, and the Bookings table will link the members with the course schedules. What I am trying to do now is creating a query that adds a Booking ID in the Bookings table when the user assigns a member to a course, and at the same time, the "No of bookings" field in the Course Schedule table updates whenever there is a new booking in the Bookings table.
I would also like to restrict the number of bookings to not exceed a course's capacity. For example, course A has a capacity of 10, and when the number of bookings is 10, the user cannot add any more bookings to course A because the capacity is reached.

Can you post a copy of the db so we can see the tables and their relationships

[Micron]

Have to wonder if this is from the same OP (different name) or it's a similar post because it's an assignment given to many students. The request seems the same - update a Booking field for Courses. In other words, storing a calculated value.

https://www.mrexcel.com/board/thread...ccess.1175611/

EDIT - ignore that. Might help if I read everything first.
So this is a school project that I have to design a database for a gym

[tyson9699]

Can you post a copy of the db so we can see the tables and their relationships

Here's the link to the file: https://drive.google.com/file/d/1Nf6...ew?usp=sharing

[tyson9699]

Have to wonder if this is from the same OP (different name) or it's a similar post because it's an assignment given to many students. The request seems the same - update a Booking field for Courses. In other words, storing a calculated value.

https://www.mrexcel.com/board/thread...ccess.1175611/

EDIT - ignore that. Might help if I read everything first.
So this is a school project that I have to design a database for a gym

I actually think this is one of my colleagues let me check out the thread. Thank you .

[pbaldy]

Posts 8 & 9 were moderated, I'm posting to trigger email notifications.

[Minty]

Okay it is an assignment.
Most of us have a unwritten rule that we won't do homework, however what probably won't impress your tutor is that the design (if you were given it as a starting point) is poor due to storing a calculated value.

You can (at any time) use a DCount() function to return the current number of bookings on a course.
What you should have somewhere is a MaxCapacity field for each course, so that you have a value you can look up to see if it is exceeded.

So somewhere you in the course booking form you should have a combo box that has a selection query behind it that only allows the course to be selected if the DCount() of the bookings is < the MaxCapacity field on the course.

Now you have something to go and do some further research on

[tyson9699]

Okay it is an assignment.
Most of us have a unwritten rule that we won't do homework, however what probably won't impress your tutor is that the design (if you were given it as a starting point) is poor due to storing a calculated value.

You can (at any time) use a DCount() function to return the current number of bookings on a course.
What you should have somewhere is a MaxCapacity field for each course, so that you have a value you can look up to see if it is exceeded.

So somewhere you in the course booking form you should have a combo box that has a selection query behind it that only allows the course to be selected if the DCount() of the bookings is < the MaxCapacity field on the course.

Now you have something to go and do some further research on

Thank you very much for the suggestions .
So what you're telling me is that there shouldn't be a "No of bookings" field at all? The design was suggested by our tutor so I don't think it will bother him that much since we've just begun with getting to know Access, but do you think that it will be easier for me to achieve what I am trying to do by dropping that field?

[Micron]

According to the other thread, the field is a requirement. If it's not, then don't use it as it is the wrong approach. Each booking should be a record which includes 1 in a field each time you book somebody, grouped by whatever constitutes one instance of a course session. That is, the sessions should have a way to distinguish between the first session of Science 101 and the next time Science 101 is taught. To check the current bookings for the course you DSum("theCountField","theTableName","theCriteria") . The criteria needs to filter the records so that it only counts each record (1) of a booking for a particular course. If the session has an identifier in each record you could use that. If you went by start date it could work as long as no 2 different courses had the same start date.

As for doing homework, I agree. I will attempt to lead and instruct though. As long as I think that some attempt to implement suggestions and/or follow up with suggested reading is happening, I'm also good with explaining/suggesting/making corrections to code, sql, expressions and the like, but I need to see attempts from the OP because I'm not here to do student assignments.IMO they learn nothing from that.

[tyson9699]

According to the other thread, the field is a requirement. If it's not, then don't use it as it is the wrong approach. Each booking should be a record which includes 1 in a field each time you book somebody, grouped by whatever constitutes one instance of a course session. That is, the sessions should have a way to distinguish between the first session of Science 101 and the next time Science 101 is taught. To check the current bookings for the course you DSum("theCountField","theTableName","theCriteria") . The criteria needs to separate each record of a booking for a particular course. If the session has an identifier in each record you could use that. If you went by start date it could work as long as no 2 different courses had the same start date.

As for doing homework, I agree. I will attempt to lead and instruct though. As long as I think that some attempt to implement suggestions and/or follow up with suggested reading is happening, I'm also good with explaining/suggesting/making corrections to code, sql, expressions and the like, but I need to see attempts from the OP because I'm not here to do student assignments.IMO they learn nothing from that.

I'm not trying to make anyone do my homework for me, of course. I've done most of the assignment and this is the only task that I am left with. I've spent like 7 hours on this task alone trying and researching Access since we're absolute newbies and our tutor barely provided any information to complete this task. What I am trying at the moment is creating 2 queries, one Append query to add a new booking, one Update query to add one value in the corresponding number of bookings for the booked course, and then create a Macro that runs both of these queries. The first query works out well, however, I'm still struggling to design the second query so that it updates the correct value for the correct field. My idea is that, the first query will ask user to enter the value for Schedule ID and Member Id and create a new record accordingly, and the second query will ask the user to somehow refer to one of the fields in the Course Schedule table so that it adds one value to the correct cell. For the second query, I chose Schedule ID as the identifier, so the order of the pop-up windows will be something like: Enter Member ID -> Enter Schedule ID (query one complete - add new record to Bookings) -> Confirm Schedule ID (query two complete - add 1 to the correspondent No of bookings)

At the moment, the design for the update query looks like this: , however, it's not working correctly and I don't know where the problem is. When I run the query, although it identifies the row correctly, it adds incorrect values to the field and these values still can exceed the Capacity.

[tyson9699]

At the moment, the design for the update query looks like this: , however, it's not working correctly and I don't know where the problem is. When I run the query, although it identifies the row correctly, it adds incorrect values to the field and these values still can exceed the Capacity.

So what I'm seeing is, instead of adding "1" to the No of bookings field, the query is adding the number of times a course appears in the Bookings table. For example,Attachment 45671 KBX7 appears 7 times in this table, so when I run the second query, it adds 7 instead of 1 to the field "Number of bookings". I know there's something wrong with the syntax in the design, but unfortunately I am not experienced enough with Access to tell exactly what it is.