Create counter field in query? 1

[LakotaMan]

Hi all,
I need to order my records so I have continuous odd and even numbers --can't use the autonumber field due to skips in numbers (deleted recs, canceled recs, etc).

I want to do this in a query so I can use this counter for conditional formatting on a form, and so it will continually update.

I've looked over the posts on this and don't find one that specifically suits this task.

I am using Access 2007 and any help you can give will be greatly appreciaed.

Thanks,
H. LM

 

[lespaul]

do you have a key field on the table? some way to tell which one should come first?

Leslie

Come join me at New Mexico Linux Fest!

 

[LakotaMan]

Les,
I am using an autonumber as the key. Trouble is, when a rec is deleted, canceled, etc. the numbers skip, are not sequentially odd/even. i.e."

1235
1236
1238
1239

Thanks,
H. LM

 

[lespaul]

right, but as long as there's a field that CAN be sorted in the correct order then you can rank or count them...i'm just not good enough to figure it out! Hopefully PHV or DHookum will be by soon and can do it!!!

Leslie

 

[dhookom]

How about providing some table and field names? Otherwise, here is the SQL to rank the Orders table in Northwind by Freight:

SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.Freight, Count(Orders_1.OrderID) AS CountOfOrderID
FROM Orders, Orders AS Orders_1
WHERE (((Orders.Freight)>=[Orders_1]![Freight]))
GROUP BY Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.Freight
ORDER BY Orders.Freight;

Results (first few records)
[tt][blue]
Order ID Customer Employee Freight CountOfOrderID
10972 La corne d'abondance Peacock, Margaret $0.02 1
10296 LILA-Supermercado Suyama, Michael $0.12 2
10644 Wellington Importadora Leverling, Janet $0.14 3
10509 Blauer See Delikatessen Peacock, Margaret $0.15 4
11035 Suprêmes délices Fuller, Andrew $0.17 5
[/blue][/tt]

Duane
Hook'D on Access
MS Access MVP

 

[LakotaMan]

dhookom to the rescue!! Once I'd substituted all my fields, worked like a charm!

Guess I need to get to know Microsoft's flagship sample DBs better.

Thanks, Duane and also to you, Les.

 

[dhookom]

Les is actually Leslie ;-)

Lakota,
This form will not be updateable. If you need to update the records, you will have to change the SQL to use DCount() or something. Also, isn't there new functionality in Access 2007 that provides for alternate row shading (or something like that) with no coding or special SQL?

Duane
Hook'D on Access
MS Access MVP

 

[LakotaMan]

Duane,

The alternate row shading is a new feature of datasheets, but I needed a form (so users can click on a row to be taken to the detail record). That's why it's fine if it's not updateable (I've disabled and locked all the controls anyway)

Again, thanks to you and Leslie for your helps.

H. LM

 

[HarleyQuinn]

Grief, I totally mis-read the intention of this thread (took it for some reason to mean that OP wanted to keep current numbers and then have them run odd-->even-->odd etc.), wrote a solution using MOD came back and found not only had I done the wrong thing completely but it's solved! Ha!

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[dhookom]

HarleyQuinn,
I've never ever done that :~/

Duane
Hook'D on Access
MS Access MVP

 

[lespaul]

well at least you've already got code ready in case anyone ever DOES ask for what you thought you were suppose to be doing!

Les

 

[HarleyQuinn]

Les and Duane -

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[dhookom]

Question: does anyone else's Northwind mdb contain as much "junk" as mine?

SELECT Switch([Type]=1,"Tables",[Type]=4,"Tables Linked",[Type]=-32768,"Forms",[Type]=-32764,"Reports",[Type]=-32761,"Modules",[Type]=5,"Queries",[Type]=-32766,"Macros",[Type]=-32756,"Pages",1=1,"Other") AS TypeName, Count(Msysobjects.Name) AS CountOfName
FROM Msysobjects
GROUP BY Switch([Type]=1,"Tables",[Type]=4,"Tables Linked",[Type]=-32768,"Forms",[Type]=-32764,"Reports",[Type]=-32761,"Modules",[Type]=5,"Queries",[Type]=-32766,"Macros",[Type]=-32756,"Pages",1=1,"Other")
ORDER BY 1;

[tt][blue]
TypeName CountOfName
Forms 81
Macros 8
Modules 24
Other 21
Pages 8
Queries 332
Reports 102
Tables 170
Tables Linked 10
[/blue][/tt]

Duane
Hook'D on Access
MS Access MVP

 

[LakotaMan]

Duane,

I got this:

TypeName CountOfName
Forms 21
Macros 7
Modules 3
Other 21
Queries 50
Reports 13
Tables 31

This is converted to ver. 2007 though.

H. LM