Getting only the last

[Bilbo_Baggins_Esq]

Opps, I see I was a little late.

I share Brian's concern entirely, except that by definition, there is no such thing as a humble opinion (teehee).

8^)

 

[Mariot]

You are right, but is also a little severe also...

Ok, I tried filtering by date instead of using the ID, using Bilbo model.

It works if I use this:

The first column should be the unique store identifier
Field: idBusiness
Table: tblInspection
Total: Group By
Sort: (blank)
Show: (CHECKED)
Criteria: (blank)

Second Column: inspection date
Field: InspDate
Table: tblInspection
Total: Max
Sort: Ascending
Show: (CHECKED)
Criteria: (blank)

The third column should be the criteria field:
Field: Open
Table: tblInspection
Total: Where
Sort: (blank)
Show: (UNCHECKED)
Criteria: “open"

That works just fine. The problem is that as soon as I add as a four column the InspectionID (so I can work with it in the seccond query), it starts showing me all inspection. The "MAX" doesn´t work no more (and BTW, that is what was happening to me later when I said that "MAX" didn´t work).

So, there is a way to add the INspectionID in that query without messing it up?

 

[Mariot]

You do post a valid concern of which I was not aware was a possibility in your processes.

You really should either build in a protection that either:
Data can not be entered out of date sequence, or change the filtering.

"for what I need right now" is BAD DBA language. (totally wrong viewpoint)

Judging by earlier posts, your date and time are in seperate fields.
Isthat correct?


Or, is the only real problem that there could be:
1 or more unsuccessful AND 1 successful inspection on the same day?

Yes, date and time are in two separated fields. Time is a range, i.e. "from 23:00 to 01:00"

Is there a possibility there could be TWO successful inspections on the same day?

EDIT: forgot what I just said. The possibility of more than two success inspection in one day exist, I just check. I thought it was the other way around.

 

[Mariot]

And about me being a bad DBA : this thing wasn´t working at all so I was happy with something that worked partially. But I mean not that reckless, I try to respect the all the normal forms and other Access commandaments lol.

 

[Brianwarnock]

I didn't say that you were a bad DBA, but what was being proposed was bad practice.

You infact you do not have a Time field but a text field giving a range of times, presumably this is for a reason, but also presumably 2 inspections will not take place at the same time, and if they did I think this will work.

Create a date/time field in a query this will look like this
datetime:[datefield] + timeserial(Mid(yourtimefield,6,2),0,0)

This takes the date the inspection starts and the hour it starts to create a datetime field which you can MAX on. The start time is used as the end time may be in a new day. I am also assuming the format "from hh:00 etc.

To understand what I have done, if it is not obvious, lookup TimeSerial and Mid in VBA help.


Brian

 

[Bilbo_Baggins_Esq]

I suspect that Brian was referring to me, and my proposal.

However, again, I had no indication based on the prior posts, that inspection records could be added out of date sequence.
The proposed method would be fine if all records were in always in date sequence, and agree that lacking that protection, the proposed method would be bad practice in that the possiblitiy would exist for it to output wrong data.

Additionally, while I did make reference to your statement, I was not necessarily stating that you're a bad DBA, but that the stated approach to a partial, or potentially flawed solution is contrary to the normally preferred reason for having a database in the first place (e.g. accurate data).

This being said, of course, Brian is completely correct in that your time field is in fact a text field, and IMO problematic.
Even using your example an immediate problem can be seen.

"from 23:00 to 01:00"

Well, the actual date for 23:00 is different from the actual date for 01:00.
This is not without solution, but is problematic in that it takes extra steps to resolve it.

Is there any standard "time window" that is applied?
You show a 2 hour window, is that standard?
Using your example, is it possible that two records could exist such as

7/31/2007 > From 23:00 to 01:00
7/31/2007 > Trom 23:00 to 23:30

 

[Brianwarnock]

I suspect that Brian was referring to me, and my proposal.

I was referring to nobody, only the bad practice, I do not know either of you and would not therefore judge you, even the best can occasionally do things that are not quite correct, i.e. we all make mistakes, and after 40+ years in IT I've made most of them.

However apologies if I offended anybody.

Brian

 

[Mariot]

Is there any standard "time window" that is applied?
You show a 2 hour window, is that standard?
Using your example, is it possible that two records could exist such as

7/31/2007 > From 23:00 to 01:00
7/31/2007 > Trom 23:00 to 23:30

No, there can´t be superpose inspections. Well, they can enter superposed time (it was to difficult to block that), but they shouldn´t.

I should be something like

7/31/2007 > From 23:00 to 23:30
7/31/2007 > From 23:45 to 01:54
7/31/2007 > From 02:10 to 03:45

and there is the other problem: day of work starts at 18:00.


Yes, the time fields could be taken as text fields where you enter a range or a start time in the firt one.

 

[Brianwarnock]

Are you seriously telling us that
7/31/2007 > From 02:10 to 03:45
actually took place on 1st August ?

I think that the database should have included start date/time and end date/time fields, then the Last inspection could have been found easily. You would presumably look at the end date

To get the information for a working day, I assume 30th july means 30th july18:00 to 31st July17:59

criteria Between (cdate(format([datefield],"dd/mm/yy")) +#18:00:00#) And cdate((Format([datefield],"dd/mm/yy"))+1 +#17:59:59#)

sorry your format is "mm/dd/yy"

Brian

 

[Mariot]

Are you seriously telling us that
7/31/2007 > From 02:10 to 03:45
actually took place on 1st August ?

Exactly =D

I think that the database should have included start date/time and end date/time fields, then the Last inspection could have been found easily. You would presumably look at the end date

To get the information for a working day, I assume 30th july means 30th july 18:00 to 31st July17:59

criteria Between (cdate(format([datefield],"dd/mm/yy")) +#18:00:00#) And cdate((Format([datefield],"dd/mm/yy"))+1 +#17:59:59#)

sorry your format is "mm/dd/yy"

Brian

Where do I apply or use that criteria?

Thanks again Bryan

PS: what about what I said in post #22?

 

[Brianwarnock]

As each inspection will haveits unique ID as soon as you include that you will get all records. You may remember that on a post I think on your other thread I explained that MAX is a field not a record operator, therefore the technique for using it always involves 2 queries, although because of the your requirements I believe that it would take 3.

1 Filter out all none required records by not open or whatever else disqualifies the record.

2 with that data select business ID and Max date, you would group on businessID, this information is the unique key to the records that you require so

3 join query2 to the table or tables on these two fields and select any further information required

When you run query3 you should now get the data you require.



The criteria I mentioned is not required for this exercise, I was just saying how you could get information for a particular day, although I suppose that you may have to apply something to the date returned on query3 to give your workingday date.

Its late here and i'm getting tired but I assume something like
inspectiondate:IIf(cdate(format([datefield],"hh:nn:ss"))<#18:00:00#,[datefield]-1,[datefield]) you may want to wrap a format round those dates to give what you want, but i think I may be losing the plot.

By the way you mentioned at one point that English was not your first language but you are doing great.

I'm off to bed shortly so logging off now will get back on tomorrow.

Brian

 

[Mariot]

I´m also leaving, so I´ll try that tomorrow.

About the english, I´m know quite enough, I studied at school, assisted at english courses, read a lot in english, listening a lot, etc. But I´m not that confident when it comes to writing.

It´s like I´m trap in certain structures and is hard to write fluently and with style.

With this thread I´m training that I think lol.

I´m use to post in forums, but not in english, so it was a little weird.

Well, again thanks!

 

[Brianwarnock]

Decided that I was a bit presumptious yesterday in thinking that you could redesign your data base. I have created a very small database which illustrates, I hope the way I think you need to tackle this.

Query1 builds a file which is all of the inspection table plus an end date/time field. It looks at the hours of start time of an inspection and if that is <18 adds 1 day to the inspection date.
the second query does the filtering and finding the max datetimr and the third, which of course is the one you run, joins this information to the first qury and the business table to get the other info.

Brian

 

[Brianwarnock]

Hi again,

realised that when I switched from using Start time to using End time I forgot to allow for an inspection running over midnight. Kicked brain into gear and wrote a function.

Still think redesigning data base to carry start and end date/time fields is best but hey, perfection cannot always be had.

To use function code, with correct field names

enddatetime:fgetenddatetime([inspectiondate],[fieldtime])

copy and paste the code below into a code module

Function fgetenddatetime(inspdate As Date, fldtime As String) As Date
Dim truedate As Date
truedate = inspdate
starttime = TimeSerial(Mid(fldtime, 6, 2), Mid(fldtime, 9, 2), 0)
endtime = TimeSerial(Mid([fldtime], 15, 2), Mid([fldtime], 18, 2), 0)

If starttime < #6:00:00 PM# Then truedate = truedate + 1
'   This allows for midnight to 17:59 period
If endtime < starttime Then truedate = truedate + 1
'   This corrects for the after midnight finish
' Ok the above does not allow for greater than 24 hour inspection
' but nor does the database

fgetenddatetime = truedate + endtime

End Function

Brian