Resquence table

[jrp444]

I have the following information that I pull into a table on access.
Field1 Field2 Field3 Field4 Field5
20060811 0948 000000087-8700016170 94 01
20060811 0948 000000087-8700016170 94 02
20060811 0948 000000087-8700016170 94 01
20060811 0948 000000087-8700016170 94 02
20060811 0948 000000087-8700016170 94 01
20060811 0948 000000087-8700016170 94 02
20060811 0948 000000087-8700016170 94 01
20060811 0948 000000087-8700016170 94 02

I download this information into another database based on the Date(Field1),
Time(Field 2), Account No(Field 3), Code (Field4) and Sequence ( Field5).
The database will not take the information if the Time, Date, & Account No
has the same sequence.

Can anyone show me how to find the data that has the duplicate Date, Time,
Account & Sequence No and resequence the sequence number so it will be like
01,02,03,04 etc.
on these accounts.

 

[John Vinson]

Can anyone show me how to find the data that has the duplicate Date, Time,
Account & Sequence No and resequence the sequence number so it will be like
01,02,03,04 etc.
on these accounts.

Use the Query Wizard to create a "Find Duplicates" query.

I wouldn't store the sequence number in the table at all; instead
calculate it on demand in a Query (and you can and shoule export data
from a Query, it's not necessary to export from a Table). You can use
a calculated field like

SequenceNo: (SELECT Count(*) FROM mytable AS X WHERE X.Account =
mytable.Account AND X.[Date] + X.[Time] <= mytable.[Date] +
mytable.[time]);

Note that Date and Time are reserved words and WILL cause trouble when
Access mistakes them for the builtin Date() and Time() functions; and
that it's often better to store a date/time value as one field so that
you don't need to combine them as I did above.

John W. Vinson[MVP]

 

[jrp444]

The resequence that you show in your in your calculation, will this
resequence all the accounts in the table if I run it against the whole table?
If that would work I would just run the query and not worry about the
duplicates in the table and resequence all the accounts. This would take
care of the duplicates.

Can anyone show me how to find the data that has the duplicate Date, Time,
Account & Sequence No and resequence the sequence number so it will be like
01,02,03,04 etc.
on these accounts.

Use the Query Wizard to create a "Find Duplicates" query.

I wouldn't store the sequence number in the table at all; instead
calculate it on demand in a Query (and you can and shoule export data
from a Query, it's not necessary to export from a Table). You can use
a calculated field like

SequenceNo: (SELECT Count(*) FROM mytable AS X WHERE X.Account =
mytable.Account AND X.[Date] + X.[Time] <= mytable.[Date] +
mytable.[time]);

Note that Date and Time are reserved words and WILL cause trouble when
Access mistakes them for the builtin Date() and Time() functions; and
that it's often better to store a date/time value as one field so that
you don't need to combine them as I did above.

John W. Vinson[MVP]

 

[jrp444]

In the sequence routing mytable is the Name of the table the data is in,
account is my field3, Date is field1 and Time is field2. Is this correct?

Can anyone show me how to find the data that has the duplicate Date, Time,
Account & Sequence No and resequence the sequence number so it will be like
01,02,03,04 etc.
on these accounts.

Use the Query Wizard to create a "Find Duplicates" query.

I wouldn't store the sequence number in the table at all; instead
calculate it on demand in a Query (and you can and shoule export data
from a Query, it's not necessary to export from a Table). You can use
a calculated field like

SequenceNo: (SELECT Count(*) FROM mytable AS X WHERE X.Account =
mytable.Account AND X.[Date] + X.[Time] <= mytable.[Date] +
mytable.[time]);

Note that Date and Time are reserved words and WILL cause trouble when
Access mistakes them for the builtin Date() and Time() functions; and
that it's often better to store a date/time value as one field so that
you don't need to combine them as I did above.

John W. Vinson[MVP]

 

[John Vinson]

The resequence that you show in your in your calculation, will this
resequence all the accounts in the table if I run it against the whole table?
If that would work I would just run the query and not worry about the
duplicates in the table and resequence all the accounts. This would take
care of the duplicates.

Remember - I CANNOT SEE YOUR DATABASE. I'm *guessing* at some of this.
My query will assign sequence numbers from 1 to whatever for each
value of Account.

In the sequence routing mytable is the Name of the table the data is in,
account is my field3, Date is field1 and Time is field2. Is this correct?

I have no idea. If your table still has fields named Field1 and
Field2, do yourself a favor and give them meaningful names! But yes,
you just need to use your own table and fieldnames in place of my
examples.

John W. Vinson[MVP]

 

[jrp444]

I changed your Sequence calculation to the following:
SequenceNo: (SELECT Count(*) FROM SusansNotes AS X WHERE X.Field3=
SusanNotes[.Field3] AND X.[Date] + X.[Time] <= Susannotes.[Field1] +
Susannotes.[Field2])

When I run the query it ask me for the information for Field1, Field2 and
Field3. It seems to not be finding it in the table. What am I doing wrong?

Can anyone show me how to find the data that has the duplicate Date, Time,
Account & Sequence No and resequence the sequence number so it will be like
01,02,03,04 etc.
on these accounts.

Use the Query Wizard to create a "Find Duplicates" query.

I wouldn't store the sequence number in the table at all; instead
calculate it on demand in a Query (and you can and shoule export data
from a Query, it's not necessary to export from a Table). You can use
a calculated field like

SequenceNo: (SELECT Count(*) FROM mytable AS X WHERE X.Account =
mytable.Account AND X.[Date] + X.[Time] <= mytable.[Date] +
mytable.[time]);

Note that Date and Time are reserved words and WILL cause trouble when
Access mistakes them for the builtin Date() and Time() functions; and
that it's often better to store a date/time value as one field so that
you don't need to combine them as I did above.

John W. Vinson[MVP]

 

[John Vinson]

I changed your Sequence calculation to the following:
SequenceNo: (SELECT Count(*) FROM SusansNotes AS X WHERE X.Field3=
SusanNotes[.Field3] AND X.[Date] + X.[Time] <= Susannotes.[Field1] +
Susannotes.[Field2])

When I run the query it ask me for the information for Field1, Field2 and
Field3. It seems to not be finding it in the table. What am I doing wrong?

Presumably your table does not contain fields named Field1, Field2 or
Field3. Open the table in design view and check - might they be
Field_1, Field_2, Field_3? Or do you have Lookup fields in the table?

Also the first brackeeting is wrong: you've got the period inside the
brackets, not in front of the bracket; it should be

SequenceNo: (SELECT Count(*) FROM SusansNotes AS X WHERE X.Field3=
SusanNotes.[Field3] AND X.[Date] + X.[Time] <= Susannotes.[Field1] +
Susannotes.[Field2])

using your actual fieldnames of course.

John W. Vinson[MVP]

 

[jrp444]

I did not have the Table name correct in all the place it needed to be. Once
I fixed that I got the following:
Date Time Account Code SequenceNo
20060809 1455 000000010-1007379183 94 3
20060809 1455 000000010-1007379183 94 3
20060809 1455 000000010-1007379183 94 3

As you can see it sequence everything as being no "3".

Any ideas????

I changed your Sequence calculation to the following:
SequenceNo: (SELECT Count(*) FROM SusansNotes AS X WHERE X.Field3=
SusanNotes[.Field3] AND X.[Date] + X.[Time] <= Susannotes.[Field1] +
Susannotes.[Field2])

When I run the query it ask me for the information for Field1, Field2 and
Field3. It seems to not be finding it in the table. What am I doing wrong?

Presumably your table does not contain fields named Field1, Field2 or
Field3. Open the table in design view and check - might they be
Field_1, Field_2, Field_3? Or do you have Lookup fields in the table?

Also the first brackeeting is wrong: you've got the period inside the
brackets, not in front of the bracket; it should be

SequenceNo: (SELECT Count(*) FROM SusansNotes AS X WHERE X.Field3=
SusanNotes.[Field3] AND X.[Date] + X.[Time] <= Susannotes.[Field1] +
Susannotes.[Field2])

using your actual fieldnames of course.

John W. Vinson[MVP]

 

[John Vinson]

I did not have the Table name correct in all the place it needed to be. Once
I fixed that I got the following:
Date Time Account Code SequenceNo
20060809 1455 000000010-1007379183 94 3
20060809 1455 000000010-1007379183 94 3
20060809 1455 000000010-1007379183 94 3

As you can see it sequence everything as being no "3".

Any ideas????

Please post the SQL view of the query you're actually using.

John W. Vinson[MVP]

 

[jrp444]

This is the SQL view, I do appreciate what you are doing.

SELECT SusansNotes.Date, SusansNotes.Time, SusansNotes.Account,
SusansNotes.Code, (SELECT Count(*) FROM SusansNotes AS X WHERE X.Account=
SusansNotes.[Account] AND X.[Date] + X.[Time] <= Susansnotes.[Date] +
Susansnotes.[Time]) AS SequenceNo
FROM SusansNotes;

 

[John Vinson]

This is the SQL view, I do appreciate what you are doing.

SELECT SusansNotes.Date, SusansNotes.Time, SusansNotes.Account,
SusansNotes.Code, (SELECT Count(*) FROM SusansNotes AS X WHERE X.Account=
SusansNotes.[Account] AND X.[Date] + X.[Time] <= Susansnotes.[Date] +
Susansnotes.[Time]) AS SequenceNo
FROM SusansNotes;

Ah. I was incorrectly assuming that your fields named [Date] and
[Time] - again, DANGEROUS fieldnames, they are both reserved words! -
were Date/Time fields which can be added. Evidently they are Text
fields instead.

However, the problem here is tiebreaking. These three records all have
*exactly the same data* for all these fields, and Access can't decide
which of them should be 1, or 2, or 3. Is there any other field which
can break the tie, such as an Autonumber?

John W. Vinson[MVP]

 

[jrp444]

No but I can add an Primary Key to the table when I bring it in. If I add
the Key will it work correctly then?

This is the SQL view, I do appreciate what you are doing.

SELECT SusansNotes.Date, SusansNotes.Time, SusansNotes.Account,
SusansNotes.Code, (SELECT Count(*) FROM SusansNotes AS X WHERE X.Account=
SusansNotes.[Account] AND X.[Date] + X.[Time] <= Susansnotes.[Date] +
Susansnotes.[Time]) AS SequenceNo
FROM SusansNotes;

On Tue, 15 Aug 2006 11:27:01 -0700, jrp444

I did not have the Table name correct in all the place it needed to be. Once
I fixed that I got the following:
Date Time Account Code SequenceNo
20060809 1455 000000010-1007379183 94 3
20060809 1455 000000010-1007379183 94 3
20060809 1455 000000010-1007379183 94 3

Ah. I was incorrectly assuming that your fields named [Date] and
[Time] - again, DANGEROUS fieldnames, they are both reserved words! -
were Date/Time fields which can be added. Evidently they are Text
fields instead.

However, the problem here is tiebreaking. These three records all have
*exactly the same data* for all these fields, and Access can't decide
which of them should be 1, or 2, or 3. Is there any other field which
can break the tie, such as an Autonumber?

John W. Vinson[MVP]

 

[John Vinson]

No but I can add an Primary Key to the table when I bring it in. If I add
the Key will it work correctly then?

Just include the Primary Key in the sort order for the subquery, so
each record is unique:

SELECT SusansNotes.Date, SusansNotes.Time, SusansNotes.Account,
SusansNotes.Code,
(SELECT Count(*) FROM SusansNotes AS X
WHERE X.Account=SusansNotes.[Account]
AND X.[Date] + X.[Time] <= Susansnotes.[Date] + Susansnotes.[Time]
AND X.[Keyfield] <= Susansnotes.[Keyfield]) AS SequenceNo
FROM SusansNotes;


John W. Vinson[MVP]