A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

report problem



 
 
Thread Tools Display Modes
  #1  
Old April 29th, 2010, 05:17 PM posted to microsoft.public.access.reports
tonkaplayer
external usenet poster
 
Posts: 17
Default 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  
Old April 29th, 2010, 06:25 PM posted to microsoft.public.access.reports
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default 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  
Old April 29th, 2010, 07:33 PM posted to microsoft.public.access.reports
tonkaplayer
external usenet poster
 
Posts: 17
Default 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  
Old April 30th, 2010, 03:41 PM posted to microsoft.public.access.reports
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:20 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.