How To Find Most Recent Transaction for Customer and Display

[JewliaB]

I'm uncertain how to continue, or if it is even possible. I know the forums are generally for more specific questions, but I'm really hoping someone can point me in the right direction here. I've tried searching and help and a couple books, but I still don't know how to proceed. I have two tables in my database, one is a list of customers and the other is their various transactions with our company. I will be trying to run a report off the query and one of the things I would like to do is display a start date of their communications with us and a "most recent" date. Essentially, I would like my query to search the table of transactions for a given customer and find the most recent transaction date, then return this date to me. How would I go about this?

Thank you all in advance,

Jewlia

 

[pbaldy]

You may only want the first query here, but I'm posting the link in case you end up wanting the second.

http://www.baldyweb.com/LastValue.htm

 

[JewliaB]

I don't understand. Where do I input this information? I haven't worked a lot with filtering data.

 

[pbaldy]

You asked for a query, which is what those are. You could either paste the SQL into a blank query and adjust the table and field names or build one in the query builder, using View/Totals to get the row that will allow you to set the Group By and Max options.

 

[Mike375]

Here is something simple you can do to get the feel of it.

Make a query on the table that holds the records having transactions.

Drag down the field row the ID number field and the date of transaction field twice

So your query field line in query Design view will have

IDNumber FieldName.......TransactionFieldName........TransactionFieldName

On the toolbar you will see a strange look E, I forget the name of it, Omega or Alpha or something. Click it and you will see Group By appear under each of the field names on a new row called Totals

When you click on Group By you will see it is a drop down list. For the first
TransactionFieldName select Min and select Max for the second one and leave Group By for the ID field name. Save the query and then reopen it and will see the result.

Now go back to Query Design and then to View on the toolbar and select SQL View and that will show you what you have done and you will be able to relate to what is on Paul's website.

 

[accessaspire219]

Hi,
I am doing something similar and found your information here very helpful. Thanks!! I have 3 columns in my query -- Item Number, PO Number and Due Date, I want to find the earliest due date for each item. When I do hit the E sign (TOTAL) it displays the group by for all the columns. However, I do not want to "group by" for PO Number. Since if I do that it is giving me the minimum date per PO number per item and I want the minimum date per item only. Is there any way I can avoid the "group by" for the PO number column but still have the corresponding PO number (for the minimum date per item) displayed on the result?
Thanks.

 

[pbaldy]

Did the link in post 2 not help? It sounds like that's what you want, except Min instead of Max.

 

[accessaspire219]

Yup, the link helped! But I need the PO# to be on the MinDate query itself, since that is the only way I can link the MinDate to the Qty on the PO which is in another table. Without the PO# this crucial link is lost. Is there a way by which I can have the PO# on the MinDate query however it is not not grouped by??
Thanks.

 

[pbaldy]

I guess I'm not visualizing the data, so maybe you can post a sample db? You can try Min or Max on the PO number instead of Group By.

 

[accessaspire219]

Thanks Paul, I will try to do that. For now, I have another problem!! up until now all my create table queries were working fine. Now all of a sudden all of them give an error saying -- invalid argument. I have no idea. Can anyone throw some light on this??

 

[pbaldy]

Sure; one of the arguments is invalid.

Hard to say without seeing the query.

 

[accessaspire219]

I am pretty sure I got THAT part!! does this help --> SELECT AllItems_tbl.ITEM, COWIDE11.WERKS, COWIDE11.INTRANSIT, COWIDE11.AVG3M, COWIDE11.DEP_AVG, COWIDE11.FREQUENCY, COWIDE11.STOCK, COWIDE11.OPEN_SOS, ([STOCK]+[INTRANSIT]-[OPEN_SOS])/([DEP_AVG]+[AVG3M]) AS MIOH INTO MIOH_tbl
FROM AllItems_tbl LEFT JOIN COWIDE11 ON AllItems_tbl.ITEM = COWIDE11.MATNR;

 

[accessaspire219]

It does display the results when I select datasheet view, however, it still does not create the table in this case..

 

[pbaldy]

I don't see anything wrong offhand. Is it possible that any of the values are Null, or that the 2 items in the divisor of the calculation could total zero, resulting in a divide by zero problem?

 

[accessaspire219]

The issue was -- the mdb file exceeded 2 gb!! and now everytime i create a new mdb and run the queries i created the mdb file hits 2 gb!! :O

 

[Rabbie]

The issue was -- the mdb file exceeded 2 gb!! and now everytime i create a new mdb and run the queries i created the mdb file hits 2 gb!! :O

Have you tried Compact and repair. a DB can become very bloated when you keep trying changes to queries.

 

[accessaspire219]

Just to give you an idea, what I have is a query consisting of 6-7 columns of which PO date and material are two columns. I want to retain only the records with either blank due date or the min due date per item. I plan to do this by using group by for the material column and min for the due date. However, for all the other columns I just need it show the data corresponding to min date by material criterion. Does this help to visualize the data?? Any idea how this can be done??

 

[accessaspire219]

Hi Rabbie,
Yes, I did. However that gives me error 3001. So I just created a new mdb file. I am not sure if there is anyway I could reduce the size of the old mdb file or would I just need to delete it??

 

[Rabbie]

Hi Rabbie,
Yes, I did. However that gives me error 3001. So I just created a new mdb file. I am not sure if there is anyway I could reduce the size of the old mdb file or would I just need to delete it??

I would copy what you need from the old .mdb file to the new one. Then delete the old one once you are sure you have got everything you need from it.

 

[accessaspire219]

Thanks Rabbie. Going a step forward - If I am looking for the min date per item for a group of PO's, how can I set it to return only a single value in case there are more than one PO's with the same min date??

Thanks.