John,
I'm sure I've made a mess of my database - fortunately I use a copy for experimenting. In my original database, I had four different tables collecting info from a form with subforms. The problem was if any of the four tables didn't have a corresponding record, none of the record would appear in my report. In my experimental db, I've combined the tables into one (which I know is probably a no-no in db design). This gets all of my records to return.
I have one query that I use for both the main report and the subreport. They are linked by PSDID and RecordMonthText as the Master/Child Fields.
SELECT DISTINCTROW PSD.PSDName, Year([DateofActivity]) AS RecordYear, Format([DateofActivity],"mmmm") AS RecordMonthText, ReportNarcRecord.NarcRecordID, Handlers.HandlerBadge, Handlers.HandlerLastName, ReportNarcRecord.*, qselNarcRecordCountTotal.CountOfNarcRecordID, sqelNarcRecordTimeTotal.SumOfElapsedTime
FROM ((Handlers INNER JOIN (PSD INNER JOIN ReportNarcRecord ON PSD.PSDID = ReportNarcRecord.PSDID) ON Handlers.HandlerBadge = ReportNarcRecord.HandlerBadge) INNER JOIN qselNarcRecordCountTotal ON Handlers.HandlerBadge = qselNarcRecordCountTotal.HandlerBadge) INNER JOIN sqelNarcRecordTimeTotal ON Handlers.HandlerBadge = sqelNarcRecordTimeTotal.HandlerBadge;
FROM ((Handlers INNER JOIN (PSD INNER JOIN ReportNarcRecord ON PSD.PSDID = ReportNarcRecord.PSDID) ON Handlers.HandlerBadge = ReportNarcRecord.HandlerBadge) INNER JOIN qselNarcRecordCountTotal ON Handlers.HandlerBadge = qselNarcRecordCountTotal.HandlerBadge) INNER JOIN sqelNarcRecordTimeTotal ON Handlers.HandlerBadge = sqelNarcRecordTimeTotal.HandlerBadge;
Jessica
On Wednesday, May 20, 2015 10:02 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Jessica-
What is the Record Source for the main report? Does that query return each user more than once?
What is the SQL for both the report and subreport?
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On May 20, 2015, at 4:57 PM, hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Good morning group!
I have a query built that appears fine in datasheet view. In this view it shows each record one time. However, when I open the query in a report I have problems.
My report is set up with a subreport to show my results in columns. Each page shows a specific user and a specific month's records. For example, page 1 should show UserA and all records from May - right now I have two. However, since there are two records the report shows both records on page 1 and there's a page 2, also with both records. If I have three May records for UserA there will be three pages, all showing all three records.
How do I tell my query or report not to create a page for every record?
Jessica
__._,_.___
Posted by: Jessica Hoback <hobackjessica@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar