Only showing related that are most current

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Another basic question. I have a one-to-many relationship between an Issue/Task table and an Update table. I want a query to show me all records from Issue/Task, together with the most current record from the Update table that relates to each

I'm really confused by the Field Properties area of the Query Design view and how these elements relate to each other. If I click the Total button the Group By row appears. It's confusing how that relates to each field and the fields around it

Also, another general question. If you have a Type field and you want to group all records by Type, is that a function of the report layout or the guery

Really basic stuff, sorry

Thanks.
 
Without field or table names this example may not help, but here is a rough
draft of one SQL statement that could work.

SELECT *
FROM IssueTask as I INNER JOIN Updates as U
ON I.PrimaryKey = U.RelatingKey
WHERE U.SomeDateField =
(SELECT Max(Tmp.SomeDateField)
FROM Updates as Tmp
WHERE Tmp.RelatingKey = U.RelatingKey)

That assumes that you have a field in the update table that tells you which
record is the most current record for each Issue/Task

I'll skip the totals button and how it works, as the time to fully explain it
would be too long for me in this forum.

Third question: Group...Type...

In a query when you group by type, the records are combined on the field(s) you
group by. So, if you

Group by Product and Sum the Amount you will get ONE line for each product and
the sum of the Amounts for that product. That would answer a question like how
many dollars worth of "The Last Hostage" did we sell.

In a report, group by organizes the data into groups and allows you to have
area(s) to also aggregate the data. For instance.

Detail Listing (Sale Price, Name of Book, Customer Name)
$22 The Last Hostage Bob (Normal price
$20 The Last Hostage John
$15 The Last Hostage Alice
 
Wow ... way over my head. Is there any way to do this without writing SQL code directly? This is a Date field that is created on each record. I want to be able to grab the most current one

I understand your explanation of grouping. Thank you
----- John Spencer (MVP) wrote: ----

Without field or table names this example may not help, but here is a roug
draft of one SQL statement that could work

SELECT
FROM IssueTask as I INNER JOIN Updates as
ON I.PrimaryKey = U.RelatingKe
WHERE U.SomeDateField =
(SELECT Max(Tmp.SomeDateField
FROM Updates as Tm
WHERE Tmp.RelatingKey = U.RelatingKey

That assumes that you have a field in the update table that tells you whic
record is the most current record for each Issue/Tas

I'll skip the totals button and how it works, as the time to fully explain i
would be too long for me in this forum

Third question: Group...Type..

In a query when you group by type, the records are combined on the field(s) yo
group by. So, if yo

Group by Product and Sum the Amount you will get ONE line for each product an
the sum of the Amounts for that product. That would answer a question like ho
many dollars worth of "The Last Hostage" did we sell

In a report, group by organizes the data into groups and allows you to hav
area(s) to also aggregate the data. For instance

Detail Listing (Sale Price, Name of Book, Customer Name
$22 The Last Hostage Bob (Normal pric
$20 The Last Hostage Joh
$15 The Last Hostage Alic
----------------------------
Summar
$57 3 Copie
----------------------------
$18 Smart Mobs Ronal
...

So they are related, but different things with the same name


Deb wrote
 
You can do this same thing by stacking queries.
and the
First query gets the Latest update date for each related update field using the
query grid

Again with no fields or tables known.

Put the update table in the query grid
Select the relating field and the date field
In the totals row. Select group by for the relating field and Max for the date field.
Save the query.

Open a NEW query
Select the IssueTask table and the query you just saved

Create a join between the Primary Key of the Issue task table and the related
field in the query.
Select the join line and double click on it. Select the option that says All
issue task records and only matching Updates fields.
Select the fields you want to display.
 
Thank you, John. I think I get it. I'm going to print your email and try it when I get home.

Sorry about leaving out the names. I had typed a reply message with all the pertinent info, sent it, but it never showed up. I just retyped with the bare minimum of information

I believe this is it. If not, I'll reply with the information you requested

Thanks again.
 
Back
Top