By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,561 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

Relationships & Reports

P: 8
Hello everyone. I decided to post because I'm an Access newbie and didn't know how to properly phrase my problem as search terms.

I have a database that has two tables. One contains a list of facilities, the other a list of work orders, date, hours, activity, and a drop down to choose the facility. There is also a form that lets the user enter all the data about the work order, and in that form the drop down correctly shows the entire building list.

I need a report that the user can run that will show the following: the work order, facility, B/U, date, activity, and hours, with B/U, work order, and facility being the three grouping levels.

I made a report, using the wizard, and it returns the results I need...except that it's using the unique ID from the building table instead of the building name. I changed the relationship, but that didn't fix it. I tried changing the control source and got back "#Error" for my trouble. Attached are two screen shots: the first of the tables, the form, and the report, the second of the relationship layout.

A copy of the database, less a few tidbits of semi-proprietary data can be made available if need be.

Tables, Form, and Report:



Relationships:



Any help (even a "Read section X of the help file you dummy!") is most appreciated.
Dec 29 '06 #1
Share this Question
Share on Google+
10 Replies


100+
P: 1,646
Hi Austin,
Sounds like you entered incorrect info into the report wizard. Put a query together with the fields that you want on the report and recreate the report using the wizard basing the report on your query. Forget about everything else.
Dec 29 '06 #2

P: 8
willakawill, thanks for the reply.

OK, I created a query and then created a new report based on that query:



Here are the results of the Query:



But the report seems to be doing the exact same thing:



I think the relationship isn't working right, but I don't know why.

Oh technical info I forgot to add in the first post: I'm using Access 2003, but the database is being saved for Access 2000
Dec 29 '06 #3

NeoPa
Expert Mod 15k+
P: 31,661
If the query is working but the report isn't reflecting the new values returned by the report you could have one of two problems.
1. You haven't saved the query before running the report.
2. The report has filtering or sorting set which works differently from the native query.
Mainly we're working blind though, as the report as shown in the picture seems to be showing fields which aren't there in your query...
Dec 30 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
In your original post your table was bound on the facility list. Are you sure that this is looking at the list name and not the corresponding number. In the table design go down to facility and in the lookup properties is there anything showing besides textbox if there is can you post the select statement for the row source.

Mary
Jan 1 '07 #5

P: 8
Aha: in the table design view, for the 'Facility' field the lookup query is this:
Expand|Select|Wrap|Line Numbers
  1. SELECT [Building List].ID, [Building List].[Building Number] FROM [Building List]; 
For reference: The database.

Thank you for taking the time to help me out.
Jan 2 '07 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Aha: in the table design view, for the 'Facility' field the lookup query is this:
Expand|Select|Wrap|Line Numbers
  1. SELECT [Building List].ID, [Building List].[Building Number] FROM [Building List]; 
For reference: The database.

Thank you for taking the time to help me out.
Go back to the relationships window and delete the relationship between Building Number and Facility and replace it with a relationship between ID and Facility. This should solve your problem.

Mary
Jan 3 '07 #7

P: 8
I think Access just hates me (or I'm being dense?). Is this the point were I should delete the tables, reports, and queries and redo them from scratch? The data entry is no issue, that's all stored in a text file for backup anyway.

Jan 3 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
I think Access just hates me (or I'm being dense?). Is this the point were I should delete the tables, reports, and queries and redo them from scratch? The data entry is no issue, that's all stored in a text file for backup anyway.
Open the report in design view. Delete the textbox currently used for facility and now open the fieldname list and drag facility from it onto the report.

This should solve your problem.

Mary
Jan 3 '07 #9

P: 8
Thank you all, that did it! You guys are great. I could not have asked for a more patient and helpful group.
Jan 4 '07 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
Thank you all, that did it! You guys are great. I could not have asked for a more patient and helpful group.
You're Welcome.
Jan 4 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.