Using Dmax +1 Function From Query Total??

[Beanie_d83]

Hi all,



Is it possible/advisable to use the Dmax +1 function from the Total created from a query?

I need to increment part numbers by 1 each time a part of a particular component type is created instead of using autonumber.

SELECT tbl_component_type.Component_Type_ID, tbl_parts.Part_Number, tbl_parts.Part_ID_PK
FROM tbl_component_type INNER JOIN tbl_parts ON tbl_component_type.Component_Type_ID = tbl_parts.Component_Type_ID_FK
WHERE (((tbl_component_type.Component_Type_ID)=[forms]![Copy Of frm_component]![cboSelect_Component_Type]))
ORDER BY tbl_parts.Part_Number;

Above is the SQL for the query, so my plan is to use the total generated by this and simply add 1??

[Minty]

I can't see a total result from that query.
Why not use a autonumber - what is the problem with that?

[Beanie_d83]

Hi Minty,

When I open the query in datasheet view, I can click the totals button on the ribbon (in the records portion) which displays the totals. It is this total I want to use to generate an incremental number.

I can't use autonumber more than once in a table. I need to keep a record of all the parts we use in the manufacture of our products and these are identified by their "component type ID". Each component type (or group) have their own set of sequential part numbers so I need to generate an increment of one each time a new part of a particular component type ID is required.

[Minty]

You would have to use an aggregate query(Totals Symbol in the query designer) to get the totals displayed in the Datasheet view then use that to get your DMax() value.

[Beanie_d83]

Ok, so would I use this to count the number of same component type ID records or use the Max value of the part number? I presume the latter as this is more relevant to what I'm doing!!

[Minty]

Yes - create the query that returns the count of the item you want, then use either DLookup() to return that value and add one, or if you can simply use a DMax() you might do it a bit quicker.

[Beanie_d83]

Right well so far so good! I now have the below code for the part number count which is returning the right results. I have noticed though that when the query runs, the first row in data sheet view has no ID number and a count of 1 which I find confusing. How can there be a count of an ID field that doesn't exist??

SELECT tbl_parts.Component_Type_ID_FK, Count(tbl_parts.Part_Number) AS CountOfPart_Number
FROM tbl_parts
GROUP BY tbl_parts.Component_Type_ID_FK;

[Minty]

You must have a Partnumber record without an ID, or a Empty or ZLS string record somewhere.

[Beanie_d83]

Ah yes I had a part with a missing type ID!

Minty, thank you very much for your help

[Minty]

You are welcome!
Good luck with your project.