incrementing

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

Guest

Hope this makes sense....

I have the following table with data.... With the need next to it.

user user id line
A A 1 1
B B 2 1
B B 2 2
C C 3 1
C C 3 2
C C 3 3

Is there an easy solution to this or do I have to do this in multiple steps?

Thanks in advance for your thoughts....
 
Greg said:
I have the following table with data.... With the need next to it.

user user id line
A A 1 1
B B 2 1
B B 2 2
C C 3 1
C C 3 2
C C 3 3

Is there an easy solution to this or do I have to do this in multiple steps?
Click to expand...


That can only be done if there is another field (or fields)
that can be used to sort the records into a unique order.
Your sample data has some duplicated records so it is
insufficient for this purpose.
 
Marshall,

Thanks for your reply. So if I already had an autonumber in addition to the
user, it can be done? If so, can you help me out?

for example:

id user id user claimid line
1 A 1 A 1 1
2 B 2 B 2 1
3 B 3 B 2 2
4 C 4 C 3 1
5 C 5 C 3 2
6 C 6 C 3 3
 
Well, an autonumber is a very arbitray sorting field, but if
it has a unique index, it will do. I don't understand what
the two id fields and two user fields are supposed to
signify so I'll just ignore them.

SELECT T.id, T.user, T.claimid,
(SELECT COUNT(*)
FROM table As X
WHERE X.claimid= T.claimid
AND X.id <= T.id
) As line
FROM table As T
ORDER BY T.claimid
 
Marshall,

Point of clarification. I need to insert two values into the table. The
first will be to assign a claim number(there will be 1 claim number for each
id[id=patient]). For each claim, there may be 1 or more lines which I need
to increment.

Hope that makes sense.

Marshall Barton said:
Well, an autonumber is a very arbitray sorting field, but if
it has a unique index, it will do. I don't understand what
the two id fields and two user fields are supposed to
signify so I'll just ignore them.

SELECT T.id, T.user, T.claimid,
(SELECT COUNT(*)
FROM table As X
WHERE X.claimid= T.claimid
AND X.id <= T.id
) As line
FROM table As T
ORDER BY T.claimid
--
Marsh
MVP [MS Access]


Thanks for your reply. So if I already had an autonumber in addition to the
user, it can be done? If so, can you help me out?

for example:

id user id user claimid line
1 A 1 A 1 1
2 B 2 B 2 1
3 B 3 B 2 2
4 C 4 C 3 1
5 C 5 C 3 2
6 C 6 C 3 3
Click to expand...
Click to expand...
 
Marshall Barton said:
Well, an autonumber is a very arbitray sorting field, but if
it has a unique index, it will do. I don't understand what
the two id fields and two user fields are supposed to
signify so I'll just ignore them.

SELECT T.id, T.user, T.claimid,
(SELECT COUNT(*)
FROM table As X
WHERE X.claimid= T.claimid
AND X.id <= T.id
) As line
FROM table As T
ORDER BY T.claimid
--
Marsh
MVP [MS Access]


Thanks for your reply. So if I already had an autonumber in addition to the
user, it can be done? If so, can you help me out?

for example:

id user id user claimid line
1 A 1 A 1 1
2 B 2 B 2 1
3 B 3 B 2 2
4 C 4 C 3 1
5 C 5 C 3 2
6 C 6 C 3 3
Click to expand...
Click to expand...
 
Sorry, but this thread is making less and less sense every
time I see it :-(

Best I can tell, you are using "id" for two, maybe three
different things. Trying to analyze your example again, I
think you mean that you want the same claimid for all
records with the same "user" value.

SELECT T.id, T.user,
(SELECT COUNT(*)
FROM table As X
WHERE X.user= T.user
AND X.id <= T.id
) As line,
(SELECT COUNT(*)
FROM (SELECT DISTINCT Y.user
FROM table As Y
) As Z
WHERE Z.user<= T.user
) As claimid,
FROM table As T
ORDER BY T.user

This is turning out to be a real mess. How did you ever get
a table with such incomplete data where it doesn't matter
what claim numbers are applied to the records?

I just noticed that you said somewhere that you want these
calculated fields to be in the table?? I really don't
understand what's happening with that, but if you really
mean that, please explain it carefully and in more detail.
--
Marsh
MVP [MS Access]

Point of clarification. I need to insert two values into the table. The
first will be to assign a claim number(there will be 1 claim number for each
id[id=patient]). For each claim, there may be 1 or more lines which I need
to increment.

Hope that makes sense.


Marshall Barton said:
Well, an autonumber is a very arbitray sorting field, but if
it has a unique index, it will do. I don't understand what
the two id fields and two user fields are supposed to
signify so I'll just ignore them.

SELECT T.id, T.user, T.claimid,
(SELECT COUNT(*)
FROM table As X
WHERE X.claimid= T.claimid
AND X.id <= T.id
) As line
FROM table As T
ORDER BY T.claimid
Click to expand...
Click to expand...
 
Back
Top