Numbering records in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I am trying to number records in a query by employee and charge number.
An example is bleow. This needs to be done in a query not a report.

Any ideas would be we appreciated.

Thanks,

Employee charge# autonumber
1 123 1
1 456 2
2 223 1
 
hi Mike,
I am trying to number records in a query by employee and charge number.
An example is bleow. This needs to be done in a query not a report.
Click to expand...
I don't see any scheme or logic in your example. Could you explain it a
little bit better, please?
Employee charge# autonumber
1 123 1
1 456 2
2 223 1
Click to expand...


mfG
--> stefan <--
 
Try something like

SELECT TableName.Employee, TableName.[charge#],
DCount("*","TableName","Employee=" & [Employee] & " And [charge#] <=" &
[charge#]) AS AutoNumber
FROM TableName
ORDER BY TableName.Employee, TableName.Charge

Note: you will have the same resault for the counter if the charge# repeat
itself for tha same employee.
Also, it's important to sort the data
 
Try something like

SELECT TableName.Employee, TableName.Charge,
DCount("*","TableName","Employee=" & [Employee] & " And [charge#] <=" &
[charge#]) AS AutoNumber
FROM TableName
ORDER BY TableName.Employee, TableName.Charge

Note: you will have the same resault for the counter if the charge# repeat
itself for tha same employee.
Also, it's important to sort the data
 
Hi Stefan,
I have a file that contains an employees ID, charge #s, and hours
worked. I created a query that sums hours by charge # and by employee. I need
to create a field that numbers each employees charge numbers sequentially and
resets for each employee. Each employee could potentially have multiple
charge numbers. I have created another example below. The column "need to
create" is what I would like to have access create in a query.

Thanks,

Employee ID# Charge # Hours Need to create
001 123 4 1
001 456 4 2
004 211 8 1
 
I am getting an #Error messeage, do you have any suggestions.

SELECT TableName.Employee, TableName.[charge#],
DCount("*","TableName","Employee =" & [Employee] & " And [charge#]<=" &
[charge#]) AS AutoNumber
FROM TableName
ORDER BY TableName.Employee, TableName.[charge#];


Ofer Cohen said:
Try something like

SELECT TableName.Employee, TableName.[charge#],
DCount("*","TableName","Employee=" & [Employee] & " And [charge#] <=" &
[charge#]) AS AutoNumber
FROM TableName
ORDER BY TableName.Employee, TableName.Charge

Note: you will have the same resault for the counter if the charge# repeat
itself for tha same employee.
Also, it's important to sort the data

--
Good Luck
BS"D


MikeM said:
Hi,
I am trying to number records in a query by employee and charge number.
An example is bleow. This needs to be done in a query not a report.

Any ideas would be we appreciated.

Thanks,

Employee charge# autonumber
1 123 1
1 456 2
2 223 1
Click to expand...
Click to expand...
 
If the Employee field type is text (for example) you need to add single quote
before and after the value

SELECT TableName.Employee, TableName.[charge#],
DCount("*","TableName","Employee ='" & [Employee] & "' And [charge#]<=" &
[charge#]) AS AutoNumber
FROM TableName
ORDER BY TableName.Employee, TableName.[charge#]


--
Good Luck
BS"D


MikeM said:
I am getting an #Error messeage, do you have any suggestions.

SELECT TableName.Employee, TableName.[charge#],
DCount("*","TableName","Employee =" & [Employee] & " And [charge#]<=" &
[charge#]) AS AutoNumber
FROM TableName
ORDER BY TableName.Employee, TableName.[charge#];


Ofer Cohen said:
Try something like

SELECT TableName.Employee, TableName.[charge#],
DCount("*","TableName","Employee=" & [Employee] & " And [charge#] <=" &
[charge#]) AS AutoNumber
FROM TableName
ORDER BY TableName.Employee, TableName.Charge

Note: you will have the same resault for the counter if the charge# repeat
itself for tha same employee.
Also, it's important to sort the data

--
Good Luck
BS"D


MikeM said:
Hi,
I am trying to number records in a query by employee and charge number.
An example is bleow. This needs to be done in a query not a report.

Any ideas would be we appreciated.

Thanks,

Employee charge# autonumber
1 123 1
1 456 2
2 223 1
Click to expand...
Click to expand...
Click to expand...
 
Works like a champ. Thanks for your help!

Ofer Cohen said:
If the Employee field type is text (for example) you need to add single quote
before and after the value

SELECT TableName.Employee, TableName.[charge#],
DCount("*","TableName","Employee ='" & [Employee] & "' And [charge#]<=" &
[charge#]) AS AutoNumber
FROM TableName
ORDER BY TableName.Employee, TableName.[charge#]


--
Good Luck
BS"D


MikeM said:
I am getting an #Error messeage, do you have any suggestions.

SELECT TableName.Employee, TableName.[charge#],
DCount("*","TableName","Employee =" & [Employee] & " And [charge#]<=" &
[charge#]) AS AutoNumber
FROM TableName
ORDER BY TableName.Employee, TableName.[charge#];


Ofer Cohen said:
Try something like

SELECT TableName.Employee, TableName.[charge#],
DCount("*","TableName","Employee=" & [Employee] & " And [charge#] <=" &
[charge#]) AS AutoNumber
FROM TableName
ORDER BY TableName.Employee, TableName.Charge

Note: you will have the same resault for the counter if the charge# repeat
itself for tha same employee.
Also, it's important to sort the data

--
Good Luck
BS"D


:

Hi,
I am trying to number records in a query by employee and charge number.
An example is bleow. This needs to be done in a query not a report.

Any ideas would be we appreciated.

Thanks,

Employee charge# autonumber
1 123 1
1 456 2
2 223 1
Click to expand...
Click to expand...
Click to expand...
 
Both fields are text, could you help me out with the sing quote placement on
the charge#s. I can not quite figure out the quote palcement and I am getting
errors

Thanks for your help.

Ofer Cohen said:
If the Employee field type is text (for example) you need to add single quote
before and after the value

SELECT TableName.Employee, TableName.[charge#],
DCount("*","TableName","Employee ='" & [Employee] & "' And [charge#]<=" &
[charge#]) AS AutoNumber
FROM TableName
ORDER BY TableName.Employee, TableName.[charge#]


--
Good Luck
BS"D


MikeM said:
I am getting an #Error messeage, do you have any suggestions.

SELECT TableName.Employee, TableName.[charge#],
DCount("*","TableName","Employee =" & [Employee] & " And [charge#]<=" &
[charge#]) AS AutoNumber
FROM TableName
ORDER BY TableName.Employee, TableName.[charge#];


Ofer Cohen said:
Try something like

SELECT TableName.Employee, TableName.[charge#],
DCount("*","TableName","Employee=" & [Employee] & " And [charge#] <=" &
[charge#]) AS AutoNumber
FROM TableName
ORDER BY TableName.Employee, TableName.Charge

Note: you will have the same resault for the counter if the charge# repeat
itself for tha same employee.
Also, it's important to sort the data

--
Good Luck
BS"D


:

Hi,
I am trying to number records in a query by employee and charge number.
An example is bleow. This needs to be done in a query not a report.

Any ideas would be we appreciated.

Thanks,

Employee charge# autonumber
1 123 1
1 456 2
2 223 1
Click to expand...
Click to expand...
Click to expand...
 
Try

SELECT TableName.Employee, TableName.[charge#],
DCount("*","TableName","Employee ='" & [Employee] & "' And [charge#]<='" &
[charge#] & "'") AS AutoNumber
FROM TableName
ORDER BY TableName.Employee, TableName.[charge#]


--
Good Luck
BS"D


MikeM said:
Both fields are text, could you help me out with the sing quote placement on
the charge#s. I can not quite figure out the quote palcement and I am getting
errors

Thanks for your help.

Ofer Cohen said:
If the Employee field type is text (for example) you need to add single quote
before and after the value

SELECT TableName.Employee, TableName.[charge#],
DCount("*","TableName","Employee ='" & [Employee] & "' And [charge#]<=" &
[charge#]) AS AutoNumber
FROM TableName
ORDER BY TableName.Employee, TableName.[charge#]


--
Good Luck
BS"D


MikeM said:
I am getting an #Error messeage, do you have any suggestions.

SELECT TableName.Employee, TableName.[charge#],
DCount("*","TableName","Employee =" & [Employee] & " And [charge#]<=" &
[charge#]) AS AutoNumber
FROM TableName
ORDER BY TableName.Employee, TableName.[charge#];


:

Try something like

SELECT TableName.Employee, TableName.[charge#],
DCount("*","TableName","Employee=" & [Employee] & " And [charge#] <=" &
[charge#]) AS AutoNumber
FROM TableName
ORDER BY TableName.Employee, TableName.Charge

Note: you will have the same resault for the counter if the charge# repeat
itself for tha same employee.
Also, it's important to sort the data

--
Good Luck
BS"D


:

Hi,
I am trying to number records in a query by employee and charge number.
An example is bleow. This needs to be done in a query not a report.

Any ideas would be we appreciated.

Thanks,

Employee charge# autonumber
1 123 1
1 456 2
2 223 1
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Advanced Query Question 5
Restart Record Count 1
Query By Date and Count Records 3
Query Question 3
group multiple query in one query 2
Comparing Tables 11
1 to many query - trying to identify missing records 4
Record county by status 1

Back
Top