If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
report problem
I have a database that tracks student parking stickers. The make and modle
of car is selected from a combo box that uses a table as its lookup ie. users selects Ford in Make then in Modle the only see teh ford modles and select one there. The make is stored in the studnet information as a number ie 1 stands for Ford 23 for Toyota and so on. My question is when I create report on the student table it displays the number in the make field not the name of the make. How do I get it to refer back and pull the name of the make? boy this rambles that happens when you are doing too many things at once. I fyou understand please help. Keep it simple though. |
#2
|
|||
|
|||
report problem
Tonkaplayer -
You can do this two ways. One is to adjust the query that the report is based on (or set up a query if the report is based on a table). The other is to look up the data from within the report. If you want to adjust the query, then add the lookup tables to the design grid and add the joins if they are not automatically there. (Note that if the make or model fields can be left blank, then the joins must be outer joins.) Then select the field from the lookup tables with the descriptions of the make or model so they will be in your query. Now on the report, use those fields rather than the numeric fields. If you want to look up the names on the report (only use this if there aren't too many), set the control source for the field to be something like this (use your tablename, fieldnames): =DLookup("CarMakes","CarMakeDescription","[CarMakeID] = " & [CarMakeField]) Do the same for the car model. Use the help on DLookup for this. -- Daryl S "tonkaplayer" wrote: I have a database that tracks student parking stickers. The make and modle of car is selected from a combo box that uses a table as its lookup ie. users selects Ford in Make then in Modle the only see teh ford modles and select one there. The make is stored in the studnet information as a number ie 1 stands for Ford 23 for Toyota and so on. My question is when I create report on the student table it displays the number in the make field not the name of the make. How do I get it to refer back and pull the name of the make? boy this rambles that happens when you are doing too many things at once. I fyou understand please help. Keep it simple though. |
#3
|
|||
|
|||
report problem
I will need to look thru this. Let me add some info. I am looking to just
print the table so security can have a paer copy, therefore there is specific record input I am looking for. I have created a query I tried to just dump it from the table. All works fine excpet the make of car which is the one that shows the number. The lookup tables are not joined to the student table. Here is what I have Table called Make with two fields: MakeID (autonumber) MakeName (Text) Table Called Model with fields ID (autonumber) ModelName (text) Makeid (number) Table called Parking which contains student id, last name, first name ......Make (which captures the make of car for each student and this is where only the number of the make is and not the name.) Model (which captures the model for each student and does show the actual name.) My user interface is called parking form and display name grade etc and allows the users to input a parking sticker number and info for a car year amke model color etc. So I would like to dump the parking table to a print out with of course the name of the car and not it's corresponding autonumber. Does this help? ps my make and model have a realtionship but they are not realted to anything else. To cpmlicate this I have fields for a second sticker and car also in the same table as the first that also link to the combo lookup in make and model. Have I complete confused you? Also I appreciate your style of response, not condesending like many on here are. "Daryl S" wrote: Tonkaplayer - You can do this two ways. One is to adjust the query that the report is based on (or set up a query if the report is based on a table). The other is to look up the data from within the report. If you want to adjust the query, then add the lookup tables to the design grid and add the joins if they are not automatically there. (Note that if the make or model fields can be left blank, then the joins must be outer joins.) Then select the field from the lookup tables with the descriptions of the make or model so they will be in your query. Now on the report, use those fields rather than the numeric fields. If you want to look up the names on the report (only use this if there aren't too many), set the control source for the field to be something like this (use your tablename, fieldnames): =DLookup("CarMakes","CarMakeDescription","[CarMakeID] = " & [CarMakeField]) Do the same for the car model. Use the help on DLookup for this. -- Daryl S "tonkaplayer" wrote: I have a database that tracks student parking stickers. The make and modle of car is selected from a combo box that uses a table as its lookup ie. users selects Ford in Make then in Modle the only see teh ford modles and select one there. The make is stored in the studnet information as a number ie 1 stands for Ford 23 for Toyota and so on. My question is when I create report on the student table it displays the number in the make field not the name of the make. How do I get it to refer back and pull the name of the make? boy this rambles that happens when you are doing too many things at once. I fyou understand please help. Keep it simple though. |
#4
|
|||
|
|||
report problem
Tonkaplayer -
You want a query like this (Use your field names where I have made them up): SELECT Parking.StudentID, Parking.StudentLastName, Parking.StudentFirstName, Make.MakeName, Model.ModelName FROM (Parking LEFT JOIN Make on Parking.Make = Make.MakeID) LEFT JOIN Model ON Model.ID = Parking.Model AND Model.MakeID = Parking.Make IF every Parking record has both a make and model from the associated tables (that is no blanks), then it can be simpler: SELECT Parking.StudentID, Parking.StudentLastName, Parking.StudentFirstName, Make.MakeName, Model.ModelName FROM Parking, Make, Model WHERE Parking.Make = Make.MakeID AND Model.ID = Parking.Model AND Model.MakeID = Parking.Make You can do this in query design by adding all three tables. The joins may come automatically. If so, just add the fields you want to see from the query. If the joins are not automatic, then 'drag' the Make field from the Parking table to the MakeID field in the Make table, etc. -- Daryl S "tonkaplayer" wrote: I will need to look thru this. Let me add some info. I am looking to just print the table so security can have a paer copy, therefore there is specific record input I am looking for. I have created a query I tried to just dump it from the table. All works fine excpet the make of car which is the one that shows the number. The lookup tables are not joined to the student table. Here is what I have Table called Make with two fields: MakeID (autonumber) MakeName (Text) Table Called Model with fields ID (autonumber) ModelName (text) Makeid (number) Table called Parking which contains student id, last name, first name .....Make (which captures the make of car for each student and this is where only the number of the make is and not the name.) Model (which captures the model for each student and does show the actual name.) My user interface is called parking form and display name grade etc and allows the users to input a parking sticker number and info for a car year amke model color etc. So I would like to dump the parking table to a print out with of course the name of the car and not it's corresponding autonumber. Does this help? ps my make and model have a realtionship but they are not realted to anything else. To cpmlicate this I have fields for a second sticker and car also in the same table as the first that also link to the combo lookup in make and model. Have I complete confused you? Also I appreciate your style of response, not condesending like many on here are. "Daryl S" wrote: Tonkaplayer - You can do this two ways. One is to adjust the query that the report is based on (or set up a query if the report is based on a table). The other is to look up the data from within the report. If you want to adjust the query, then add the lookup tables to the design grid and add the joins if they are not automatically there. (Note that if the make or model fields can be left blank, then the joins must be outer joins.) Then select the field from the lookup tables with the descriptions of the make or model so they will be in your query. Now on the report, use those fields rather than the numeric fields. If you want to look up the names on the report (only use this if there aren't too many), set the control source for the field to be something like this (use your tablename, fieldnames): =DLookup("CarMakes","CarMakeDescription","[CarMakeID] = " & [CarMakeField]) Do the same for the car model. Use the help on DLookup for this. -- Daryl S "tonkaplayer" wrote: I have a database that tracks student parking stickers. The make and modle of car is selected from a combo box that uses a table as its lookup ie. users selects Ford in Make then in Modle the only see teh ford modles and select one there. The make is stored in the studnet information as a number ie 1 stands for Ford 23 for Toyota and so on. My question is when I create report on the student table it displays the number in the make field not the name of the make. How do I get it to refer back and pull the name of the make? boy this rambles that happens when you are doing too many things at once. I fyou understand please help. Keep it simple though. |
Thread Tools | |
Display Modes | |
|
|