ACCESS FEED: Re: [MS_AccessPros] Query Trouble

Jessica-

Something like:

=DCount("NarcRecordID", "<name of table here>", "HandlerBadge = " & [HandlerBadge] & " AND Year([<datefield>]) = " & [<control containing the displayed year>])

No need to GROUP BY if you first filter for the current handler.

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

http://www.viescas.com/

(Paris, France)

On May 21, 2015, at 5:24 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

For now that report is fixed in terms of my original question. Since I have your attention though, would you be willing to help me figure out how to make a query display a count of records for year to date? I have the count of ALL records ever created by a particular user, however, I also need it show show just the current year. My query for that is a count of NarcRecordID and Grouped by HandlerBadge. How do I tell it to count records for only the year displayed on the report?

Jessica

On Thursday, May 21, 2015 10:16 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Jessica-

If you're getting what you want, then I don't need to see the table layouts.

If you think you still need help, I need the table name, the table subject, the Primary Key, and any Foreign Keys as a minimum. Perhaps also show me which tables contain the fields you need in your report.

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

http://www.viescas.com/

(Paris, France)

On May 21, 2015, at 4:44 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Good morning John,

Something I did yesterday worked, I'm getting only one page of results now. Probably eliminating the unnecessary tables from the query.

What's the best way to show you the table layout?

Jessica

On Thursday, May 21, 2015 1:42 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Jessica-

Let's go back to square one. I need to see the layout of all the tables involved.

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

http://www.viescas.com/

(Paris, France)

On May 20, 2015, at 7:55 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

I eliminated PSD table from the subreport query because that doesn't need to be there. It wasn't serving any purpose anyway so thank you for catching that.

RecordMonthText is a field I added to the query to convert the DateofActivity into the text version of that month. There's a field in the header of the report (main report) that shows the month spelled out and a field for the year (RecordYear). Becuase of that the ReportNarcRecord (details) must be in the header. I also need it for the PSDID and HandlerBadge and their related tables to show PSDName and HandlerLastName in the header.

The header also displays a count of all records for that handler and a total time. While we're on that topic, I also need to figure out how to make it display a record count and time total for year to date.

Ideally, the users will be able to search for records by a specific month, year and handler or dog. I haven't tried to figure that out yet. I've been busy trying to make the report display all of the records correctly.

Thanks,

Jessica

On Wednesday, May 20, 2015 12:34 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Jessica-

I see tables PSD and ReportNarcRecord in both queries. Is it the ReportNarcRecord table that has the detail info? If so, you need to eliminate that from the main report query. But when you do that, I suspect you won't get the RecordMonthText that you say you need to link the two. Are you using some sort of external filter to just get the months you want, or do you want all months? If the latter, then DateOfActivity doesn't need to be in the outer recordset.

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

http://www.viescas.com/

(Paris, France)

On May 20, 2015, at 7:14 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John, I made a a copy of my copy and I've made adjustments but I'm still having the same problem.

The record source for my main report is qselNarcHeader:

SELECT DISTINCT PSD.PSDName, Year([DateofActivity]) AS RecordYear, Format([DateofActivity],"mmmm") AS RecordMonthText, ReportNarcRecord.NarcRecordID, Handlers.HandlerBadge, Handlers.HandlerLastName, qselNarcRecordCountTotal.CountOfNarcRecordID, sqelNarcRecordTimeTotal.SumOfElapsedTime, ReportNarcRecord.Agency, ReportNarcRecord.PSDID
FROM Quantity INNER JOIN ([Time] INNER JOIN (TrainCertDeploy INNER JOIN (((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) ON TrainCertDeploy.TrainCertDeployID = ReportNarcRecord.TrainCertDeployID) ON Time.TimeID = ReportNarcRecord.TimeID) ON Quantity.QuantityID = ReportNarcRecord.QuantityID;

And the record source for my subreport is qselNarcPerformDoc:

SELECT ReportNarcRecord.*, Handlers.HandlerLastName, PSD.PSDName, TrainCertDeploy.TrainCertDeployDesc, Format([DateofActivity],"mmmm") AS RecordMonthText, Year([DateofActivity]) AS RecordYear, IIf(IsNull([TimeAged])=True,[TimeDesc],[TimeAged] & " " & [TimeDesc]) AS AgedTime, IIf(IsNull([NarcoticQty])=True,[QuantityDesc],[NarcoticQty] & " " & [QuantityDesc]) AS Quantity
FROM TrainCertDeploy INNER JOIN ([Time] INNER JOIN (Quantity INNER JOIN (PSD INNER JOIN (Handlers INNER JOIN ReportNarcRecord ON Handlers.HandlerBadge = ReportNarcRecord.HandlerBadge) ON PSD.PSDID = ReportNarcRecord.PSDID) ON Quantity.QuantityID = ReportNarcRecord.QuantityID) ON Time.TimeID = ReportNarcRecord.TimeID) ON TrainCertDeploy.TrainCertDeployID = ReportNarcRecord.TrainCertDeployID;

The Master/Child Fields are PSDID and RecordMonthText and RecordYear.

Jessica

On Wednesday, May 20, 2015 11:03 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Jessica-

How many records per user does the first query return? If it's more than one for some users, that's your problem. You could try:

SELECT DISTINCT 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;

… but I don't think that will help. The outer report query needs to contain ONLY the fields that you want on that report. It should not contain any detail that appears multiple times per user.

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

http://www.viescas.com/

(Paris, France)

On May 20, 2015, at 5:50 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

The subreport is so that I can have my records display in columns under one header showing the user, month, year, counts, etc.

I do have two separate queries in my original db but I have the same problem.

Here's the header query:

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;

And the subreport query:

SELECT ReportNarcRecord.*, NarcoticCheckBox.*, Diversion.*, TargetLocations.*, IIf(IsNull([TimeAged])=True,[TimeDesc],[TimeAged] & " " & [TimeDesc]) AS AgedTime, IIf(IsNull([NarcoticQty])=True,[QuantityDesc],[NarcoticQty] & " " & [QuantityDesc]) AS Quantity, TrainCertDeploy.*
FROM Quantity INNER JOIN ([Time] INNER JOIN (TrainCertDeploy INNER JOIN (((ReportNarcRecord INNER JOIN Diversion ON ReportNarcRecord.NarcRecordID = Diversion.NarcRecordID) INNER JOIN NarcoticCheckBox ON ReportNarcRecord.NarcRecordID = NarcoticCheckBox.NarcRecordID) INNER JOIN TargetLocations ON ReportNarcRecord.NarcRecordID = TargetLocations.NarcRecordID) ON TrainCertDeploy.TrainCertDeployID = ReportNarcRecord.TrainCertDeployID) ON Time.TimeID = ReportNarcRecord.TimeID) ON Quantity.QuantityID = ReportNarcRecord.QuantityID;

These still have the four separate tables making up my records.

Jessica

On Wednesday, May 20, 2015 10:43 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Jessica-

Oh, my! You need two separate queries - one to return the DISTINCT users and the other to return the detail. Using the one query, when a user has several records, you'll get a "page" for each record with the user data repeated.

But why are you using a subreport if everything is in this one query? Put the user name in a group header and show the detail you want in the Detail section.

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

http://www.viescas.com/

(Paris, France)

On May 20, 2015, at 5:16 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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.

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

http://www.viescas.com/

(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