Query to Show Field Based on Last Record ID

[Adam McReynolds]

I am looking to get a query to show my list of customers "Grouped By" [CustomerName], that show only the single [CurrentBalance] field for each customer based on the "Last or Highest" [RecordID].

Also, each customer can have up to 4 different [StockType]'s but at least 1 [StockType].So my results would look like this:

[CustomerName] - [StockType] - [CurrentBalance]
Customer#1 StockType#1 5

Customer#1 StockType#2 4

Customer#2 StockType#1 5

Customer#3 StockType#1 5


Any Help would be much appreciated. Thanks.

Edit: A picture of what my results would look like has been added.

 

[Minty]

The simplest way to achieve this would be to create a query that lists the max([RecordID] for each customer ID, then link this to the Customer Stock Type query by Customer ID.

If you are stick give us the actual field names and it will be simple to construct.

 

[unclefink]

I worked my way through this exact situation yesterday; however my situation was a little more complex.

In any case, if you just want to find the highest "balance" for each individual person you will need to change your query a bit.

On your query remove the "stock item" so you only have the customer name and current balance. After you have this set up, click on the backwards "E" on the top of your page that says total on it and change "group by" to read "MAX" in the total row under the current balance column of the query.

If you want to have the stock type in the query, you will have to create an additional query that communicated with the initial query.

Check out the "qrysample.jpg" attachment.

I'm relatively new at this whole concept myself, so there may be other ways of doing this same task. If you want additional information included, you will then need to create a secondary query based on the first query.

Let me know if this helps.

 

[unclefink]

I just realized as I was responding initially, someone else has already responded; so hopefully my input is not redundant.

In any case, if you want a final report or such that shows all of the data in the table with only the "Max" balance, you will create a query that looks like the "qrysample2".

In order to create this query, build a new query and pull in the initial query, in my example titled "MaxBalancebycustomer". In addition to pulling in MaxBalancebycustomer into the new query, pull in your data table, in this example, I titled it "table1".

Link the customername from MaxBalancebycustomer to customer name in table1

Link the Maxofcurrentbalance from MaxBalancebycustomer to current balance in table1

For your query fields, show customername and Maxofcurrentbalance from the MaxBalancebycustomer and stock type from the table 1. (reference "qrysample2" attachment for illustration)

This will give you a result that looks like qrysample3.

qrysample4 is the initial data in table1. Hope this helps, if not i've had some more time practicing as this took me forever to figure out on my own with different data comprised of 1,000+ records and the information I was trying to acquire was not a number. (This is the more complex) reference I was referencing in my previous posts.

Again, if this process is redundant or there are other easier ways of reaching the same conclusion, forgive me as I'm a newby and also in the self teaching process myself.

 

[Adam McReynolds]

Thanks to Minty and UncleFink for your replies. I think I need to explain the situation better. I have an action table which is inserted into from a form using SQL Insert and VBA.

The form code basically searches for the last record from that customer and then pulls up that last [CurrentBalance] for that customer. It then adds the quantity I type on the form and SQL inserts that into a new record which shows a new [CurrentBalance].

What I need the query to do is to show the current balance for the most recent record for each customer, not the highest current balance in the table. Additionally, I need to to group by stock type, so if a customer has 2 different stock types, then I want to see the current balance for each stock type(so this is the only case where a customer would be listed more than once).

Thanks again.

 

[unclefink]

HMMMMMMM, above my level of experience i'm afraid . Sorry.

 

[Minty]

Thanks to Minty and UncleFink for your replies. I think I need to explain the situation better. I have an action table which is inserted into from a form using SQL Insert and VBA.

The form code basically searches for the last record from that customer and then pulls up that last [CurrentBalance] for that customer. It then adds the quantity I type on the form and SQL inserts that into a new record which shows a new [CurrentBalance].

What I need the query to do is to show the current balance for the most recent record for each customer, not the highest current balance in the table. Additionally, I need to to group by stock type, so if a customer has 2 different stock types, then I want to see the current balance for each stock type(so this is the only case where a customer would be listed more than once).

Can you post your table structure for the stock records. I can do some air code but it would make more sense if we see your actual data layout. Just a brief sample will be fine.