Getting only the last

[Mariot]

I need to build the following query:

I have 2 tables:

tblBusiness
tblInspection

tblShops have an ID, an adress, bussines name, owner name, etc
every Shop can be inspected many times. A inspection can be futile if the store is closed. If the store is open, the inspection is made, and as result with can have “with irregularities” “without irregularities”. Two or more inspections can be made the same day (if the n – 1 time the store was close).

So, here is the basic select:

SELECT tblBusiness.idBusiness, tblBusiness.BussinessName, tblInspection.InspectionDate, tblnspeccion.InspeccionTime, tblInspection.Open, tblInspection.Irregularity
FROM tblBusiness INNER JOIN tblInspection ON tblBusiness.idBusiness = tblInspection.idBusiness;

What I need is to get the LAST inspection where the bussines was open and with irregularies. One inspection per Business, but of all business. Ah, and if there ir a newer inspection but the business was close, it doesn´t count.

I tried “Group by MAX” with InspectionDate, but it just didn´t work. For example, it showed me two inspection of the same business, not “the last” of that bussiness (it was easy to filter the Open field and the Irregularity field).

I suspect that what I need to solve this is to use two or more SQL stament, two in one., something like that. But I´m in a hurry and I just can seem to get it right.

I hope you can help me with this. Thanks for reading anyway.

P.S.: the fact that there can be more than one inspection per day can be a bitch =(

but there is a "InspectionTime" that can save that". Mmm the problem is that one inspection can be made 1 july around 20:00, but the seccond on 2 july 01:00. But we store the two inspections in 1 july... so... that mens problem.

But that issue is not so important as the previous problem up there (well, in order to solve the seccond I need to solve the firts...)

 

[Banana]

I believe you can do a SELECT TOP X, and if you reverse the sort order, you'll get the last X records.

Alternatively, there's also Last() function, but I think that only returns one record.

 

[Mariot]

Mmm I don´t think that´s going to work =(

 

[Banana]

Explain why it didn't work, instead of saying it's not going to work. A bit hard to give help if we don't know exactly what you are after.

Also, if you only want one business per inspection, have you tried SELECT DISTINCT?

 

[Brianwarnock]

Firstly do not use Last it retrieves the last record input which may not be what you want.
Part of the problem may be having separate Date and time fields, with one field containing date and time you merely need to select max on it.

Your Totals query would group on Id, Business name, where for Open and irregular filtering, and max on the Date/Time field. If you cannot change the design construct a Date/Time field by concatenation.

Brian

 

[Brianwarnock]

Thinking about it I'm not convinced that that will work, I think that you may need 2 queries.
1 to do the filtering
2 to now select the max date/time from the filtered list.

Brian

 

[Mariot]

Explain why it didn't work, instead of saying it's not going to work. A bit hard to give help if we don't know exactly what you are after.

Also, if you only want one business per inspection, have you tried SELECT DISTINCT?

Sorry, you are right.

I have tried your solution before posting. But english is not my native language, sometimes I find hard to express things here (I´m not used to it). So I chose to give that answer instead.

The thing is that the same thing continue to happening.

I think SELECT DISTINCT could work, but I´m trying it right now and I just can´t make it work... I´m stuck...

Thanks for your time ^_^

 

[Brianwarnock]

Note that MAX is a field function not a record function, therefore you cannot Max date and max time to get the Max date/time

for exemple if 2 records had

Date Time
1 Jul 16:00
2 Jul 11:00

Max on the 2 separate fields would return 2 Jul 16:00 that is why you need 1 field

Brian

 

[Mariot]

Thinking about it I'm not convinced that that will work, I think that you may need 2 queries.
1 to do the filtering
2 to now select the max date/time from the filtered list.

Brian

Right now that´s what I have, one query that filter "open" and "with irregularities"; and the other for the "max date".

Thank you Brian

 

[Brianwarnock]

Happy to help

Brian

 

[Mariot]

I have to study the SELECT DISTINT thing... I think it could help. I never used it before.

Something like this may help:

SELECT
client name,
worker,
`time`
FROM
yourtable
WHERE
`time` IN
(SELECT
MAX(`time`)
FROM
yourtable
GROUP BY
client name)

(I saw it here http://forums.devarticles.com/gener...electing-max-date-for-many-records-15388.html )

But, again, I don´t know how to take it to my proyect.

 

[Banana]

just add "DISTINCT" to right of SELECT...

SELECT [color=red]DISTINCT[/color]
client name,
worker,
`time`
FROM
yourtable
WHERE
`time` IN
(SELECT
MAX(`time`)
FROM
yourtable
GROUP BY
client name)

Note: I'm not sure if you want distinct for client name or from the subquery where you are doing max. Exerpiment with it.

 

[Brianwarnock]

This lookks like it's using a sub query, I'm not good on those I always used the 2 query approach.
I did think from an eraler post that it was now working, and I still believe that you will not get a correct result unless you put the date and time together in one field.

I'm sorry but I will be signing out shortly I hope that you resolve your problems.

Brian

 

[Mariot]

It´s ok, I don´t think I´m gonna solve it today, so I will take it again monday.

Bananas, I tried adding DISTINCT after SELECT, but it didn´t affect the result... so I wonder if there is another way of using the DISTINCT... I mean, how do SQL know where he has to apply the DISTINCT, in wich field (if there are more than one).

 

[Banana]

Distinct affects the whole record, not individual field.

I'd probably try what Brian suggest:

LastInspection: [Date] & [Time]

sorted descending. See if that works.

 

[Mariot]

"Time" consist on two fields, it´s a range really. And the problem is that the "day" doesn´t start at 00:00, so, I don´t know.

But, I think that the lesser of my problems... I just can´t filter the inspections and get the one I want.

I think the firts step is to get Access to give me the last inspection of every Business. Anybody know how to do that?

The seccond step is changing that a bit and doing that filtering with the desired criteria ("open" and "with irregularities").

Later on I can try to insert the problem of two inspection the same day.

What do you people think?

Anyway, thank you all for reading and helping

 

[Bilbo_Baggins_Esq]

I'm sorry, I did not realize this was a duplicated topic.
Here is the post I made in the other thread:

I think what you're needing is to create one query based off the inspection table.
This should be a GROUP BY query

The idBusiness shouldbe the ONLY group by column
There should be an idInspection column in the Inspection table and that should be set to MAX
Now just add your criteria and sent them to WHERE. In your case by whatever value you use to define a successful inspection.

Then, you can join this query to the Business table off the idBusiness column, and to the Inspection table off the idInspection column via another query(s).

Is this what you're looking for?

If your Inspection table does not have an ID column, you might want to consider adding one.


And your recent reply from the other thread:

Every inspection have an ID, and is linked to a Business or Store.

I tried the MAX in the query but it doesn´t do a thing =(

Now, I'll expound on my initial post from the other thread.

You need to make two SEPERATE QUERIES.

The first simply finds the MAX Inspection_ID for each location in the tblInspection TABLE
This first query should be a TOTALS Query (group by)(with a new query open in design view, click the “Totals” button on the tool bar

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

The second column should be the Inspection ID field:
Field: idInspection
Table: tblInspection
Total: Max
Sort: Ascending
Show: (CHECKED)
Criteria: (blank)

The third column should be the criteria field:
Field: Irregularity
Table: tblInspection
Total: Where
Sort: (blank)
Show: (UNCHECKED)
Criteria: “without irregularities”

Save this query. We’ll call it “Max_Regular_Inspection_ID”

Now, make your main query to bring together data from the tblInspection and tblBusiness tables JOINED to the newly created “Max_Regular_Inspection_ID” query.

JOIN the column (tblInspection.idInspection) to the column (Max_Regular_Inspection_ID.MaxofidInspection)
JOIN the column (tblBusiness.idBusiness) to the column ((Max_Regular_Inspection_ID.idBusiness)

Now you can drag whatever column you want from each table into the query structure and view it all together, including the inspection time, business address, etc.

Try this and let me know how that works for you.

 

[Mariot]

Wooooo that works!!!!

Thank you, you good hobbit ^_^

Yes is a good and easy method of filtering the "last", working with the ID. The only problem I can see about it is the following: somebody entering a new inspection and later an old one. Because date doesn´t count, that can be the only flaw. Do you agree?

But that is some extreme case, and it´s not supossed to happend... for what I need right now, it really suites me.

Thank you all for your help and time.

PS: Bilbo I´m using your method but filtering by "open". I get the last inspection where the store was open. Then, I filter for irregularities. Really great

 

[Brianwarnock]

The only sure way to get the last is by date, anything else is a flawed design that no self respecting DBA would put up with, IMHO.

Brian

 

[Bilbo_Baggins_Esq]

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?

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

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