Problems with a Report from a Query

[Ian Brown]

Hi, this is my first post on this site, so my apologies if I have done this wrong. I'll also apologise for my spelling as I'm dyslexic.

I'm having a problem with a Report from my database. I have 'Project Basic', 'Project Details' and 'Design Versions' tables and they are linked by the 'Project Reference Number'. I want to produce a report using data from all three tables, ordered by Project, so I used a query to gather the data (see the sql below), then based the report on the query. I have three entrees in the tables but the report will only work with one of the records and not with the other entries.

I'm using Access 2003 on XP.

SELECT [Project Basic].[Project Refrance Number], [Project Basic].*, [Design Versions].*, [Project Details].*
FROM ([Design Versions] INNER JOIN [Project Basic] ON [Design Versions].[Project Refrance Number] = [Project Basic].[Project Refrance Number]) INNER JOIN [Project Details] ON [Project Basic].[Project Refrance Number] = [Project Details].[Project Refrance Number]
WHERE ((([Project Basic].[Project Refrance Number])=[Forms]![Print Project List]![txtPRN]));

[Scott Price]

Hi, this is my first post on this site, so my apologies if I have done this wrong. I'll also apologise for my spelling as I'm dyslexic.

I'm having a problem with a Report from my database. I have 'Project Basic', 'Project Details' and 'Design Versions' tables and they are linked by the 'Project Reference Number'. I want to produce a report using data from all three tables, ordered by Project, so I used a query to gather the data (see the sql below), then based the report on the query. I have three entrees in the tables but the report will only work with one of the records and not with the other entries.

I'm using Access 2003 on XP.

SELECT [Project Basic].[Project Refrance Number], [Project Basic].*, [Design Versions].*, [Project Details].*
FROM ([Design Versions] INNER JOIN [Project Basic] ON [Design Versions].[Project Refrance Number] = [Project Basic].[Project Refrance Number]) INNER JOIN [Project Details] ON [Project Basic].[Project Refrance Number] = [Project Details].[Project Refrance Number]
WHERE ((([Project Basic].[Project Refrance Number])=[Forms]![Print Project List]![txtPRN]));

Your WHERE clause restricts the query results to one record, as only one Project Reference Number can be returned from the text box. (as an aside, there is a misspelling in your field name: Project Refrance Number... Not a problem as long as you know what it is talking about, and it is used consistently!)

To see all records, remove the WHERE criteria.

Regards,
Scott

[Ian Brown]

Your WHERE clause restricts the query results to one record, as only one Project Reference Number can be returned from the text box. (as an aside, there is a misspelling in your field name: Project Refrance Number... Not a problem as long as you know what it is talking about, and it is used consistently!)

To see all records, remove the WHERE criteria.

Regards,
Scott

Thanks for your reply Scott. I only want to print one project. When the project is selected from the Print Project List form, it returns no records, that is unless it's the record for Project RM0336.

I have restarted the database and set the query again, but with no luck. I'm trying with a sub form looking at separate queries, not too sure if this will work though.

[Scott Price]

Thanks for your reply Scott. I only want to print one project. When the project is selected from the Print Project List form, it returns no records, that is unless it's the record for Project RM0336.

I have restarted the database and set the query again, but with no luck. I'm trying with a sub form looking at separate queries, not too sure if this will work though.

Hmm... Just checking your FROM clause. You are setting the results to be returned from Design Versions. Are all three records present in all three tables?

If your main Project information is stored in Project Basic, you should reword your FROM clause to choose from that table.

If this by itself doesn't work, you may also need to investigate right/left joins.

To do this: In query design view grid, double click the line representing the relationship between each table. This will bring up the dialog box allowing you to set the properties for the join. Choose the appropriate join from the three options.

Regards,
Scott

[Ian Brown]

Thanks Scott for your help. I managed to get round the problem by using subforms. This should do for now and I can look into this later.

Again, thanks for your help!!

[Scott Price]

Thanks Scott for your help. I managed to get round the problem by using subforms. This should do for now and I can look into this later.

Again, thanks for your help!!

You're welcome, Ian. Glad you got something working for you!

Regards,
Scott