Generating a report between date ranges (1 Viewer)

Jaydee-187

Registered User.
Local time
Today, 14:14
Joined
Apr 23, 2017
Messages
9
Hi there!

I would like to generate a report from a query based on a range selected by the user from a form. I have entered one weeks worth of data to test it out as shown in the picture attached.

I have various food types (listed in the columns) and would like the report to sum of amount of food used by food type , based on the selected date range.

Please note that I am fairly new to access but have grasped a fair amount during my struggles.

Any suggestions would be appreciated ! Thank you.
 

Attachments

  • queryfoodbydate.JPG
    queryfoodbydate.JPG
    28.3 KB · Views: 103

June7

AWF VIP
Local time
Today, 06:14
Joined
Mar 9, 2014
Messages
5,425
That is not normalized data structure. Is that result of a CROSSTAB query? If that is your actual table then you seriously need to rethink your design. The filter and aggregate calc you want to do is not easily done with that structure.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:14
Joined
Jul 9, 2003
Messages
16,245
Oh dear no! Not likely to happen until you've sorted out your table structure. It might be possible to construct VBA code / queries to do what you want, however I wouldn't like to undertake it! Your best option is to redesign the tables. Any coding or query will just lead to tears eventually, as you find the code to be very specific and a monster to manage.
 

Jaydee-187

Registered User.
Local time
Today, 14:14
Joined
Apr 23, 2017
Messages
9
That is not normalized data structure. Is that result of a CROSSTAB query? If that is your actual table then you seriously need to rethink your design. The filter and aggregate calc you want to do is not easily done with that structure.

Hi,

Yes this is a cross tab query.
 

Jaydee-187

Registered User.
Local time
Today, 14:14
Joined
Apr 23, 2017
Messages
9
Oh dear no! Not likely to happen until you've sorted out your table structure. It might be possible to construct VBA code / queries to do what you want, however I wouldn't like to undertake it! Your best option is to redesign the tables. Any coding or query will just lead to tears eventually, as you find the code to be very specific and a monster to manage.

Hi,

Thanks for your response.

This was a snap of a crosstab query and not the table.

Would you be able to help?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:14
Joined
Jul 9, 2003
Messages
16,245
Would you be able to help?

I will add my two cents worth when I think it might do some good if that's what you mean.

Have you tried putting something like --- Between [StartDate] and [EndDate] in the query builder grid?
 

isladogs

MVP / VIP
Local time
Today, 14:14
Joined
Jan 14, 2017
Messages
18,186
Please upload the original query on which the crosstab is based
Either the SQL or a screenshot of design view
 

Jaydee-187

Registered User.
Local time
Today, 14:14
Joined
Apr 23, 2017
Messages
9
Hi,

Attached. I have also attached my DB.

Thank you.

------------
I've just uploaded my db and managed to filter the results of the query by date but would like it to show the total of each food for the week.

Could you help me?
 

Attachments

  • ZooDB_v4_vbtest2.zip
    1.9 MB · Views: 94
Last edited:

isladogs

MVP / VIP
Local time
Today, 14:14
Joined
Jan 14, 2017
Messages
18,186
Hi,

Attached. I have also attached my DB.

Thank you.

In contrast to June7, I do use crosstabs for certain purposes and it is possible to do reports from them
The main issue is to manage the report column headers but these can be defined in the report design
 

Jaydee-187

Registered User.
Local time
Today, 14:14
Joined
Apr 23, 2017
Messages
9
I will add my two cents worth when I think it might do some good if that's what you mean.

Have you tried putting something like --- Between [StartDate] and [EndDate] in the query builder grid?

Ok, Thanks for the suggestion.

I have uploaded my db and if you have the time, I'd appreciate your help.
 

isladogs

MVP / VIP
Local time
Today, 14:14
Joined
Jan 14, 2017
Messages
18,186
Hi

The thread has reappeared but your database is no longer available

For info, I've attached a screengrab of one of my 'crosstab reports'.
This one is based on a crosstab query for the last 40 lessons with a class.
The underlying data is of course normalised

The date range for the report is automatically updated depending on the date it is printed & the class selected. The column headers are updated accordingly. The report handles all of this without difficulty.

I have several other similar examples

@June7:
It would be difficult if not impossible to do this type of report by any other means.
If you can tell me otherwise, do let me know how!
 

Attachments

  • Crosstab report.PNG
    Crosstab report.PNG
    47.6 KB · Views: 94
  • CrosstabReportDesign.PNG
    CrosstabReportDesign.PNG
    22.8 KB · Views: 96

HiTechCoach

Well-known member
Local time
Today, 09:14
Joined
Mar 6, 2006
Messages
4,357
I use Crosstab reports a lot.

I have an example that I use all the time as a template.

See: Dynamic Report based on a Crosstab query

The following example uses starting dates and ending dates that are entered on a form as the parameters in a crosstab query. When a button on the form is chosen, Microsoft Visual Basic for Applications (VBA) functions run the crosstab query that creates a dynaset. The contents of the dynaset are then presented in a report.
 

Users who are viewing this thread

Top Bottom