Relationships & Reports

[AustinClark]

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.

[willakawill]

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.

[AustinClark]

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

[NeoPa]

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...

[MMcCarthy]

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

[AustinClark]

Aha: in the table design view, for the 'Facility' field the lookup query is this:

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.

[MMcCarthy]

Aha: in the table design view, for the 'Facility' field the lookup query is this:

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

[AustinClark]

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.

[MMcCarthy]

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

[AustinClark]

Thank you all, that did it! You guys are great. I could not have asked for a more patient and helpful group.

[MMcCarthy]

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.