Need to do grouping in a query

[Pat DiPersia]

Am trying to setup a query to handling some odd grouping.

For example, I have two records with many similar fields, for example, we'll
say "Date", "Domain", "Server Name", "Free Space." Domain and server name
stay static, but date and free space changes. I'd like to leave historic
data in the table, but setup a query to only show me the latest date
information.

Now for the hard part - this table contains many different domains and
servers that are checked on various dates. In the query, I'd like to see
all servers with their most recent information. This is where I'm coming up
short.

Thanks for any help!

 

[John Vinson]

Am trying to setup a query to handling some odd grouping.

For example, I have two records with many similar fields, for example, we'll
say "Date", "Domain", "Server Name", "Free Space." Domain and server name
stay static, but date and free space changes. I'd like to leave historic
data in the table, but setup a query to only show me the latest date
information.

Now for the hard part - this table contains many different domains and
servers that are checked on various dates. In the query, I'd like to see
all servers with their most recent information. This is where I'm coming up
short.

A Subquery is the ticket here. Create a query based on your table (ps:
you may want to change the name of the field Date if that's what
you're using, it's a reserved word!) Put a criterion on the date field
of

=(SELECT Max([date]) FROM yourtable AS X WHERE X.[Domain] =
yourtable.[Domain] AND X.[Server Name] = yourtable.[Server Name])


John W. Vinson[MVP]

 

[Pat DiPersia]

PERFECT - worked great, thanks!

No worries on the date/time fields - using ReportDate and ReportTime.
Thanks for the heads up.

Am trying to setup a query to handling some odd grouping.

For example, I have two records with many similar fields, for example, we'll
say "Date", "Domain", "Server Name", "Free Space." Domain and server name
stay static, but date and free space changes. I'd like to leave historic
data in the table, but setup a query to only show me the latest date
information.

Now for the hard part - this table contains many different domains and
servers that are checked on various dates. In the query, I'd like to see
all servers with their most recent information. This is where I'm coming up
short.

A Subquery is the ticket here. Create a query based on your table (ps:
you may want to change the name of the field Date if that's what
you're using, it's a reserved word!) Put a criterion on the date field
of

=(SELECT Max([date]) FROM yourtable AS X WHERE X.[Domain] =
yourtable.[Domain] AND X.[Server Name] = yourtable.[Server Name])


John W. Vinson[MVP]

 

[Pat DiPersia]

OK, while that did work pretty well, I'm running into another snafu.

Especially during testing, and for sure in production, I'm running into
situations where some of the systems are being scanned more than once in a
day, as well as on different days of the week across all of the systems. I
tried to adapt the query given earlier, but no luck - no matter what, I miss
some systems in my query.

Any advice?

Am trying to setup a query to handling some odd grouping.

For example, I have two records with many similar fields, for example, we'll
say "Date", "Domain", "Server Name", "Free Space." Domain and server name
stay static, but date and free space changes. I'd like to leave historic
data in the table, but setup a query to only show me the latest date
information.

Now for the hard part - this table contains many different domains and
servers that are checked on various dates. In the query, I'd like to see
all servers with their most recent information. This is where I'm coming up
short.

A Subquery is the ticket here. Create a query based on your table (ps:
you may want to change the name of the field Date if that's what
you're using, it's a reserved word!) Put a criterion on the date field
of

=(SELECT Max([date]) FROM yourtable AS X WHERE X.[Domain] =
yourtable.[Domain] AND X.[Server Name] = yourtable.[Server Name])


John W. Vinson[MVP]

 

[John Vinson]

OK, while that did work pretty well, I'm running into another snafu.

Especially during testing, and for sure in production, I'm running into
situations where some of the systems are being scanned more than once in a
day, as well as on different days of the week across all of the systems. I
tried to adapt the query given earlier, but no luck - no matter what, I miss
some systems in my query.

If you're storing three scans, all with the same date, without a time
portion, you will indeed get ambiguous or wrong results. What's in the
fields of the records which are being found, and what's in the fields
of the records being missed? Your origial post asked for *the most
recent* scan; if you want all the scans in the past week that's a
different query.

John W. Vinson[MVP]

 

[Pat DiPersia]

We are indeed including a time stamp as well. We still want the most recent
scan based on date and time. Typically, there will only be one scan in a
day, but sometimes not. When we had a few in one day, we received all times
for that day, which was the most recent day - thus the query was running
correctly, but we wanted to limit it to the most recent time as well.

When I adapted your select statement to the time stamp (And leaving it in
the date field as well), I was not receiving records from the current date,
only records from the previous day. Not sure why that was.

Does that help?

 

[John Vinson]

We are indeed including a time stamp as well. We still want the most recent
scan based on date and time. Typically, there will only be one scan in a
day, but sometimes not. When we had a few in one day, we received all times
for that day, which was the most recent day - thus the query was running
correctly, but we wanted to limit it to the most recent time as well.

When I adapted your select statement to the time stamp (And leaving it in
the date field as well), I was not receiving records from the current date,
only records from the previous day. Not sure why that was.

Does that help?

Please post the SQL of your current query.

John W. Vinson[MVP]