Report Nightmare for me

[wrayman]

*Wrong files in this post please see reply*



I can not figure this out, I am a newbie, but this seems simple but not for me

I started a database with queries but that isn't working[

Hello, have a simple problem. Need to create a report that has the structure of the attached file sample2.pdf

In the Database table Glucose Reading I need the fields [ReadingDate] [ReadingTime] [Reading] displayed in the report

The ReadingTime will be filtered as followed

Morning = Readings taken between 06:30 (am) to 13:00 (pm)

Afternoon = Readings taken between 13:01 (pm) to 18:00 (pm)

Evening = Readings taken between 18:01 (pm) to 01:00 (am)

All the filters would be changeable

From the above criteria, the report would be generated with the structure of sample2.pdf

[wrayman]

Whoops wrong file(s) sorry new to forum

Here are the correct files:

sample.zip

[ssanfu]

How do you propose to handle readings like 9/6/2016 evening readings? There are 3 readings (technically 4 because of the 9/7/2016 12:07 AM reading).
Which reading should be displayed?



I would probably add a table "tblReportReadings" and use VBA to add records to the table. A form would have the date range and the filter times.
A report would be based on the table.

[wrayman]

How do you propose to handle readings like 9/6/2016 evening readings? There are 3 readings (technically 4 because of the 9/7/2016 12:07 AM reading).
Which reading should be displayed?



I would probably add a table "tblReportReadings" and use VBA to add records to the table. A form would have the date range and the filter times.
A report would be based on the table.

Good Question, the solution is I will delete the duplicate/multiple readings so there are only 3 readings for one day.

I am making progress, the form worked until I imported more data, then it blew up and the dates are no longer in order.......

Maybe I am going about it the wrong way?

updatedGlucose.zip

[ssanfu]

Tables do not have any order. They are just bit buckets.

Just need to sort (order) the data in the form record source.

Reports have their own sort - sorting in a query does not affect a report sort order.

[wrayman]

I tried that sort earlier, and it returned no records, so I knew you provided me with a working file, and it still returned no records.

Turns out my display driver or something else was messing up the access report engine.

I rollbacked driver and it worked.

So what did you think of the solution I came up with to display in the report? is that an efficient way?

[wrayman]

Just notice another weird behavior

If a record doesn't have an entry for Morning Afternoon and Evening it doesn't show up in the report.

Some days I am not able to take 3 readings. Any suggestion to a fix?

[ssanfu]

Some days I am not able to take 3 readings. Any suggestion to a fix?

Maybe you could add the missing reading(s) as 0 (zero), so you have 3 readings. That is what my Dad did if/when he missed his inhaler times.




Oops, forgot....
For the dates, you can enter a start date, an end date or both dates.

Entering just a start date will give you all records from that date to the end
Entering just an end date will give you all records from that date to the beginning
Entering a start date and an end date will give you all records between those dates.

[wrayman]

Maybe you could add the missing reading(s) as 0 (zero), so you have 3 readings. That is what my Dad did if/when he missed his inhaler times.

I could and have done that, but the actual readings aren't entered they are downloaded from the meter. So I have to sift through all the readings and manually find the missing reading

[ssanfu]

Didn't know about downloading the readings.

Sooooo....

Maybe Plan B : Use vBA to check and add reading of 0 if missing.

Download Glucose Readings from meter
Enter a starting date in text box... No sense in checking Jan records if current month is October.
Click button to start.
For each day, code checks for Morning, Afternoon, Evening reading.
If not found, enter the date and a value of 0

Would that work for you?

[wrayman]

Yes I am trying something like that, but not yet in VBA

RDNG: IIf([Reading] Is Null Or [Reading]="","N/A",[Reading])

Doing that in a query, but haven't gotten it to work fully yet for automatic input, will have to do some reading on how to implement in VBA

Thnanks

[wrayman]

Here is the issue of the "missing" data

1.) The issue isn't the value is null, the issue is the record isn't in the database unless it is manually entered.

2.) Sometimes there are more than 3 records of a given date

The report that I have will not display the information if there is less than three , and will only display 3 when there is more than 3, back to a nightmare or a report

[ssanfu]

I changed the form and report record source. I created a new query "QryGlucoseReadings". In the form, clear the filter, then look at dates 9/5, 10/2 & 10/6 for the evening readings.

So, there MUST be date record in the table for it to show up in the form/report. Look in the table at the date 10/7. I entered just a date. Then look at the form/report.

Then I added a 2nd evening reading for 10/5. Yep... look at the form (set a filter on start & end dates of 10/05/2016)



What do you think???

[wrayman]

Very well done! Very impressive

Pushing the envelope here maybe but is there a way when the record repeats to NOT display the duplicate information (see PDF for example)

I am 95% complete thanks to ssanfu/Steve



example.pdf