Multi-table grouped query ungrouping - don't know why

[Anchoress]

I have a simple contact management db with stores in the parent table and
individual contacts with salespeople in the child table. I wanted to create a
query that would show each store with the most recent contact.

I created a successful grouped query that used the "max" feature to show the
date of the most recent contact for each store, but when I tried to add more
info from the child table, the grouping turned off.

I am sure that my question has been asked and answered multiple times, and
if someone would just steer me to where I can find the answer that would be
great. Below is a list of the fields in the successful query:

Salesperson (Parent table, text)
Company Name (Parent table, text)
Company Address (Parent table, text)
Notes (Parent table, memo)
MaxOfP/V Date (Child table, date)

And here's what I'd like to add:

P/V (Child, text)
By (person) (Child, text)
Purpose (Child, text)
Comp'd? (Child, y/n)
Ordered? (Child, currency)
Product gifted (Child, text)
F/U action (Child, text)
F/U date (Child, date)
Notes (Child, memo)

 

[MGFoster]

Supposedly JET (Access) SQL can't GROUP on a Memo field.

 

[John Spencer]

The grouping didn't turn off. You just have more fields determining the
group and therefore more groups. The max gets calculated for the group.

You need a subquery to get the Max Date.

The best I can do with the information you have given is a generic
sample of what the query might look like.

Assumption: There is one field in the Parent table that uniquely
identifies each record (primary key) and in the Child table there is one
field (foreign Key) that identifies which record in the Parent table the
child record is associated with.

SELECT P.*, C.*,
(SELECT Max([P/V Date] FROM Child as C2
WHERE C2.ForeignKey = P.PrimaryKey) as MaxDate
FROM Parent INNER JOIN Child
ON P.PrimaryKey = C.ForeignKey

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John Spencer]

Whoops. SQL should read more like the following. I forgot to alias the
table names in the FROM clause of the main query.

SELECT Parent.*, Child.*,
(SELECT Max([P/V Date] FROM Child as C2
WHERE C2.ForeignKey = Parent.PrimaryKey) as MaxDate
FROM Parent INNER JOIN Child
ON Parent.PrimaryKey = Child.ForeignKey


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

The grouping didn't turn off. You just have more fields determining the
group and therefore more groups. The max gets calculated for the group.

You need a subquery to get the Max Date.

The best I can do with the information you have given is a generic
sample of what the query might look like.

Assumption: There is one field in the Parent table that uniquely
identifies each record (primary key) and in the Child table there is one
field (foreign Key) that identifies which record in the Parent table the
child record is associated with.

SELECT P.*, C.*,
(SELECT Max([P/V Date] FROM Child as C2
WHERE C2.ForeignKey = P.PrimaryKey) as MaxDate
FROM Parent INNER JOIN Child
ON P.PrimaryKey = C.ForeignKey

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a simple contact management db with stores in the parent table
and individual contacts with salespeople in the child table. I wanted
to create a query that would show each store with the most recent
contact.

I created a successful grouped query that used the "max" feature to
show the date of the most recent contact for each store, but when I
tried to add more info from the child table, the grouping turned off.

I am sure that my question has been asked and answered multiple times,
and if someone would just steer me to where I can find the answer that
would be great. Below is a list of the fields in the successful query:

Salesperson (Parent table, text)
Company Name (Parent table, text)
Company Address (Parent table, text)
Notes (Parent table, memo)
MaxOfP/V Date (Child table, date)

And here's what I'd like to add:
P/V (Child, text)
By (person) (Child, text)
Purpose (Child, text)
Comp'd? (Child, y/n)
Ordered? (Child, currency)
Product gifted (Child, text)
F/U action (Child, text)
F/U date (Child, date)
Notes (Child, memo)

 

[Anchoress]

Thank you for all the replies. I don't know SQL, but I will carefully examine
the responses and try my best to apply them to my problem.

 

[Pete D.]

When I was a beginner in Access, last week, I made some simple queries in
the query grid and flipped back and forth to SQL/Grid view. After a few
times of seeing it both ways I became very dangerous. Now I go back and
forth as grid saves me typing and SQL will let me see the whole picture.
Sorry for going a little of topic, just a learning tool.
Pete D.