Assigning a sequence number to duplicates in query

[XanLo]

Hi, Firstly I'm very new to access to apologies if I don't word any of this correctly.

I have a set of data in 1 single table that contains email addresses and references. There can be more than 1 reference assigned to an email address and I need to be able to sequentially number them so that later on I can export all the references on a single line showing the email address only once (planning to use the sequential number to identify duplicates and create additional tables or queries later on).

For Example initial data will be recorded in table FULL_LIST as:-

email_address1 Ref100
email_address2 Ref101
email_address3 Ref103
email_address3 Ref104
email_address4 Ref105
email_address4 Ref106
email_address4 Ref107

What I am after is the ability to run a query which has a number in as follows:-

email_address1 Ref100 1
email_address2 Ref101 1
email_address3 Ref103 1
email_address3 Ref104 2
email_address4 Ref105 1
email_address4 Ref106 2
email_address4 Ref107 3

The query I'm currently trying looks like this:-

SELECT (Select Count(*)
from Full_List As H
Where H.Email_Address = Full_List.Email_Address) AS [Assign#], *
FROM Full_List
ORDER BY Email_address, ACCREF DESC , ONLINEKEYREF;

and the results look like this:-

email_address1 Ref100 1
email_address2 Ref101 1
email_address3 Ref103 2
email_address3 Ref104 2
email_address4 Ref105 3
email_address4 Ref106 3
email_address4 Ref107 3

Can anyone help at all?

Many thanks


P.S. I can't post images yet as I'm a new member so I've attached screenshots of data instead in a word doc (all data is sample and not real references or emails in case anyone worries).

 

[plog]

You haven't provided your field names, so exact SQL is not possible. But you seem to have a handle on writing queries so I will give you the general method.

To impose a numbering system on data, you need to define an order to it. Right? I mean, 1st, 2nd, 3rd, 259th only can be determined when you set the rules for numbering. So you need to define that order to number your data. It looks like you are numbering your data based on its [RefNum] field. So my method assumes that [EmailField] / [RefNum] permutations in your data are unique. If that's not the case, then you need to better define how your data is to be ordered.

To achieve what you want, you need your subquery to Count just the number of [RefNum] values for your [EmailField] are below the current record. Your current subquery just takes the [EmailField] into consideration. You also need to include the [RefNum] field.

 

[plog]

There is also another method rather than you subquery. You can use a DCount:

..., DCount("[EmailField]", "Full_List", "[EmailField] = '" & [EmailField] & "' AND [RefNum]<='" & [RefNum] & "'"") AS [Assign#]...

 

[arnelgp]

Refnum100 to 107 u think is the content of the field and are dummy data

 

[arnelgp]

You add auto number field then it would be possible.

 

[Pat Hartman]

Domain functions are very inefficient so unless you are positive that the recordset will never exceed a few hundred rows, I would do two separate queries and join them
qry1
Select EmailAddress, Count(*) as RefCount From YourTable
Group by EmailAddress;
qry 2 Select YourTable.*, qry1.RefCount
from YourTable
Inner Join YourTable.EmailAddress = qry1.EmailAddress

 

[Gasman]

Fron only what you have described, would not Allen Browne's Concatenate do all that for you.

http://allenbrowne.com/func-concat.html

 

[XanLo]

Thank you for this, I amended my query to include the ref in the sub query and it did exactly what I needed.

SELECT (Select Count(*)
from Full_List As H
Where H.Email_Address = Full_List.Email_Address
And H.ACCREF + H.ACCREF<= Full_list.ACCREF + Full_List.ACCREF) AS [Assign#], *
FROM Full_List
ORDER BY Email_address, ACCREF DESC , ONLINEKEYREF;

 

[Pat Hartman]

Just FYI, If this is a Jet/ACE BE, Access does not optimize sub queries well which is why I suggested two separate queries that you specifically join. If your table is small, the subquery won't be a problem but it may become one as the row count grows.