Multi Table Query with Look up...

I wish. I think they lock out a lot of stuff (I also can't get to MVP Albert Kallal's website. For some reason they have that blocked as well :( )
 
Our web security here is pretty low, however our access to different sections of our drives is very limited. If I wasn't working on this database behind our IT department's back I would of asked them to allow me access to upload it... however since they swear off Access, I am having to do everything myself. Making me very happy I took computer courses in High school and that they weren't all that long ago.

***:eek: Wow...random tinkering... I added the [RequestID] field from the actual Requests table into the query... and now have it working exactly the way I want it to...

Now I just need to know how to make the query automatically make a report with the returned data.

:) Feeling mightily proud of myself right about now...
 
Cool -- keep trying. It is good to get things done yourself as you will retain it much longer. :)
 
I think I figured the report out too. Because the query is searching for a parameter, it automatically asks for the request ID when I make it into a report. Now I just need to make the report legible... and I might finally be done with this database's set up... thank you for all your help!!!
 
Well I jumped ahead of myself... the report is NOT working the way I thought it was... not at all... Ugh...

It is pulling all the information from the query, however it is displaying the different Airline tickets on multiple pages - and displaying the blank fields.

I'll worry about the multiple pages later - first I want to get rid of the blank fields.

I tried putting Is Not Null in all the Criteria boxes for the separate information, but it makes the query not run at all - well it does but it stops returning data.

I have no idea what is happening now...:confused:
 
well, I'll try to take a look later at the screenshots (you may want to do up a few more of what you currently have, including of the report in design view as well as the report in report view.).
 
Alright. I'll do a couple of the query in design view (it is way too long for one picture) and then I will take a few of the report and put them on the same photobucket album.
 
Ok... I'm beginning to really dislike Access reports...

I have no idea what happened but all of the data has no magically decided to fit together on one page... now I just need to know how to make the query eliminate blank fields - because that should (keyword being should) eliminate all the extra titles and stuff on the report.

I'll add the pictures of the Query, but there is no point in adding the report now... it is spread over 4 pages because of the additional information.
 
BlueChicken said:
Alright. I'll do a couple of the query in design view (it is way too long for one picture) and then I will take a few of the report and put them on the same photobucket album.
Click to expand...
Good, and if anyone else wants to try to take a stab at it before I can do so when I get home, please feel free. I don't want to have to make you wait, if not necessary. :)
 
Well, I don't see any screenshots of your current report problem. The query looks like it should work but I would have to see the report in print preview and also in design view. So, if you don't want to wait until tomorrow night for me to look, take the screenshots and zip them and then upload, if you can get them to under 785Kb zipped. Otherwise we'll have to postpone again.
 
Ok... attached are the pictures of the report. I know that by rearranging I can get all the fields on one page - however, as you can see Request 117 only has air travel information, but the query which the report is based on is still displaying all the other blanks. I want the query to only report on files that actually have data - because that should change the way my report comes out for each of the files...

It is quite annoying!
 

Attachments

From what I've seen, I do believe you need to

1. Move your fields to the left. Currently your fields start out quite a ways.

2. Change to Landscape Orientation instead of Portrait.


So start there and then see what runs off the edge.
 
SOS said:
From what I've seen, I do believe you need to

1. Move your fields to the left. Currently your fields start out quite a ways.

2. Change to Landscape Orientation instead of Portrait.


So start there and then see what runs off the edge.
Click to expand...

Rearranging the form is not the problem. The problem is that there will be lots of empty fields that the query is picking up when I don't want it to. I want to have the query only pick up the information for each request that has been completely filled out because there are going to be records without one or even two of the areas needed. For example, Employee 1 may have 4 flight legs, 1 hotel, and 1 rental car - all information would be displayed in the report... however Employee 2 who only has 1 hotel will still have all the rest of the information (which would be blank for his request since nothing would be booked) also displayed on their report.

In my query I tried to set it so the criteria for everything would be "Is Not Null" which would only pull the files with information, but the query would not pull anything at all. That is what I am trying to do... only pull the information that has been booked, then report on that booked information.

I need to be able to send out a letter type report to the employee who the request was made for so that they can see their booked travel information (may that be airline, hotel, and/or rental car) in one spot in an organize manner. I do not want to see labels next to blank records because that would only be distracting to the employee.
 
Last edited:
Attached are newer pictures of the report. This format only works if all records have data (as pictured in my lovely fake file for Anne Rice, Request ID 500), but it does not work as well for the records that only have one section, as displayed in booking 117. (Information blocked because of employee information). Also... it only will work if there is only 1 hotel booking and 1 rental car booking because I had to move them up above the body into the header in order to have the airline information display in order right below one another without displaying on another page intirely.

I want to get rid of these stupid blanks! Ahhh! :mad:
 

Attachments

Actually, I have an idea. If you create a subreport each for hotel and car information then you can set them to shrink if they have no data.
 
SOS said:
Actually, I have an idea. If you create a subreport each for hotel and car information then you can set them to shrink if they have no data.
Click to expand...

Wow... that sounds so simple. Ingeniously simple... except I have no experience with sub-reports at all.

Will I be able to fit them on one page?
 
BlueChicken said:
Wow... that sounds so simple. Ingeniously simple... except I have no experience with sub-reports at all.

Will I be able to fit them on one page?
Click to expand...

Well, if you size them to only be as big as necessary and then when you put them in the main report you just size the control that houses them to be nothing more than a few centimeters high. Then, be sure to select that section and set the Can Shrink property to YES on the main form.
 
OK... I found a site using Google that gives step by step instructions for sub forms... and I gave them a shot. Looks like I may be able to get them working I just am haveing so trouble now getting only the data for one record (requestID) to show... am I going to have to make individual queries for flights, hotels, and rental cars?
 
BlueChicken said:
am I going to have to make individual queries for flights, hotels, and rental cars?
Click to expand...
Yes, you would do a query for each type and then you will link them by use of the Master/Child Links on the subreport container control.
 
OK... you are simply awesome!

The sub forms set to "can shrink" works perfectly. And I was able to make the main form look like the letter format I had wanted in the first place with expressions in unbound text boxes... and I had already made queries to pull the records from each form, so all I had to do was choose the right option! Thank you! Wow... I can't believe that it could be so simple!

:D:D:D:D:D:D:D:D:D

THANK YOU!!!!

Finally, I can just make everything uniform in design and call this database done!
 
You must log in or register to reply here.

Similar threads

M
Query with multiple parameters but don't bring up duplicate rows?
Replies
11
Views
410
MajP
MajP
N
Query to show records EXCEPT those with matching fields
Replies
8
Views
672
nortonm
N
D
Solved Query if Between lower and upper to return lower, returning all records
Replies
9
Views
518
dullster
D
R
Solved Query to find users not currently assigned to equipment
Replies
19
Views
755
DaveMD
D
R
Finding which queries use specific control from the main form
2
Replies
27
Views
2,464
isladogs
isladogs

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom