473,323 Members | 1,551 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Relationships & Reports

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
10 1939
willakawill
1,646 1GB
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
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
32,556 Expert Mod 16PB
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
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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

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

Similar topics

7
by: Jeff | last post by:
When you connect to an Oracle database with Access and ADO, is there a way to get information on the Oracle table relationships? Jeff
5
by: Deano | last post by:
I'm experiencing bloat problems when I restore my backup files. Users can take a look at the reports of any previous backup without having to ensure they have a backup of their current data - this...
5
by: Mike Turco | last post by:
What is the difference between creating relationships in the front-end vs. the back-end database? I was trying to create a relationship in a database front-end and noticed that I could not check...
6
by: GaryDave | last post by:
My school registration database has not been quite right after a recent compact and repair (done while I was away). Though most of the many forms and subforms are working normally, one form in...
1
by: fastfish | last post by:
I have converted a db from access 97 to 2000 - some how the relationships didn't get moved over. Big problem. Is thier any way, other than recreating it by hand, to duplicate the relationships...
45
by: salad | last post by:
I'm curious about your opinion on setting relationships. When I designed my first app in Access I'd go to Tools/Relationships and set the relationships. Over time I'd go into the window and see...
4
by: alexandre.brisebois | last post by:
Hi, I am using access 2003, I would like to know if there is an option to reorganize the tables in a maner that is readable, as we can do in sql sever 2000 or 2005. I have been given a database...
1
by: cricket7 | last post by:
I think I figured out my tables and relationships. Tables: Table 1 tblTowedVehicles fldTowID fldReportDate fldReportTime fldlReportNumber
4
by: netnewbie78 | last post by:
Hello All, I don't have a problem (but maybe I will after I explain). I have a question with regards to something I saw in Access 2007. But first, a little backstory: I'm writing a very small...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.