DCount In a Query - increment number (1 Viewer)

[Leo_Polla_Psemata]

Hi
I have a query which displays data from a table, I copy paste this query in an excel and I create an XML. Manually I add number id in the excel, say, from one to 300.
I have recently insert a function like the below and now the id is ready, not need to insert manual.
DCount("[bner]","[DataInput]","[aa]<=" & [aa])


My problem is that I must make a partly increment number for the field BNER and each time we have a new bner record then we get a new id withing query.
To be more precise, i copy paste the result I need and hope the line out will be good
I delete it, display was no good, I will upload a picture
https://ibb.co/cunOH6

 

[Leo_Polla_Psemata]

i tried to link a picture then I upload,

You will see, there is a general id for each record, regardless of what is as bner
and another id that counts the bner, new bner, id starts from 1.

 

[The_Doc_Man]

First things first. If your ID should look like XXnnnnn/n then this can be done two or three ways, but the BEST way is to not keep this as one field in the table. You can do string concatenation in queries and queries can drive anything that tables could.

So in the table, your BNER and the IDPer fields could be part of a compound primary key and you could compute that ID Per BNER (IDPer) field and just store it based on your DCOUNT. Then when you need the PK in its "combined" form you can just have a field with

SELECT [BNER] & "/" & [IDPer], ...

for display purposes.

 

[Leo_Polla_Psemata]

Thanks Doc_Man

My problem is how to make auto number is query, i got it, then how to make
record id for each change of data.

Maybe I don't describe this very well

Let's suppose we have query results

A
A
A
B
C
D
D
E
F
F

and make it

A 1
A 2
A 3
B 1
C 1
D 1
D 2
E 1
F 1
F 2

Then , yes, I can make it A/1 and so on, but, which function would help me to dcount the fields like the above example?

 

[WayneRyan]

Leo,

About the only way to accomplish this with a query is as follows:

In addition to the PRIMARY id of [bner] you'll need another field that is unique
within the set of [bner]s. An example would be purchase order number [PO_Number].

Select bner,
       fnTheNumber([bner], [PO_Number]
from DataInput

Then in a MODULE, put this:

Public Function fnTheNumber(strBner as String,
                            PO_Number as Integer) As Number
fnTheNumber = DCount("[PO_Number]",
                     "DataInput",
                     "[bner] = '" & strBner & "' And " & _
                     "[PO_Number] <= " & PO_Number)
End Function

This technique is always inefficient; Nested queries, but it will work.
You just need the secondary UNIQUE field for your additional "sort".

In normal usage, the additional column (your "sort" number) would be added as
the data was entered; but as an additional column.

As Doc says, you can concatenate them later.

Wayne

 

[jgesouras]

Thanks folks - you saved me a lot of time

 

[Pat Hartman]

Your problem is symptomatic of a design flaw. This problem should be solved by using two tables. One to hold the "repeating" information and the second to hold the variable info.