Append query that adds records to multiple tables

[Micron]

In a select query criteria governs the query result based on what it finds in the table field where the criteria is added. Your entry isn't criteria, it is a "rule" or more exactly it is an expression. If you can figure out what the expression returns as a value, you'll realize why that value won't be found in the table.



It's not clear if you're going with the calculated field in the table (i.e. +1 to the current value for each new booking) or not. Please clarify. Might be a good idea if you copied, compacted and zipped your copy and post it here. It will avoid a lot of questions about your db schema, relationships, forms and all that. I for one will continue to look at and advise since you are earnestly attempting to do the work. I can't speak for others but based on other threads here I don't think I'll be the only one who will try to help.

EDIT - I saw that you posted an off site link. Some responders are at work and cannot access these sites. Some just won't because of other reasons. Better to post a sample here as suggested.

[tyson9699]

Thank you Micron, here is the file I'm working with. The first query is named "Assign to Course" and the second query, which I'm struggling with, is "Add no of bookings".

[tyson9699]

Thank you all for trying to help me out, I have finally figured out to do everything. I'm closing this thread here.

[Micron]

Looks to me like you want "Assign to course" to append member and schedule to bookings and that works. Not sure why you need the other append query. You don't want another record for the course because there is only one course (course is an entity). Nor do you want another record in course schedule (?) as that represents 1 unique course being conducted once even if that is a period of dates. You don't take 10 math classes and consider it 10 courses, no?

With your schema, to figure out how many attendees you have registered for kbx7 just DCount the appropriate course schedule (e.g. KBX7 - or is it KBX-1?). Research DCount with criteria.
Currently you have no form to attempt to add bookings so there is no easy way to prevent wanton additions regardless of whether or not you're below the capacity for a room. Nor can you prevent adding the same member twice or more for the same course as a result.

Look at table course schedule in design view and note the Description column. In absence of descriptive and unambiguous field names some details there would help anyone reviewing your design. I have no idea if No of bookings is your stored value for the current number of planned attendees or if that's the max for the room. Thus I can't advise further on how to determine the current enrollment for a course. Perhaps DLookup on the field containing the max allowed if that's what your Course Schedule.No of bookings field is for. Interesting that beyond saying that your form code (or macros if that's what you're using) needs to compare the max allowed against the DSum result.

You really should review the other thread on this project. There is a lot there about names and design suggestions for this and I don't want to repeat them. You are making the same less than perfect design decisions. Not sure but I think having manufactured record id's (e.g. KBX-1) is a potential problem too.

Note that you won't be able to maintain control as noted above without a form. Then a properly designed form will require you to redesign your append query.
Maybe look here for "scheduling"
http://www.databaseanswers.org/data_...all_models.htm

or specifically
http://www.databaseanswers.org/data_...ling/index.htm

You really should review the normalization link (other thread) and should consider the concept of junction tables as are shown in the second link.

[Micron]

Thank you all for trying to help me out, I have finally figured out to do everything. I'm closing this thread here.

Hmm. I guess it doesn't pay to write a book for an answer.
Hope you get good marks for your project.

[tyson9699]

Hmm. I guess it doesn't pay to write a book for an answer.
Hope you get good marks for your project.

Holy cow thank you so much for your effort Micron, you are a legend

[ssanfu]

Some suggestions re:Naming objects
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not begin an object name with a number.

---------------------------------------------------------------------
Field name: "No of bookings"
Is their a field: "Yes of bookings" ??

Better would be "Num of bookings" (but without the spaces)
Even Better: "NumOfBookings" or "Num_Of_Bookings"

---------------------------------------------------------------------



AutoNumber
----------------
Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key

Every table I design has an Autonumber primary key field. The Primary Key field name has "_PK" as a suffix. A Foreign Key field name has "_FK" as a suffix.


Use a Meaningless Field for the Key Field

Microsoft Access Tables: Primary Key Tips and Techniques

You have a Text field as the PK field in table "Course Schedule" (BTW - you should avoid spaces in ALL object names)

[tyson9699]

Some suggestions re:Naming objects
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not begin an object name with a number.

---------------------------------------------------------------------
Field name: "No of bookings"
Is their a field: "Yes of bookings" ??

Better would be "Num of bookings" (but without the spaces)
Even Better: "NumOfBookings" or "Num_Of_Bookings"

---------------------------------------------------------------------



AutoNumber
----------------
Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key

Every table I design has an Autonumber primary key field. The Primary Key field name has "_PK" as a suffix. A Foreign Key field name has "_FK" as a suffix.


Use a Meaningless Field for the Key Field

Microsoft Access Tables: Primary Key Tips and Techniques

You have a Text field as the PK field in table "Course Schedule" (BTW - you should avoid spaces in ALL object names)

Thank you for your suggestions. I've got into Access for like less than 24 hours so I sure make a lot of mistakes xD. Will keep your notes in mind the next time I make a database

[Micron]

Thank you for your suggestions.

All covered in the other thread IIRC.