Query-PivotTable View-to Report (1 Viewer)

[Lilly420]

Hello,

I have a query in which I saved in PivotTable View because that is how the person wants to see the data. In the query, it has a date prompt in which one can type in a date range and it will give the data within those dates. Works perfect for what I need.

Then I read how you can put a PivotTable into a Report (which works great as it is for a Manager and I can add titles, etc.) so I did that but now the date prompt comes up twice and the only way to get the data is to type in the dates twice…would anyone know why that is and how to fix it so it only prompts once? I am confused.

Thank you for any help you can provide.:banghead:

Lilly

 

[CJ_London]

your prompts for dates are different - suggest you post your sql so we can see where.

Alternatively, you have a report and subreport - these will each have their own query and date prompts - solution is to modify your subform query to refer to the parent dates (e.g. parent.startdate, parent.enddate)

 

[Lilly420]

Hello CJ,

Thank you for the help. Below is the SQL from my query...is that what you needed?

SELECT tblFEEmployeePTONonTime.PTODate, tblFEEmployees.FEEmpLastName, tblFEEmployeePTONonTime.PTODays, tblFEEmployeePTONonTime.PTOComments
FROM tblFEEmployees INNER JOIN tblFEEmployeePTONonTime ON tblFEEmployees.FEEmployeeID = tblFEEmployeePTONonTime.FEEmployeeID
WHERE (((tblFEEmployeePTONonTime.PTODate) Between [Start Date] And [End Date]) AND ((tblFEEmployees.FEEmpLastName) Not Like "Suayan") AND ((tblFEEmployees.FEJobTitleID) Not Like "1" And (tblFEEmployees.FEJobTitleID) Not Like "2"))
ORDER BY tblFEEmployeePTONonTime.PTODate DESC;

I did try adding parent in as you suggested but did something wrong because it said inproper use of brackets...sorry not sure how to do it correctly.

Again, thank you for taking the time to help me.

Lilly

 

[CJ_London]

So this is asking for two dates - Start Date and End Date

You said it is asking for two dates, so what is the problem?


Also I note that you are treating FEJobTitleID as text - is this correct?

instead of

((tblFEEmployees.FEJobTitleID) Not Like "1" And (tblFEEmployees.FEJobTitleID) Not Like "2")
You could just have

tblFEEmployees.FEJobTitleID not in (1,2)

 

[Lilly420]

Hello CJ,

Sorry if I did not explain this you well. The query works great...I need a prompt for start and end dates to get the range of data...

I read that I could put a Pivot Table into a Report...so I did that and now it prompts me twice when I try to run the Report which houses the PivotTable Query...i.e., when I click on the Report...it asks me for start date - I type the date - then it asks me for end date- I type the date...then it asks me again for start date and I type the date and then again for end date - I type the date - it have to type the dates in four times in order to get the report to run and I don't know why....does this make sense now?

I changed my query for the ID to read what you suggest and it works great...thank you...

...Not in (1,2)...the only way I could get it to work for me was the other way...did not know the "Not in" so many thanks.

Lilly

 

[CJ_London]

OK - so do you have a main report and a subreport - and is this the code for the main report (since it isn't a crosstab)?

 

[Lilly420]

Hello CJ,

I never got any other emails from you so I thought maybe I lost you and then was out of the office for a bit...the code I gave you was for the query...I was not sure if that is what you needed? Again, sorry for not following up sooner.

Lilly

 

[CJ_London]

My question was about whether you have a main report and a subreport - and if so there will be two queries, and you have only supplied one.

With regards the query you have supplied, it will only ask for the dates once, not twice. This implies you also have date parameters in the other query - hence my thinking that you have a report and a subreport.

Please confirm whether you have a report and subreport and if so, indicate which one the query supplied is for and also supply the other query

 

[Lilly420]

Ok, this is what I did. I have only one query with the code I gave you...I changed the Default View of the query to PivotTable so when you run it that is how one sees it (PivotTable) and then I put that query into a report...the report only has a Report header where I just put in a title and a Close Report button. The query is in the details of the report but is seen as a PivotTable...does this make sense but when you run the report it prompts on date criteria twice for start date and end date and not sure why that is...?

Thank you for the help...

Lilly

 

[Lilly420]

Just in case you need to know how I created the report...I created a blank report and then added a title, saved it. Then I opened it back up, went into Layout view, and dragged the query which is defaulted to Pivot Table view into the details. Not sure this helps any but thought I would tell you how I created the report.

Lilly

 

[CJ_London]

I'm getting confused.

I changed the Default View of the query to PivotTable

do you mean "I changed the Default View of the form to PivotTable?". So far as I am aware there is not a pivot query, instead you have a crosstab.

Assuming you are talking about a form then I suspect this is because you have used the dates in your pivot filter or columns which is a separate operation from the filtering of the query, hence the request twice.

Suggest you convert your query to a crosstab and use that instead

Or something else to try (I've not tried it myself so no guarantees)...

Use a form rather than a report - Put the start and end dates on your form as unbound controls with suitable default values and modify your query to use these instead - assuming you

a) have called the two text controls SDate and EDate and set their default values to something relevant
b) have put the sql to your query in your form recordsource (it won't work if you just refer to the query) you would change this

(tblFEEmployeePTONonTime.PTODate) Between [Start Date] And [End Date])

to this

(tblFEEmployeePTONonTime.PTODate) Between Parent.SDate And Parent.EDate)

You may also need a bit of code in the afterupdate of these date controls to requery the subform after you have entered a date

 

[Lilly420]

Sorry don't mean to confuse you.

No not in a form--in the query--how you do this ... click a blank area of the query builder so that the Property Sheet displays Query Properties. Set the Default View Property to PivotTable or PivotChart so that is what I did. Works perfect for me but I am not the one using it...So I read how you can put an object (query) into a report or form so that is what I did ... I sent that to you earlier and I tried form too and it did the same thing. If I only run the query in does exactly what it is suppose to and gives me the Pivot table...I was trying to make this more user friendly for those that don't know Access well and could close out of the report easily.

 

[CJ_London]

Ahh! Learn something new every day - I didn't know that.

Either way, I regret I don't have any other suggestions.

When you say you tried a form did you try a pivot form and drag that onto the report?

I'll see if I can get someone else to help

 

[MarkK]

Does the parent report have a RecordSource, or is it just a host for the subform?

Generally, if you drop a QueryA on a report in design view, a new subreport is automatically created that has QueryA as a RecordSource. If the parent report also has a valid RecordSource, the subreport will try to automatically synchronize with the parent in the LinkMasterFields and LinkChildFields properties of the subform control. In addition, when you open the parent report, the subreport loads first (!!!), and it will prompt for whatever parameters it needs. Then, the main report opens and tries to enforce the LinkMaster/ChildFields of the subform, and your subform query is re-executed.

I expect this is the problem. A solution might be to clear the LinkMasterFields and LinkChildFields properties of the subform control. You can also load tbe subreport programmatically AFTER the main report opens. To do this, delete the name of the subreport from the SourceObject property of the subform control in design view. Then, load the subreport in code after the report opens . . .

private sub report_open(cancel as integer)
    Me.MySubreport.SourceObject = "NameOfMySubReport"
end sub

With this approach the subform loads AFTER the main form and should only query its datasource once.

Hope this helps, and if not, post a database that demonstrates the problem, and we'll crack it that way,
Cheers,

 

[RainLover]

Sorry for my lack of expertise in this regard, but maybe this might help.

My understanding of a pivot is that it is similar to a graph with the added ability to change the chart as viewed by the user.

There is nothing that can be changed on a report. A pivot does nothing except display data.

So instead why not use a Graph. Just because you found this new toy is no reason to have to use it.

I once produced various Reports, all of which had 6 (Six) graphs. I would have 6 forms where I constructed the Graphs. I could preview the output then when all 6 are correct I would print the report from the Forms.

A totally different approach. I hope it helps you save the problem.

Just another quick suggestion.

Because you are having problems try using less data. Solve the small problem then move on to the real one.

 

[Lilly420]

Hello everyone,

First, thank you all for the help.

MarKK-I believe you are correct just not sure how to fix it correctly. I am new to code and learning with the help of all you smart folks here in the forum. I am unsure of how to do what you said...I will give it a try but it may take me awhile...if I have problems which I do anticipate, may I post my database for you to help me with...?

To the other question about charts, unfortunately, the users want the data in the Pivot Table view like I have it...but again thank you for the suggestion.

You people are the best, thank you again...and please let me know if I can reach out again with my database for help...probably alot of help

Lilly

 

[Lilly420]

To all that tried to help me,

Well I was unsuccessful trying to do what MarkK suggested but then came up with something else...instead of putting the query into the report...I decided to make the query a Make-Table and then took the table and made a form-changed that to PivotTable View and put that form into a Report and then I could add all my formatting and buttons etc - then I developed a Macro so the user justs hits the button and it asks for the date range only once...it works great...I know there is a cleaner way of doing it but I don't know enough about code to do it that way. Anyway, thank you to all for the help, I truly appreciate it. Happy holidays to you and yours.

Lilly