Problem with opening up report

[Dinger_80]

I have a report that pulls information from 8 tables. The query behind the report generates all of the required information I am looking for. Though when I go to open the report it says that a table could refrer to more than one table listed in the FROM clause of your SQL statement. The only reason I can see this as happening is because I use a query in the query for the report because I use one field multiple times but need different information. To better explain, I input into the report the person who requested the work, and also the person who did the work. Both require the FullName from a related table (That table has a one to many relationship with 2 tables). So I am wondering how to make it so that my report opens. If that means changing the query or if there is another method to achieve getting information to a text box in a report that needs information from two serparate records. Here is the SQL

SELECT TestRequestTable.TRNumber, WitnessTestingTable.AgencyProjectNumber, WitnessTestingTable.FileNumber, CatalogNumberTable.CatalogNumber, CatalogNumberTable.ProductName, TestingProcedureTable.SpecificNameOfTest, WitnessTestingTable.WitnessAgency, TestRequestTable.DateSubmitted, TestingProcedureTable.LabProcedureNumber, CatalogNumberTable.Manufacturer, ComponentTable.ComponentPartNumber, ComponentTable.ComponentType, FinishedTestRequestTable.ComponentMaterial, FacilityLocations.FacilityLocation, TestRequestTable.SampleDisposal, TestRequestTable.TestCatagory1, TestRequestTable.TestCatagory2, FinishedTestRequestTable.CrossReferences, FinishedTestRequestTable.TestingManHours, FinishedTestRequestTable.ReportSummary, TestRequestTable.PurposeOfTest, FinishedTestRequestTable.ProductDescription, FinishedTestRequestTable.TestProcedure, FinishedTestRequestTable.Observations, FinishedTestRequestTable.Attachments, FinishedTestRequestTable.ResultsDate, ReportPrepNameQuery.FullName, CrouseHindsPersonalTable.FullName
FROM ReportPrepNameQuery, CrouseHindsPersonalTable INNER JOIN ((FacilityLocations INNER JOIN (ComponentTable INNER JOIN (TestingProcedureTable INNER JOIN (CatalogNumberTable INNER JOIN (WitnessTestingTable INNER JOIN TestRequestTable ON WitnessTestingTable.ID = TestRequestTable.ProjectNumber) ON CatalogNumberTable.ID = TestRequestTable.CatalogNumber) ON TestingProcedureTable.ID = TestRequestTable.TypeOfTest) ON ComponentTable.ID = TestRequestTable.ComponentTested) ON FacilityLocations.ID = TestRequestTable.TestLocation) INNER JOIN FinishedTestRequestTable ON TestRequestTable.FinishedRelateField = FinishedTestRequestTable.ID) ON CrouseHindsPersonalTable.ID = TestRequestTable.Requestor;

 

[vbaInet]

You need to qualify every field name with the table name if that same field name is used in more than one table.

TableName[B][COLOR="Red"].[/COLOR][/B]FieldName

By the way, I haven't forgotten about your other thread. I just haven't had time to go back to it.

 

[Dinger_80]

As far as I can tell I did that in both the SQL and in the Report. Though FullName isn't used in multiple tables as much as the key is in multiple tables. The query itself works. It returns all the values I would need for the report to be generated. Somehow the report gets hung up on something. Is it because of the query within a query that is causing this issue? The ReportPrepNameQuery does also refrence the CrouseHindsPersonalTable. This is my first time with doing large amounts of relational database work (took me a long time and a few errors to understand how that all works). I haven't done anything up to this point where one field name is used multiple times in a report (or 1 to many relationship).

No worries, you have helped me out a real lot on here and if I was somewhat closer I'd buy you a round or two.

 

[vbaInet]

Just to be sure, go back to the query and fully qualify every field.
There must be somewhere your report is trying to use that field. Perhaps look in these places:

1. Order By property of the report
2. Filter By property of the report
3. Group and Sort properties of the report where you can create groupings and sorts.

 

[Dinger_80]

So beside the Qualifying that is already stated is it possible to qualify them further? Also Order and Filter have nothing on them they are blank properties. As far as Group and Sort are you suggestion I use that or asking what I am doing with that? If its the later, until you mentioned it I have never used it before, much less heard of it.

 

[vbaInet]

Nope, that's pretty much it.

As for the things I listed, I was asking you to check those properties to ensure that field names used in them are fully qualified.
If you want you can upload the db and I'll take a look. Relevant bits only and test data.

 

[Dinger_80]

Here is the downsized version with everything you need. Part of the report gets information from who the person is logging in. I don't know how to set it up to work off of a non work pc. My best suggestion would be to add a record to the CrouseHindsPersonalTable. The location of where that happens is in the User Module. As is I have the same issue in what I am uploading as I do on my full system.

 

[vbaInet]

I really won't have time trying to figure out where to add data. You understand your structure better than me so it's best you add the data and upload.

If the report already displays with some data then let me know and I'll have a quick look.

 

[vbaInet]

And if you're still struggling let me know and I'll take a look later.

 

[Dinger_80]

Ok this should work better. Wasn't too sure on how to set things up so as to get the results needed. I gave this a dry run on another work PC and it seemed to produce the same results.

 

[vbaInet]

The problem is "ReportPrepNameQuery.FullName". You need to remove it the field and table completely from the query and use other means to get that data into the report.

 

[Dinger_80]

So how would you recommend doing that then? The problem is I need the FullName of two records because two bits of information are required.

 

[vbaInet]

Dlookup(), subform?

 

[Dinger_80]

Ok got the freport to open up. Made a sub report that gets just the submitters name of the request and put that into the main report. It is working like a charm.

 

[vbaInet]

Good job!