ACCESS FEED: Re: [MS_AccessPros] how to build report

Selasa, 20 Januari 2015

Re: [MS_AccessPros] how to build report

Jessica-

Remove the labels from the subreport. Size the subreport control so that there's space on the outer report next to the left side of the subreport. Put your labels in that blank space - you'll have to fiddle with it some to get them to line up with the data inside the 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 Jan 20, 2015, at 9:07 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Thanks John, I got the columns to work. Now is there a way to have the labels show only on the first column?

Jessica

On Friday, January 16, 2015 2:39 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Jessica-

For your outer report (Narcotic), you need to start with a query that looks something like this:

SELECT ReportNarcRecord.Agency, PSD.PSDName, Handlers.HandlerLastName, Year([DateofActivity]) AS RecordYear, Format([DateofActivity],"mmmm") AS RecordMonthText

FROM [Time] INNER JOIN (Quantity INNER JOIN (TrainCertDeploy INNER JOIN (Handlers INNER JOIN (PSD INNER JOIN ReportNarcRecord ON PSD.PSDID = ReportNarcRecord.PSDID) ON (Handlers.HandlerBadge = ReportNarcRecord.HandlerBadge) AND (Handlers.HandlerBadge = PSD.HandlerBadge)) ON TrainCertDeploy.TrainCertDeployID = ReportNarcRecord.TrainCertDeployID) ON Quantity.QuantityID = ReportNarcRecord.QuantityID) ON Time.TimeID = ReportNarcRecord.TimeID

GROUP BY ReportNarcRecord.Agency, PSD.PSDName, Handlers.HandlerLastName, Year([DateofActivity]), Format([DateofActivity],"mmmm");

Add SUM fields to that to get the totals you want for time, count, etc.

Then use your original query as the Record Source of a new report. Copy all the controls from Date of Activity through Indication (the first column) onto this new report and size it so it just contains the controls in one column. Go to Page Setup tab, Page Setup, click on the Columns tab, and enter 3 or 4 or whatever you think will work. If you look at this in Print Preview, you should see multiple records across the page.

Now put that columnar report as a subreport on your new report based on the Totals query above. Set the Link properties to Agency, PSDName, HandlerLastName, RecordYear, and RecordMonthText.

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 Jan 16, 2015, at 8:25 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I should have thought of that. My day started much too early this morning.

It is uploaded now. Thanks

Jessica

On Friday, January 16, 2015 1:16 PM, "wrmosca@comcast.net [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Hi Jessica

Compact your database and then zip it. That should make it small enough. If it is still too big, import just the objects you need into a new database and then zip that one. Keep in mind you shouldn't be uploading any company data. It should be sample data only.

Bill Mosca

---In MS_Access_Professionals@yahoogroups.com, <hobackjessica@yahoo.com> wrote :

John, My file is too large to upload. I'm not sure how to answer your question.

For the report I uploaded a PDF of:

ReportTrackingRecord - TrackingRecordID (PK)

EvidenceSkillRating - EvidenceID (PK), TrackingRecordID (FK), among other fields as seen on report

TrackingSkillRating - TrackingID (PK), TrackingRecordID (FK), among other fields as seen on report

Handlers - HandlerBadge (PK)

PSD - PSDID (PK), HandlerBadge (FK)

TrainCertDeploy - TrainCertDeployID (PK)

DistanceID (PK) - distance measure (ft, yd, mi)

I have the PSD and Handlers table so I can have the report show names rather than badge numbers

The query just ties them all together and combines the distance with the distance label for length of track, shows just the year and just the month for the boxes at the top.

I'm not sure what else you need.

Jessica

On Friday, January 16, 2015 12:44 PM, "Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

It scares me to do this cuz I'm afraid you'll find dozens of rookie mistakes that I've made throughout the database but I will upload the entire database.

There are three reports that will need to have the columns. As I said I didn't use subreports for any of them. But that will probably be the easiest wrong for me to right.

Thanks!

Jessica

On Friday, January 16, 2015 12:38 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Jessica-

What is the structure of your tables and the report query?

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 Jan 16, 2015, at 7:16 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

How do I get each column to show data from a different record - all from the month of January?

I have a report built using a query that joins all of the necessary tables. It works fine to get the data from my first record but I'm stuck on getting the next record to fill in the second column.  I exported what I have to a PDF that I will upload - "sample track report".   (I didn't use a subreport but I think that will be easy enough to change).

While we're at it, at the top of the form there are also boxes to show a running record count for Year to Date and Career to Date.   Can you also help me with a formula for those?

Thanks!
Jessica

On Friday, January 16, 2015 11:07 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Jessica-

I would build an outer report for year, month, and user, then a subreport in 3 columns. You might be able to use a Wizard to get a jumpstart on the subreport, then tweak the settings to get three columns.

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 Jan 16, 2015, at 5:52 PM, hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Happy Friday all!

I am trying to build a report that displays multiple records, each in it's own column on the report. I have already designed the report based off another version already being used. I will want to be able to query by month and user to run the report.

The report would look somewhat like this

Month: January    Year: 2015    User: Jessica H

             Record1      Record2       Record3
Date
Time
Etc.
Etc.
Etc.
Etc.

Each record will have all of the same fields which will make up my row headers.

Is this possible? I'm a novice Access user so if it is possilbe I appreciate simplified or "dumbed down" responses. Fingers crossed that I can do this in a query rather than code.

Thanks!
Jessica