Help With AS Database (MS Access)
I have to make a database booking system for my coursework, however my teacher (I'm in a comprehensive and do ICT in a grammar cause my school does not offer it) is unwilling to help me, or any the other 3 girls who come from the comprehensive when we have problems.
Here are a few of my questions
I have a look up wizard menu that displays different bands available at different prices
How do I make these prices appear in my "Total" field near the end?
It says it can't connect with a drop down menu but I saw it was able to in an example?
Also, with the drop down menu, is there a way I can allow them to click multiple options, instead of just one from the list?
And finally how do I set the date to today's, where it changes automatically next day I use it?
It's for my date of booking, and i've tried =Date(), =Now(), =TodaysDate()
and none automatically change the day after
Any help is appreciated <3
EDIT:
I only found out today I should have a one-to-many relationship in nmy database, but I only have two tables. I'm doing a booking system for a selection of three bands.
Do you think it would be better if I changed my booking system to a Collectibles delivery system(that sells things like posters, figures, keychains from games) or do you think I should try to get the band to work?
Here are a few of my questions
I have a look up wizard menu that displays different bands available at different prices
How do I make these prices appear in my "Total" field near the end?
It says it can't connect with a drop down menu but I saw it was able to in an example?
Also, with the drop down menu, is there a way I can allow them to click multiple options, instead of just one from the list?
And finally how do I set the date to today's, where it changes automatically next day I use it?
It's for my date of booking, and i've tried =Date(), =Now(), =TodaysDate()
and none automatically change the day after
Any help is appreciated <3
EDIT:
I only found out today I should have a one-to-many relationship in nmy database, but I only have two tables. I'm doing a booking system for a selection of three bands.
Do you think it would be better if I changed my booking system to a Collectibles delivery system(that sells things like posters, figures, keychains from games) or do you think I should try to get the band to work?
Hi, firstly is this on a form or a report?
I'm guessing with you mentioning a drop down you mean a form for all your questions.
I can't remember exactly as it's been a while since I've touched Access, but you should be able to add a text field and in it's properties in the control source something like field1+field2+field3. But I'm not 100% sure, so Google should help you tweak it. If that fails look into using a little VisualBasic to declare a variable and then use that variable to populate the text field (can't remember if Access lets a label be changed with VBA - possibly, which would make it print nicer)
Multiple selections in a dropdown box aren't possible, so redesign your form to use a Combobox which will allow you to select multiple values.
With regards to the date, I honestly can't remember, but that looks correct. Ensure that it's also like that in the control source in the properties, if that fails look into VBA or a query for it.
If you need at least 1 one-to-many relationship, post up exactly what your database is meant to do and it's current relationship structure and I'm sure you could get feedback on what to add/change to meet your requirement.
I'm guessing with you mentioning a drop down you mean a form for all your questions.
I can't remember exactly as it's been a while since I've touched Access, but you should be able to add a text field and in it's properties in the control source something like field1+field2+field3. But I'm not 100% sure, so Google should help you tweak it. If that fails look into using a little VisualBasic to declare a variable and then use that variable to populate the text field (can't remember if Access lets a label be changed with VBA - possibly, which would make it print nicer)
Multiple selections in a dropdown box aren't possible, so redesign your form to use a Combobox which will allow you to select multiple values.
With regards to the date, I honestly can't remember, but that looks correct. Ensure that it's also like that in the control source in the properties, if that fails look into VBA or a query for it.
If you need at least 1 one-to-many relationship, post up exactly what your database is meant to do and it's current relationship structure and I'm sure you could get feedback on what to add/change to meet your requirement.
Original post by Adam151192
Hi, firstly is this on a form or a report?
I'm guessing with you mentioning a drop down you mean a form for all your questions.
I can't remember exactly as it's been a while since I've touched Access, but you should be able to add a text field and in it's properties in the control source something like field1+field2+field3. But I'm not 100% sure, so Google should help you tweak it. If that fails look into using a little VisualBasic to declare a variable and then use that variable to populate the text field (can't remember if Access lets a label be changed with VBA - possibly, which would make it print nicer)
Multiple selections in a dropdown box aren't possible, so redesign your form to use a Combobox which will allow you to select multiple values.
With regards to the date, I honestly can't remember, but that looks correct. Ensure that it's also like that in the control source in the properties, if that fails look into VBA or a query for it.
If you need at least 1 one-to-many relationship, post up exactly what your database is meant to do and it's current relationship structure and I'm sure you could get feedback on what to add/change to meet your requirement.
I'm guessing with you mentioning a drop down you mean a form for all your questions.
I can't remember exactly as it's been a while since I've touched Access, but you should be able to add a text field and in it's properties in the control source something like field1+field2+field3. But I'm not 100% sure, so Google should help you tweak it. If that fails look into using a little VisualBasic to declare a variable and then use that variable to populate the text field (can't remember if Access lets a label be changed with VBA - possibly, which would make it print nicer)
Multiple selections in a dropdown box aren't possible, so redesign your form to use a Combobox which will allow you to select multiple values.
With regards to the date, I honestly can't remember, but that looks correct. Ensure that it's also like that in the control source in the properties, if that fails look into VBA or a query for it.
If you need at least 1 one-to-many relationship, post up exactly what your database is meant to do and it's current relationship structure and I'm sure you could get feedback on what to add/change to meet your requirement.
For clarification I will put all the table data up, but i'll highlight the fields i'm actually having trouble with <3
I have two tables one with the customer details including:
Customer ID: Autonumber
Title: Look up wizard (Mr, Mrs etc)
Forname:
Surname:
D.O.B: Date/Time
Address:
Postcode:
City:
Phone Number:
Mobile Number:
Email address:
Notes: Memo
The second is a booking table which includes:
Booking ID: Autonumber
Band Name: (I'm offering 3 different bands)
Event Type: (wedding, birthday, public holiday ETC. These are set at different prices)
Time Slot: I tried to have a start and an end time, but my teacher wouldn't help me with the vaildation that avoids double booking /: so it's a look up wizard of different two hour slots
Date of booking:
Date of Event: >=DateAdd("d",21,Date()) and <=DateAdd("m",6,Date())
Opening Band: (Boolean) It's an addition charge, one the reasons I need to be able to add selected things in Drop down menus
Merchandise Included: Boolean
Type Of Merchandise: A Look up wizard of different things like wrist bands and t shirts at different prices per unit
These two tables are linked by a Customer Key in both, it is a Relationship with enforced integrity
Problem = I need these drop down menus to be able to have a sum function that adds the selected value together
E.G. Event Type: Wedding selected - £120
Opening Band: Yes selected - £30
Type of Merchandise - 3 Shirts at £10.00 each
50 Wrist bands at £2.00 each
1 Novelty Bear Mug at £5.00
I need a way to add these all together to display the proper price on the Customer form, but when I try it says can't use values in Look up Menu or something. Is it that i'm only able to do it in the form/report? Cause i've been trying to sort it in the table >.<
My boolean options are set to yes or no, but when its actually clicked it comes up -1 or 0. I think -1 is Yes and 0 is no, but for a hypothetical customer this would be confusing. How do i make it show?
And about the combo list, I changed it to combo list in the data design, but it still didn't let me select more than one, is that also something that needs done in the form?
I think you have over simplified your system if you have only 2 tables with a 1-M relationship. To make sure there are no double booking you don't use validation - you should use a composite key made up of fields such as what you are booking, the date, and time. That way there can be no repeat of these combinations and therefore no double booking. I'm just looking at one I created a few years ago and I have 6 tables to achieve all this.
Booking systems are a pain and I always tell my students to avoid them. You have to be very careful you get the logic right, or it will not work. If you want my advice I'd do something different.
Here's some notes I made at the time - I've not had time to check it all, but I think it is OK.
Tables Needed
Resource Table
This is a table that lists all the resources that you are intending to book/schedule.
e.g. room/court/person/vehicle.
Time Table This table lists the “booking slots”
e.g. 09:00 – 10:00
Date Table A table that lists a range of valid dates
Customer Table This table lists the customers or users of the resources.
Assume 1 customer using 1 resource at any particular time.
Booking Table The table that brings all the above together to create a list of unique bookings.
Relationships
A Time can be used many times in the Booking Table
A particular booking can only have one time in it.
1 TO MANY
A Date can be used many times in the Booking Table.
A particular booking can only have one date in it.
1 TO MANY
A Customer can be used many times in the Booking Table.
A particular booking can only have one customer in it.
1 TO MANY
A Resource can be used many times in the Booking Table.
A particular booking can only have one Resource in it.
1 TO MANY
i.e. Each Booking contains
A booking no
Date of booking
Time of booking
Resource to be booked
Customer making booking
Approach
1) Set up the tables listed above adding any relevant fields to the tables. Ensure you select an appropriate primary key. In the case of the “Booking” table, the items that make all bookings unique are a combination of date, time and resource, so these must make up the primary key.
2) Add any other further tables which are particular to your application. E.g. in a sports club you may want to have membership type as a further table.
3) Go through all fields in all tables and make into Lookup Lists if the source data is located in another table.
4) Set up the relationships.
5) Put some test data into all tables apart from the bookings table.
6) Create a form to enter the bookings. If you have set up all the lookup lists then you should be able to select all the different options to make a booking. You will receive an error message if you attempt to double book any resource.
7) Buttons you could add to this form
a. Add new booking
b. Check Availability (Runs a query on the date and resource you selected)
c. Save Booking
d. Search for customers
Booking systems are a pain and I always tell my students to avoid them. You have to be very careful you get the logic right, or it will not work. If you want my advice I'd do something different.
Here's some notes I made at the time - I've not had time to check it all, but I think it is OK.
Tables Needed
Resource Table
This is a table that lists all the resources that you are intending to book/schedule.
e.g. room/court/person/vehicle.
Time Table This table lists the “booking slots”
e.g. 09:00 – 10:00
Date Table A table that lists a range of valid dates
Customer Table This table lists the customers or users of the resources.
Assume 1 customer using 1 resource at any particular time.
Booking Table The table that brings all the above together to create a list of unique bookings.
Relationships
A Time can be used many times in the Booking Table
A particular booking can only have one time in it.
1 TO MANY
A Date can be used many times in the Booking Table.
A particular booking can only have one date in it.
1 TO MANY
A Customer can be used many times in the Booking Table.
A particular booking can only have one customer in it.
1 TO MANY
A Resource can be used many times in the Booking Table.
A particular booking can only have one Resource in it.
1 TO MANY
i.e. Each Booking contains
A booking no
Date of booking
Time of booking
Resource to be booked
Customer making booking
Approach
1) Set up the tables listed above adding any relevant fields to the tables. Ensure you select an appropriate primary key. In the case of the “Booking” table, the items that make all bookings unique are a combination of date, time and resource, so these must make up the primary key.
2) Add any other further tables which are particular to your application. E.g. in a sports club you may want to have membership type as a further table.
3) Go through all fields in all tables and make into Lookup Lists if the source data is located in another table.
4) Set up the relationships.
5) Put some test data into all tables apart from the bookings table.
6) Create a form to enter the bookings. If you have set up all the lookup lists then you should be able to select all the different options to make a booking. You will receive an error message if you attempt to double book any resource.
7) Buttons you could add to this form
a. Add new booking
b. Check Availability (Runs a query on the date and resource you selected)
c. Save Booking
d. Search for customers
Original post by marthamonkey
Okay, so i'm going to remake my tables and i'm currently planning it out
I have so far 5 tables (the booking, time, date, customer, Resource(band)) and i'm considering adding a merchandise table but i'm unsure whether that should be in the band table or one by itself with only 6 fields?
Do I need to set a primary key to the fields I want to link in the relationships? I mean in each table, like my customer ID would be a primary key, my booking ID would be one, the time would be one
Or is there only meant to be ONE primary key?
And i'm wondering if this will work:
For example, in the times table, i put in the times slots in field1, and prices for each slot in a different field
If i link the time slot to the booking table, will the price also show up or do I need to link that too?
Original post by CaityRaindrop
Okay, so i'm going to remake my tables and i'm currently planning it out
I have so far 5 tables (the booking, time, date, customer, Resource(band)) and i'm considering adding a merchandise table but i'm unsure whether that should be in the band table or one by itself with only 6 fields?
I have so far 5 tables (the booking, time, date, customer, Resource(band)) and i'm considering adding a merchandise table but i'm unsure whether that should be in the band table or one by itself with only 6 fields?
To me this sounds really dirty. You really don't want to have a time table and a date table - you'd need to keep adding future dates to it, and how does it handle overlapping times? (hint: it doesn't - if X books between 4pm and 6pm and Y books between 5pm and 7pm this won't detect this clash). A better way to do it is to have granularity of 1hr slots - when you enter a range of times it creates a number of rows in a date_time table specifying which hour slots are now unavailable.
The trouble is that, while a better solution, this is now getting complicated and is starting to involve programming. I guess it depends how reliable your system actually needs to be.
Booking systems are nasty when you have variable length appointments with no restriction on possible start times.
Do I need to set a primary key to the fields I want to link in the relationships? I mean in each table, like my customer ID would be a primary key, my booking ID would be one, the time would be one
Or is there only meant to be ONE primary key?
Or is there only meant to be ONE primary key?
You can have as many primary keys as you like in a table - to put it simply, the primary keys should the minimum number of fields able uniquely identify a piece of data. If you have many keys they're jointly called a composite key. If you have a field from another table as part of a composite key (or indeed, a primary key itself) it's a foreign key.
I agree a date table is nasty and will require constant maintenance, but in this particular case the student wanted certain dates to be unavailable like bank holidays, so the date table excluded these. If you can suggest another way of achieving this I'd be interested to hear it. My doctors will only let us book 1 month in advance, so I suspect they have a similar system were available dates are put on the system every month.
The timeslots were particular to the particular system where rooms were booked for set timeslots.
I think for the purpose of A level coursework you can't account for every variation and have to make some assumptions and simplifications.
There are probably lots of better ways of doing this, but this way worked, and didn't involve any programming.
The timeslots were particular to the particular system where rooms were booked for set timeslots.
I think for the purpose of A level coursework you can't account for every variation and have to make some assumptions and simplifications.
There are probably lots of better ways of doing this, but this way worked, and didn't involve any programming.
Original post by marthamonkey
I agree a date table is nasty and will require constant maintenance, but in this particular case the student wanted certain dates to be unavailable like bank holidays, so the date table excluded these. If you can suggest another way of achieving this I'd be interested to hear it. My doctors will only let us book 1 month in advance, so I suspect they have a similar system were available dates are put on the system every month.
The timeslots were particular to the particular system where rooms were booked for set timeslots.
I think for the purpose of A level coursework you can't account for every variation and have to make some assumptions and simplifications.
There are probably lots of better ways of doing this, but this way worked, and didn't involve any programming.
The timeslots were particular to the particular system where rooms were booked for set timeslots.
I think for the purpose of A level coursework you can't account for every variation and have to make some assumptions and simplifications.
There are probably lots of better ways of doing this, but this way worked, and didn't involve any programming.
Yeah I guess if you have invariant timeslots and don't want any programming then that solution works.
My suggestion would be an exclusion set rather than inclusion i.e. assume all dates are available and then block out the ones you don't want. This would be functionally the same as booking an entire day out for all doctors. An even better way is to have a separate table for specifying date and date-doctor (for the example of your doctors) exclusions.
It's not an easy task though and my suggestions are based on a decent knowledge of VBA and SQL.
The fundamentally difficult problem here is the complete lack of structure to appointment times. One would need to check the intersection of a date range - undoubtedly possible (indeed, I can think of 2 potential ways already) but possibly a bit fiddly and definitely needing VBA and SQL.
well, if i do put my date and time in a table, it would be the booking table with the validation:
Date: >=DateAdd("d",14,Date()) And <=DateAdd("m", 6, Date())
(more than two weeks away, but no more than 6 months)
And for time A look up wizard of hourly slots would be best?
I already know the VBA coding to make special offers and such on certain days (e.g. If Me.Day_of_event etc etc)
How do I ensure then that the dates CAN NOT be double booked?
Date: >=DateAdd("d",14,Date()) And <=DateAdd("m", 6, Date())
(more than two weeks away, but no more than 6 months)
And for time A look up wizard of hourly slots would be best?
I already know the VBA coding to make special offers and such on certain days (e.g. If Me.Day_of_event etc etc)
How do I ensure then that the dates CAN NOT be double booked?
Quick Reply
Related discussions
- About MS Access/MySQL
- Cancelled car insurance
- Helpful Utility to Move PST to Office 365
- Not allowed to download from Westlaw
- How to Recover and Convert OST Files
- Journal Recommendation
- [Request] Need Assistance Accessing an IbisWorld Report for University Project
- ICE's "Eye of God" program
- need help on bonhoeffer :( !!!
- Help from a current student
- Literature review
- Unit 2 creating systems to manage information
- Sending a PN1 form
- PCDA with cat 6 CIFAS
- Tuition Loan for Second Degree in STEM
- Essay Competition Database
- Uni materials
- Access to HE Midwifery - UNIT 4 Question 3. Hubert Essay
- How to mark STEM subject Alevel papers fast
- University of Westminster or Sheffield Hallam university
Latest
Trending
Last reply 2 days ago
Is MathsFM French Alevels good for a CS degree at top uni (OX, UCL, Durham etc)Last reply 4 days ago
OCR A-level Computer Science Paper 1 (H446/01) - 12th June 2023 [Exam Chat]Last reply 2 weeks ago
OCR A-level Computer Science Paper 2 (H446/02) - 19th June 2023 [Exam Chat]Last reply 3 weeks ago
AQA A-level Computer Science Paper 1 (7517 1A - 1E) - 12th June 2023 [Exam Chat]Last reply 1 month ago
Not receiving any response from University of Birmingham.Last reply 3 months ago
OCR GCSE Computer Science Paper 1 (J277/01) - 19th May 2023 [Exam Chat]Last reply 3 months ago
AQA GCSE Computer Science Paper 2 (8525/2) - 25th May 2023 [Exam Chat]Trending
Last reply 2 days ago
Is MathsFM French Alevels good for a CS degree at top uni (OX, UCL, Durham etc)Last reply 4 days ago
OCR A-level Computer Science Paper 1 (H446/01) - 12th June 2023 [Exam Chat]Last reply 2 weeks ago
OCR A-level Computer Science Paper 2 (H446/02) - 19th June 2023 [Exam Chat]Last reply 3 weeks ago
AQA A-level Computer Science Paper 1 (7517 1A - 1E) - 12th June 2023 [Exam Chat]Last reply 1 month ago
Not receiving any response from University of Birmingham.Last reply 3 months ago
OCR GCSE Computer Science Paper 1 (J277/01) - 19th May 2023 [Exam Chat]Last reply 3 months ago
AQA GCSE Computer Science Paper 2 (8525/2) - 25th May 2023 [Exam Chat]
