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?

 

[JonXL]

They're the same image...?

Anyway, Access uses IIF() for if/then constructions. But I don't really understand what you're trying to do so I can't give more details on how to apply it to your specific case (or even if it's the best option).

 

[Micron]

I for one don't see any difference in the 2 pics, don't get what it has to do with using IF in Access (the equivalent to Excel being IIF in Access) nor what you are asking for. If you add a booking record it gets added to the set of records automatically?

 

[Hifty]

Hey guys, sorry for the error in the image links and for my lacking explanation of what i need. So I uploaded a 3 minute video on what i need help with. Thanks for understanding. Its difficult for me to formulate a question about what I need in words. Here is a video if this helps: The Video

They're the same image...?

Anyway, Access uses IIF() for if/then constructions. But I don't really understand what you're trying to do so I can't give more details on how to apply it to your specific case (or even if it's the best option).

 

[Micron]

It's unfortunate that you can't alter much because any experienced db developer will tell you that you DON'T store calculations in tables when the calculation can be done in a form, query or report. Here's a link to why you don't in 99.9% of cases which I include here only to show you the right way so that you can later forget what you're being taught. Make sure you read at least the last paragraph. Note that the author is a much respected former developer whom many consider to be an authority on these things. You'd do well to peruse his site if you will continue to use Access.

When you are creating the booking you will have to run an UPDATE query to add one to the value - probably by using the combo or form BeforeUpdate event because you can cancel this event if it doesn't pass validation rules. AfterUpdate is not the time to enforce such validation as the record will already be committed. However, you will have to use this event to check that the current booking total + the number to be added by the combo (I presume it is 1)<= the room capacity value.

If you can do this with a query by joining the booking and room data tables and specify criteria like <= I don't know because I would not be storing the current booking value anyway. I suppose the logic there is that the criteria might be
IIF(tblBooking.Booked <= tblRooms.Capacity, 1,0) or just
IIF(tblBooking.Booked <= tblRooms.Capacity)
- again, something I've never done and never will so I'm not sure.

Be aware that if your form combos are bound to a table and you alter chosen values, you will over-write what is already there. To avoid this you have to use unbound controls but I don't think you need to worry about it for this assignment. Then again, according to the article link, doing so just might cause invalid data entries due to the design decision.

 

[Hifty]

It's unfortunate that you can't alter much because any experienced db developer will tell you that you DON'T store calculations in tables when the calculation can be done in a form, query or report. Here's a link to why you don't in 99.9% of cases which I include here only to show you the right way so that you can later forget what you're being taught. Make sure you read at least the last paragraph. Note that the author is a much respected former developer whom many consider to be an authority on these things. You'd do well to peruse his site if you will continue to use Access.

When you are creating the booking you will have to run an UPDATE query to add one to the value - probably by using the combo or form BeforeUpdate event because you can cancel this event if it doesn't pass validation rules. AfterUpdate is not the time to enforce such validation as the record will already be committed. However, you will have to use this event to check that the current booking total + the number to be added by the combo (I presume it is 1)<= the room capacity value.

If you can do this with a query by joining the booking and room data tables and specify criteria like <= I don't know because I would not be storing the current booking value anyway. I suppose the logic there is that the criteria might be
IIF(tblBooking.Booked <= tblRooms.Capacity, 1,0) or just
IIF(tblBooking.Booked <= tblRooms.Capacity)
- again, something I've never done and never will so I'm not sure.

Be aware that if your form combos are bound to a table and you alter chosen values, you will over-write what is already there. To avoid this you have to use unbound controls but I don't think you need to worry about it for this assignment. Then again, according to the article link, doing so just might cause invalid data entries due to the design decision.

Thanks for recommending this author, Micron, the thing is, most of our course is just "google it" and "trial and error" so there is not much guidance on how to do what. I appreciate your recommendation and I will try and implement it tomorrow morning. I will let you know if I get anything done. Thanks a lot.

 

[jackd]

Further to micron's comments:
-can you show us the requirements statement(s) for this database/application?
-can you post a graphic showing your tables and relationships?

User interaction is usually/always via Forms - NOT Queries and Tables.
Calculations are done via query and not stored in tables.

...the thing is, most of our course is just "google it" and "trial and error" so there is not much guidance on how to do what.

Are you paying for this training/course?
See this link for a series of articles on Database Design and Planning and more.

 

[Hifty]

Are you paying for this training/course?
See this link for a series of articles on Database Design and Planning and more.

No it's a database class in our university, most of it is theoretical basics about ERP and things along those lines and a little bit of sql. We have had no practical application of our knowledge until this project, and everything we have learned theoretically is not applying nicely to this project.

Further to micron's comments:
-can you show us the requirements statement(s) for this database/application?
-can you post a graphic showing your tables and relationships?

Yes, here they are, (P.S: "Soest" is a city, and I believe price per month for a gym course is pointless if the gym has membership ranks, where we could do it as each rank has access to different courses. But the teacher (not professor) is asking us to include a price fr each course.) (Also, we wanted addresses but the teacher also wanted city seperately so that we could return all members frmo the city of soest). I want to make a good and practical database, but I also want a good grade, so i should follow teacher's orders.

 

[Micron]

Ignore anything I say that goes against the requirements.
I'd suggest you
- get in the habit of CourseID_PK or CourseIDpk or just CourseID but the related table is CourseIDfk or CourseID_Fk or similar. When you look at a query design it's likely you'll struggle with which is the numeric id and which holds the value. It's also good for those who seek to help out and have no knowledge of the design.

- I and many others follow some sort of naming form. tblMembers, not Members; or perhaps tMembers (not my style) so as to never mix up fields vs table names.

- you should not have Instructor Name in tblCourses - only EmployeeID. However, what guarantee is there that all Employees are Instructors? Probably none, so the field name is problematic. If anything, Employee should have an EmplTypeID field and a related table for employee types. If that's too much normalization, then at least a field in Employee for type. That is the less preferred approach because an employee could potentially hold multiple roles.

-do not have spaces in ANY object names, and no special characters (save for underscore). Never start a object name wit a number.

-don't use reserved words for ANY object name. Name is a reserved word.
-not a good idea to join a pk and fk field with dissimilar names (Room and Room Number). RoomID and RoomIDfk (or again, a variation) is much better.

re your comment about addresses - at least he/she got that right. City distinct from street address, postal code, State/Province etc.

Mainly, the approach is in post 5. You'd need to
- retrieve the current booking level for a course and compare it to the capacity + 1 (or whatever) and allow the added record and increase the booking number if it passes validation. I don't see where you've specified how that must be done so it seems to be an open question. If the requirements are that you use IIF or use queries only, then that's what you have to do. If not, consider a code solution.

 

[Hifty]

Ignore anything I say that goes against the requirements.
I'd suggest you
- get in the habit of CourseID_PK or CourseIDpk or just CourseID but the related table is CourseIDfk or CourseID_Fk or similar. When you look at a query design it's likely you'll struggle with which is the numeric id and which holds the value. It's also good for those who seek to help out and have no knowledge of the design.

- I and many others follow some sort of naming form. tblMembers, not Members; or perhaps tMembers (not my style) so as to never mix up fields vs table names.

- you should not have Instructor Name in tblCourses - only EmployeeID. However, what guarantee is there that all Employees are Instructors? Probably none, so the field name is problematic. If anything, Employee should have an EmplTypeID field and a related table for employee types. If that's too much normalization, then at least a field in Employee for type. That is the less preferred approach because an employee could potentially hold multiple roles.

-do not have spaces in ANY object names, and no special characters (save for underscore). Never start a object name wit a number.

-don't use reserved words for ANY object name. Name is a reserved word.
-not a good idea to join a pk and fk field with dissimilar names (Room and Room Number). RoomID and RoomIDfk (or again, a variation) is much better.

re your comment about addresses - at least he/she got that right. City distinct from street address, postal code, State/Province etc.

Mainly, the approach is in post 5. You'd need to
- retrieve the current booking level for a course and compare it to the capacity + 1 (or whatever) and allow the added record and increase the booking number if it passes validation. I don't see where you've specified how that must be done so it seems to be an open question. If the requirements are that you use IIF or use queries only, then that's what you have to do. If not, consider a code solution.

Ok, so I have followed a lot of what you have said in regards to good practice and i have created a seperate employeetype table, however, I am having an issue with retrieving the booking level, I could get rid of BookingID in this query to group the scheduleIDs, but then the capacity for row of scheduleID 1 as an example will show '64' in the column capacity 8, i understand the issue, but I have no clue on how to solve it here. I want to be able to leave the capacity as the column heading and then see for ScheduleID 1, how many bookings are in the column capacity 8. Im playing around with it but I dont seem to be getting anywhere.