Grabbing only the last entered contact info based on full name selection in form

[newbieX]

This is related to previous post (https://www.accessforums.net/queries...tml#post271501) but am trying a new tack so posted new question.

I have a form called called Products

I have a combo box called cboOrganization on this form. It is a list of distinct organizations in the table Orders.

I also have a combo box called cboFullName. I want the record source to be all the distinct names that are associated with the organization selected on the cboOrganization. Distinct names is determined by the latest entry in the database. Since there is an autonumber field (ID) the last value can be used as a filter.

The problem I am having is grabbing the the associated contact data with this single record and filling in the other other text boxes below cboFullName with these values.

Based on attached database, if the user selects Organization XYZ, there should only be 3 names associated with it the returned query results for cboFullName's record source: John Wayne, Gerri Smith, and Liz Jones. The information associated with these names should be as follows (as these are their last entries while working for company XYZ)

John Wayne, Kalamazoo, MI, Asso. Manager
Gerri Smith, Kalamazoo, MI,Manager
Liz Jones,Chicago,IL,Sales



I am getting all unique instances of these names and their associated contacts instead of the last instance only. How can I filter out the previous entries for those records matching fullname for company XYZ?

Earlier records that show up but I don't want to might look like this:

John Wayne, Kalamazoo, MI,Sales
Gerri Smith, Detroit, MI, Sales
Gerri Smith, Kalamazoo, MI, Asst. Manager

[ssanfu]

How can I filter out the previous entries for those records matching fullname for company XYZ?

How are you determining what the "latest" entry is?

Distinct names is determined by the latest entry in the database.

There is no date field or scheme to determine "position" order.


Tables do not have an inherent order - a table is a "bit bucket". Queries are used to impart order (sorting).

[newbieX]

I was using a date field (see previous post referenced above) but occasionally two orders from the same person occurred on the same date so I went with the Autonumber "ID" since the latest entry will have the highest ID number. My thinking was if I sorted the table by ID in ascending order, the last record snagged based on name selection would also be their latest entry. The SQL for the cboFullName row source is as follows (derived from query builder):

SELECT Orders.FullName, Orders.City, Orders.State, Orders.Position, Last(Orders.ID) AS LastOfID, Orders.Organization
FROM Orders
GROUP BY Orders.FullName, Orders.City, Orders.State, Orders.Position, Orders.Organization
HAVING (((Orders.Organization)=[Forms]![Products]![cboOrganization]))
ORDER BY Last(Orders.ID);

[June7]

For one possible method, review http://allenbrowne.com/subquery-01.html#TopN

or

SELECT * FROM table WHERE ID IN (SELECT Max(ID) AS MaxID FROM table GROUP BY Organization) AND [Organization] = [Forms]![Products]![cboOrganization];

[ssanfu]

......Autonumber "ID" since the latest entry will have the highest ID number........

Yeah... well, maybe... but maybe not.

See Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

See the first #5 and Note #1.

[newbieX]

For one possible method, review http://allenbrowne.com/subquery-01.html#TopN

or

SELECT * FROM table WHERE ID IN (SELECT Max(ID) AS MaxID FROM table GROUP BY Organization) AND [Organization] = [Forms]![Products]![cboOrganization];

Still not getting anywhere. I can select distinct names but cannot carryover any of the other associated information without gaining records (as I add visible fields to the query, the number of distinct records increase. I do not know how to grab all of the below information and just this information based on the conditions described in posting above. I see it as basically a re-query or a subset query but can't figure out solution???

John Wayne, Kalamazoo, MI,Sales
Gerri Smith, Detroit, MI, Sales
Gerri Smith, Kalamazoo, MI, Asst. Manager

BTW, do uploaded files (like my Orders.accdb) get looked at and modified with working solutions and re-uploaded? I was sort of hoping this was the case when I saw that I could upload my database but perhaps not. I just want to know what is realistic to expect. I am always grateful for whatever help I get and try not to make unreasonable requests or have unreasonable expectations.

[newbieX]

Yeah... well, maybe... but maybe not.

See Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

See the first #5 and Note #1.

Good point. Thanks. As soon as I figure out how to do the above, I will change the filter to use a data field instead of a autonumber field.

[June7]

AFAIK, uploaded files are not modified. Each upload is independent of previous, even if the uploaded file has same name.

I thought you wanted only the latest entry for an organization? The query I suggested does that.

If you want the latest entry for each manager in each city for each organization, include more GROUP BY criteria in the nested subquery. However, I cannot get the output posted. Why does the example show Gerri Smith, Asst. Manager when later record is Gerry Smith, Manager? Exactly what rule do you want to apply?

[ssanfu]

Are you saving the position (Sales, Asso. Manager, Manager) as text or is there a FK to the position?

If there was a table with two fields

PositionID_PK (Long)	PositionName (Text)
10	Sales
20	Asso. Manager
30	Manager

John Wayne, Kalamazoo, MI, 20 (Asso. Manager)
John Wayne, Kalamazoo, MI, 30 (Manager)

Then you could pick the Max PositionID_PK using a total query.




---------------------------
I used 10, 20, 30 so a new position could be added without messing up the numbering. Say you wanted to add a Dept. Manager that was above Asso. Manager but under Manager, you would have

PositionID_PK (Long)	PositionName (Text)
10	Sales
20	Asso. Manager
25	Dept. Manager
30	Manager

[CJ_London]

I agree the comments re autonumber but you have a few things wrong with your subquery - you need to alias it (to distinguish it from the main query table) and you need to limit it to only look at the relevant records for that organisation. You also don't need to alias the value you are returning - or group it

SELECT * FROM table WHERE ID IN (SELECT Max(ID) FROM table AS T WHERE organisation=table.organisation) AND [Organization] = [Forms]![Products]![cboOrganization];

[newbieX]

AFAIK, uploaded files are not modified. Each upload is independent of previous, even if the uploaded file has same name.

I thought you wanted only the latest entry for an organization? The query I suggested does that.

If you want the latest entry for each manager in each city for each organization, include more GROUP BY criteria in the nested subquery. However, I cannot get the output posted. Why does the example show Gerri Smith, Asst. Manager when later record is Gerry Smith, Manager? Exactly what rule do you want to apply?

Sorry it is so complicated. What I am looking for is the list of the latest contact information for each employee from whatever organization is selected in the combo box "cboOrganization". This list comprises the values displayed in the cboFullName box. It is determined using a query in its row source property. Because I autopopulate the contact information in the other text boxes on the form based on the cboFullName selection, I am making a multi-column list for the cboFullName combo box (6 columns in total). I use VBA in the afterupdate event that assigns Me.City = Me.cboFullName.Column(3-or whatever) for example. Herein lies the problem. I cannot get these values in the cboFullName combo box to return just these names and their correct associated contact information.

John Wayne, Kalamazoo, MI, Asso. Manager
Gerri Smith, Kalamazoo, MI,Manager
Liz Jones,Chicago,IL,Sales

Also, based on what was said about Autonumber, if I go back to using DataOrdered (which is my preferred way of doing it) as the field to determine the latest entry, how can I avoid duplicate fullname listings if the person made two orders on the same day?

Oh my head is spinning.........

[June7]

This query almost generates the output example in your latest post for organization XYZ:

SELECT * FROM Orders WHERE ID IN (SELECT Max(ID) AS MaxID FROM Orders GROUP BY Organization, FullName) AND [Organization] = [Forms]![Products]![cboOrganization];

However, John Wayne latest record shows Detroit.

I know that in theory autonumber can be negative but I have never seen that.

Would multiple orders in same day by any employee have different contact info? If not, SELECT DISTINCT or GROUP BY should eliminate duplicates.

[newbieX]

This query almost generates the output example in your latest post for organization XYZ:

SELECT * FROM Orders WHERE ID IN (SELECT Max(ID) AS MaxID FROM Orders GROUP BY Organization, FullName) AND [Organization] = [Forms]![Products]![cboOrganization];

However, John Wayne latest record shows Detroit.

I know that in theory autonumber can be negative but I have never seen that.

Would multiple orders in same day by any employee have different contact info? If not, SELECT DISTINCT or GROUP BY should eliminate duplicates.

I went with OrderDate as the field to determine latest entry and modified your code as suggested and it seems to work. Now off to study what it actually does. These nested sqls are tricky. I added distinct because contact info is the same if sent on the same day. Final code is as follows:

SELECT DISTINCT Orders.FullName, Orders.City, Orders.State, Orders.Position
FROM Orders
WHERE (((Orders.OrderDate) In (SELECT Max(OrderDate) AS MaxOrderDate FROM Orders GROUP BY Organization, FullName)) AND ((Orders.Organization)=[Forms]![Products]![cboOrganization]));

As usual June7, you are the best. Thank you.