How can we use if functions in Access

[Hifty]

So what i'm trying to do for my school work is to allow number of bookings for a specific ScheduleID to update and increase every time a new booking for that scheduleID is added.

Image 1 Image 2

Is there a simple way of doing it in the design view?

 

[Micron]

Let's do a design check. I gather from your relationships that:
A course ID (1) named Science1 could be put on a schedule, which may have 3 attendees signed up (3 bookings) and the bookings have personal details such as name, DOB, etc. A room needs to be allocated, but is it actually related to "Science1" (the course) which is what you have, or is it related to the course schedule? Might there not be 2 "Science1" courses running at the same time but in different rooms? Perhaps Room is related to the schedule, or it might even be a "resource" and perhaps there is not really a need for a separate "equipment" table, just a resource table (I don't know which are requirements of the project). IMO a room is not equipment so at the very least room doesn't belong there since there is a table for room data. Equipment, if it is to remain, seems more related to the things that one needs to teach the course, thus Room doesn't belong in Equipment when it's already in Room.

One of the more difficult aspects of db normalization is knowing what the attributes (fields) of the entity (table) are and which is which. You are an entity, having attributes of height, weight, gender, name, etc. How much money you have in your wallet is not an attribute of you. These things require a good deal of insight as to what process or business the db is supposed to support. Perhaps a quick review of the following will help with normalization and some other things I've touched on (e.g naming conventions, which I see you have already made great progress on). Don't know how much time or interest you have so I present it all at the end here for your choosing.

the capacity for row of scheduleID 1 as an example will show '64'

I cannot see the entire query result and check if that's the count of 1's or not. Perhaps you get 64 because you've applied no criteria. Perhaps it on the criteria row it ought to be =Forms!frmNameOfFormHere.NameOfControlHere in the Schedule field where that control contains the scheduleID. That might depend on all the foregoing (which is a lot for you to digest, no doubt) - meaning if you alter table design and relationships, don't take that suggestion verbatim if it no longer applies.

You probably don't need a query to retrieve the current booking level. More like a domain aggregate function such (research "DSum with criteria") to limit the summing to a particular scheduleID. Research domain aggregate functions with a nod to DSum if you have to add a bunch of 1's for each attendee. If you have the value already (which I thought you were required to have) you only need to look it up - see "DLookup with criteria". Either way, I've reviewed your postings and I cannot see that you have stored the number (e.g.8) as the current number of attendees for a particular course but you did say it was a requirement. All I see now is a bunch of 1's in your latest images.

You might find something here under the topic of "students" that will help (or there may be valid schemas under a different topic)

http://www.databaseanswers.org/data_models/index_all_models.htm

The whole enchilada - pick and choose based on needs and time:

Normalization Parts I, II, III, IV, and V <<<primary reading

What Is Normalization, Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

and/or

Database Normalization – Holowczak.com Tutorials

holowczak.com

Entity-Relationship Diagramming: Part I, II, III and IV

Entity-Relationship Diagramming: Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

How do I Create an Application in Microsoft Access?

How do I Create an Application in Microsoft Access?

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

Important for success:
Naming conventions - General: Commonly used naming conventions

MS Access Naming Conventions

_________________________________________________________ Naming Conventions. This document is aimed at the user who is unfamiliar with any naming convention. It is based upon personal experience and the interaction I have had with others. It is not a hard and fast rule. Why use a...

www.access-programmers.co.uk

What not to use in names
- Microsoft Access tips: Problem names and reserved words in Access

About Auto Numbers
- UtterAccess.com
- General: Use Autonumbers properly

The evils of lookup fields - The Access Web - The Evils of Lookup Fields in Tables
Table and PK design tips - Microsoft Access Tables, Primary Key Tips and Techniques
About calculated table fields - Microsoft Access tips: Calculated Fields
About Multi Value Fields - Multivalued Fields - Mendip Data Systems

 

[Hifty]

Ok, so I had noticed that the whole query where I had 1s was almost completely pointless, when i could just add +1 to the existing number of bookings. However I dont know how to specify for which scheduleID I want the number of bkings to added to and hw to make my button both and a new booking to the tblbookings and how to add the +1 to number_of_bookings in tblcourseschedule. Is it poossible for you guys to use discord? I think it would be easier for me to understand what is going on. Because it feels like at this point im going in circles around myself. (BOOBA#5654 is my username)

 

[Micron]

I don't think it's possible to guide you much further, mainly because I don't know the limitations that have been imposed. I began to write a code sample on how one might solve this but soon realized I'd need the form to have course info/id on it and you don't have it. That's because tblRoom is linked to course which seems wrong to me as I've said. Perhaps that is a requirement. If a course will be taught in room A sometimes and room B other times, in order to get the roomID into tblCourse 2x you will have to repeat every other course attribute in the second record and that makes no sense to me.

IMO you need to get Room records onto your form somehow - either some convoluted query (which can leave you in a state where you will not be able to add or modify a record) or make the proper linkage and include room info on the form. Another approach is a form/subform design where the main form contains parent details (e.g. Purchase Order) and the subform contains child details (line items on the PO) but I fear that might be another can of worms and might not even be allowed in your case.

BTW, you don't show users ID fields when you have finished developing as they have no meaning to them. For combos, the rowsource (sql statement or query) should have the id field first then the field name(s) that you want to see in the combo list as columns. You set the first column width (ID) to 0 and the other(s) as appropriate. Textboxes for ID get hidden before db issue.

HTH

 

[Hifty]

I don't think it's possible to guide you much further, mainly because I don't know the limitations that have been imposed. I began to write a code sample on how one might solve this but soon realized I'd need the form to have course info/id on it and you don't have it. That's because tblRoom is linked to course which seems wrong to me as I've said. Perhaps that is a requirement. If a course will be taught in room A sometimes and room B other times, in order to get the roomID into tblCourse 2x you will have to repeat every other course attribute in the second record and that makes no sense to me.

IMO you need to get Room records onto your form somehow - either some convoluted query (which can leave you in a state where you will not be able to add or modify a record) or make the proper linkage and include room info on the form. Another approach is a form/subform design where the main form contains parent details (e.g. Purchase Order) and the subform contains child details (line items on the PO) but I fear that might be another can of worms and might not even be allowed in your case.

BTW, you don't show users ID fields when you have finished developing as they have no meaning to them. For combos, the rowsource (sql statement or query) should have the id field first then the field name(s) that you want to see in the combo list as columns. You set the first column width (ID) to 0 and the other(s) as appropriate. Textboxes for ID get hidden before db issue.

HTH

Hey Micron, thanks a lot for all of your help, I have learned a lot more here than in my course, here is an update video on what i have done so far, basically completing what is required of us, i think, the instructions are vague haha. Update Video

 

[Micron]

I composed this before finishing so didn't realize you have completed your work but I'll post it anyway.
-you can't put that kind of validation in a table. Do it in the form - perhaps in the form BeforeUpdate event code, which is what I began to write as previously stated but had to stop for reasons explained:
perhaps make a query that returns the capacity for a room used in the booking
(e.g. qryRoomCap) Not sure what criteriaGoesHere** will be but it is used to find the capacity only for the room related to the booking. If "Capacity" is not the name of your query field, use the name you have.

Dim lngCap As Long

lngCap = DLookup("Capacity","qryCap","criteriaGoesHere**")
If Me.formFieldNameHere + 1 > lngCap Then
  Msgbox "Cannot make this booking; room already at capacity limit)
  Cancel = True
  Me.Undo
End If

PS - advise to not use Large Number data type going forward, use long or integer for most cases. Anyone with some older Access versions will not be able to open your file because of it - not sure how far back. Large Number is a descriptor for BigInt data type, which uses at least 2x the space IIRC. It's for numbers in the range of -2^63 to 2^63-1