Sequential Numbering

[ptrifile]

I am using the following query:

SELECT "Outlook" AS queuename, tblinbox_test.from1 AS employee, "3" AS eventType, Count(tblinbox_test.from1) AS CountOffrom1, "SYSTEM_Outlook_VCT" AS updateBy, DateAdd("s",-1,DateValue([received])+1) AS updateDate
FROM tblinbox_test
GROUP BY "Outlook", tblinbox_test.from1, "3", "SYSTEM_Outlook_VCT", DateAdd("s",-1,DateValue([received])+1);

I need to add a sequential number starting with zero in a field called COUNT. I have read about many solutions but they seem to be a bit over my head. Is there a way to accomplish this?

Appreciate any suggestions.

Paul

 

[dhookom]

Adding a sequence count requires a field or expression that is used to base the count on. What is that field or expression?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[ptrifile]

Hi Duane, thanks for jumping in. I believe that since I am grouping on the "From1" field that is what I would use to base the count on.

Hope that helps.

Thank you,

Paul

 

[dhookom]

I [From1] a unique field? If not, what would you use to break ties?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[ptrifile]

Maybe it would help for me to explain what I am doing a little more. Hopefully it will help.

I am exporting Outlook email fields into a table in Access. The table only has 3 fields, ID(PK), From1(who an email is from) and Recieved (when an email was recieved)and date/time. My query is simply grouping by the "from1" field and counting how many emails were from each person. In production, only daily data with the same date will be uploaded into Access ( you helped me add 11:59:59 to each date in the data) at a time. I will then use the query to export a file to be uploaded to another system.

I don't know if that helps or not but that is what I what I am trying to do. The system that accepts this data needs to have a "count" field that starts with zero.

it would look something like this.

Count     From1     countofreceived          Date/Time
0         User1           25            05/28/2020 11:59:59
1         User2           10            05/28/2020 11:59:59
2         User3           52            05/28/2020 11:59:59
3         User4           5             05/28/2020 11:59:59

Thanks for any guidance you can provide

Paul

 

[dhookom]

I would first create the "table in Access" that should have unique From1 and possibly [Date/Time]. Then run an update query that would populate the Count field:

UPDATE [table in Access] 
SET [Count Field] = DCount("*","[table in Access]","From1<'" & [From1] & "'")

If there are multiple dates for the same From1 then you will need to also filter for the date in the DCount() where condition.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[ptrifile]

Hi Duane, thanks again. It's close to what I am looking for, however it is skipping numbers for how many counts there are for a person or countofreceived. it starts with 0, 1, 2, 3, 4 then jumps to 19 because the #4 "person" had a count of 15. Am I doing something wrong?

Can't thank you enough for the help.

Paul

 

[dhookom]

Apparently you didn't make a table that resembled your "would look something like this". Can you share your SQL and what you did?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[ptrifile]

The table only has the 3 fields, the example I gave was what the query should look like. So, the query is giving me the COUNTOFRECIEVED.

So my table only has ID, From1 and Received.

The SQL, below, is what you had suggested.

UPDATE tblinbox_test SET tblinbox_test.[Count] = DCount("*","[tblinbox_test]","from1<'" & [from1] & "'");

 

[dhookom]

Is From1 a primary key/unique index as per your example data?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[ptrifile]

No, the primary key in the table is "ID". I can certainly change it as I do not need the ID field.

 

[dhookom]

Just answer this question. If you look at the table are there any duplicates of the From1 values? Your example has only unique From1 values.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[ptrifile]

Yes, there can and are multiple From1 Values. Since these are emails coming in to an inbox, there can be many received from the same person. I apologize for not putting that in to my example.

 

[dhookom]

Did you ever create a summary table?
Can you provide:
[ul]
[li]your actual significant fields[/li]
[li]a good variety of data that can be used to provide a solution[/li]
[li]the desired values in a new column[/li]
[/ul]

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016