Relational Databases - Data not showing

[Student138]

hello

i have linked three tables together in order to create a relational database.

i have attached my file via this message

on my booking:table, i want to be able to select "customer_ID" from the drop down function. when this happens i would like it to display the customer details such as the name and skill level.
currently it is only showing the number. i linked the tables together assuming it will show the relevant data

i am unsure on how to do this

 

[moke123]

Tables are not for displaying data, forms, queries and reports are.
what you did was to create a table lookup field.
most developers avoid lookup fields in tables.
see this link...http://access.mvps.org/access/lookupfields.htm

 

[Student138]

thanks

I've created the form and sub form

from there how would I display the data, would it be something in the design view of the form ?

 

[Student138]

hello again

truth be told i am trying to solve this problem on a past paper .

i have attached screenshots, of the question and answer. the answer below the question show what they have done and the solution. problem is i don't know how to actually do it . could you please help ? :banghead:

 

[moke123]

truth be told i am trying to solve this problem on a past paper .

Unfortunately, I think your going to find that many members are going to be reluctant to do your homework for you. We will however help with specific questions provided you are doing the heavy lifting.

that said, the first thing you should do is google "normalization" and create your tables. The most important part of a relational database are normalized tables. Once you have a solid foundation then you can build your queries, forms and reports.

part of the problem with helping with homework is what the teacher expects from you. in the scenario you present the tables have lookup fields. I never use them and would never recommend using them. I would use lookup tables, not lookup fields. What would your teacher expect?

edit: BTW, if you look closely at the screenshots you may find the answer to your original question.

 

[Student138]

thanks for the response and your advice

just panicking at the moment as the practical exam is coming up

i will try to mimic the mark scheme and see what happens. however i tried before and didn't' understand why my table was not showing the details, and only showing numbers

 

[moke123]

this is one of the problems with lookup fields in tables. They are akin to a combobox in a field. Perhaps a clue? - look at the properties of a combobox, especially the column count and column widths.

I'm always suprised that a teacher would use them in a course. if you search the forums on lookup fields in tables, I'm sure you'll find plenty of examples of the problems they cause.

 

[Student138]

thank you so much for your reply

unfortunately we have to use the look up because it is on the syllabus regarding the exam

i have now completed most of the practical paper
i am trying to do the query in order to insert it in the subform, i can not figure out why it is not working on the form.
im wondering if it is the order of which one i put first. when you create sub forms you can insert what you need in terms of the query or table

also, looking at the mark scheme for the "instructor details query", they have put "[Forms]!" in the criteria, i understand that [Bookings]! will refer to the table. but i am unsure why they have put [Forms]

 

[jdraw]

If you have given an honest effort and are having difficulty, you might want to upload your evolving database as a zip file.

As moke123 has advised, most Access developers will not use lookup fields within tables, they will use lookup tables.

One of the regular posters provided this analogy to help understand "Forms":
Think of it (" a Form") like cameras on a football pitch. You don't have a different football game for every camera - you have one football game and view it from multiple viewpoints. In this way you can't have any inconsistency in the underlying information (the game) and everything is synced - imagine if you had to have 2 games and in the second game you told the players to copy the actions of the previous players. It would quickly divert. One set of information and multiple viewpoints.

Good luck with your database and exam.

 

[arnelgp]

lucky for you, i tolerate everything.

 

[Student138]

i have uploaded it . it is what i have done so far. i have tried but can not progress any further

If you have given an honest effort and are having difficulty, you might want to upload your evolving database as a zip file.

As moke123 has advised, most Access developers will not use lookup fields within tables, they will use lookup tables.

One of the regular posters provided this analogy to help understand "Forms":
Think of it (" a Form") like cameras on a football pitch. You don't have a different football game for every camera - you have one football game and view it from multiple viewpoints. In this way you can't have any inconsistency in the underlying information (the game) and everything is synced - imagine if you had to have 2 games and in the second game you told the players to copy the actions of the previous players. It would quickly divert. One set of information and multiple viewpoints.

Good luck with your database and exam.

 

[arnelgp]

another try please.
study there are codes on AfterUpdates
of the combos.

Study the query also.

 

[Student138]

thank you so much

I will have a look

another try please.
study there are codes on AfterUpdates
of the combos.

Study the query also.

 

[Student138]

HELLO again, i tried to study the query and combos. However they seem a little complicated, this method is not required for the syllabus . especially in the row source for instructor ID (combo box)
Looking at the answer i sent you, is there not an easier solution?
for example, They seem to have the IIF function done in the report

thank you so much

I will have a look

 

[Student138]

I don't understand why, that when i insert my query in the form wizard, it does not show. but with yours it does

HELLO again, i tried to study the query and combos. However they seem a little complicated, this method is not required for the syllabus . especially in the row source for instructor ID (combo box)
Looking at the answer i sent you, is there not an easier solution?
for example, They seem to have the IIF function done in the report

 

[Pat Hartman]

When you design a junction table (that is what Booking is generally called), you need to make the two foreign key fields (CustomerID and InstructorID) required. Since the purpose of this table is to link two other tables, it doesn't make sense for either of the relation fields to be empty.

If for some reason you want to allow one or both relation fields to be null, then your query will need to use left joins.

SELECT Booking.[Booking ID], Booking.Date, Booking.Time, Customer_details.[Customer ID], Customer_details.Forename, Customer_details.Surname, Customer_details.Age, Customer_details.Level, Instructor_details.[Instructor ID], Instructor_details.[Instructor name], Instructor_details.[Instructor level], IIf(IsNull([Customer_Details].[Age]),0,IIf([Customer_Details].[Age]<18,[Instructor_details].[Lesson price child],[Instructor_details].[Lesson price adult])) AS [Lesson Price]
FROM (Booking LEFT JOIN Customer_details ON Booking.[Customer ID] = Customer_details.[Customer ID]) LEFT JOIN Instructor_details ON Booking.[Instructor ID] = Instructor_details.[Instructor ID];

 

[Student138]

hello and thank you for taking the time to reply to me and writing the solution

some of the members have written about "null" and long formula in the criteria
there are some things that are not required for the exam. and i am trying to find a more simple solution. i am basically trying to work out what the examining board have done. looking at their mark scheme, they have gone a different route. their IIF function is in the report, not the query, they have done something less technical and i am trying to work out what it is.
im not trying to take a short cut, its just that the exam consists of using 5 different types of software, and we have 2.5 hours to complete it. so i don't think it needs to be that complex. complex for me not the members here

When you design a junction table (that is what Booking is generally called), you need to make the two foreign key fields (CustomerID and InstructorID) required. Since the purpose of this table is to link two other tables, it doesn't make sense for either of the relation fields to be empty.

If for some reason you want to allow one or both relation fields to be null, then your query will need to use left joins.

SELECT Booking.[Booking ID], Booking.Date, Booking.Time, Customer_details.[Customer ID], Customer_details.Forename, Customer_details.Surname, Customer_details.Age, Customer_details.Level, Instructor_details.[Instructor ID], Instructor_details.[Instructor name], Instructor_details.[Instructor level], IIf(IsNull([Customer_Details].[Age]),0,IIf([Customer_Details].[Age]<18,[Instructor_details].[Lesson price child],[Instructor_details].[Lesson price adult])) AS [Lesson Price]
FROM (Booking LEFT JOIN Customer_details ON Booking.[Customer ID] = Customer_details.[Customer ID]) LEFT JOIN Instructor_details ON Booking.[Instructor ID] = Instructor_details.[Instructor ID];

 

[Pat Hartman]

I looked at your database again and Bookings has no data. If you add a row to bookings, your query1 will work (but you should still use the left joins).

Your professor (and I use the term loosely) is not an Access expert. His naming standards are poor and he is using table level lookups which no Access expert would ever use. And he isn't enforcing referential integrity so you are not learning how to design a proper database schema. Part of learning how to use Access is learning the discipline to create sound applications and that includes naming standards, database design, and what features that you should never use even though they're there.