Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Difficulty Creating a sequential counter in Access 2007

Posted on 2009-05-07
16
Medium Priority
?
1,244 Views
Last Modified: 2012-05-06
Hey guys...
I've created a query that I need to put a sequential counter into.  I'm using the DCount function against an autonumber primary key field (ID).  For some reason or another in this particular query my counter is returning the same number as in my ID field. Any suggestions would be greatly appreciated!!

SELECT DCount("ID","[Room Bed Table]","ID<="& ID) AS [Counter], [Room Bed Table].ID, [Room Bed Table].[Facility ID], [Room Bed Table].[Facility Description], [Room Bed Table].[Unit ID], [Room Bed Table].[Room ID], [Room Bed Table].[Room Description], [Room Bed Table].[Location Comment], [Room Bed Table].Pic1, [Room Bed Table].Pic2, [Room Bed Table].Pic3
FROM [Room Bed Table]
WHERE ((([Room Bed Table].[Facility Description])="Voorhees") AND (([Room Bed Table].[Unit ID])="PICU")) OR ((([Room Bed Table].[Facility Description])="Voorhees") AND (([Room Bed Table].[Unit ID])="PCU")) OR ((([Room Bed Table].[Facility Description])="Voorhees") AND (([Room Bed Table].[Unit ID])="ICU"))
ORDER BY [Room Bed Table].ID;
0
Comment
Question by:romieb69
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 2
16 Comments
 
LVL 31

Expert Comment

by:hnasr
ID: 24329055
Check Where criteria.
Remove all the where expression. What do you get?
0
 

Author Comment

by:romieb69
ID: 24329130
Good suggestion... same thing.  I also removed all spaces from table and field names... same thing.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24329360
I know this will work:

SELECT (SELECT Count(b.ID) FROM [Room Bed Table] b WHERE b.ID<= a.ID) AS [Counter],
a.ID, a.[Facility ID], a].[Facility Description], a.[Unit ID], a.[Room ID], a.[Room Description], a.[Location Comment], a.Pic1, a.Pic2, a.Pic3
FROM [Room Bed Table] a
WHERE (((a.[Facility Description])="Voorhees") AND ((a.[Unit ID])="PICU")) OR (((a.[Facility Description]) = "Voorhees") AND ((a.[Unit ID]) = "PCU")) OR (((a.[Facility Description]) = "Voorhees") AND ((a.[Unit ID]) = "ICU"))
ORDER BY a.ID;
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:romieb69
ID: 24329530
Yep that sure as heck looks exactly like it would work... but I'm getting the EXACT same results... did a copy and paste of your code exactly as it appears.  I'm going to export the query results (only 30 recs) and attach.


RoomBedVoorhees.xlsx
0
 
LVL 31

Expert Comment

by:hnasr
ID: 24329838
If the ID starts with 1, one expects the count to be as the ID.
ID1 --- count 1
ID2 --- count 2

0
 
LVL 44

Expert Comment

by:GRayL
ID: 24330193
I was assuming that there were 'holes' in the ID as the result of deletions and subsequent additions in which the deleted ID are never used again.  If that is not the case and the ID sequence is intact, then Count will equal ID.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24330286
I do not have Office 2007 - just good old Office 2003.
0
 

Author Comment

by:romieb69
ID: 24330412
Thanks to everyone for the input.  The actual auto number field (ID) does not contain any holes in the main table. The returned results from my query though do leave holes in the sequence of numbers. My goal here was to basically 're-create' a new id field that would 'start over' at 1 based on the number of records returned from a query.  I have some additional VB code that needs to begin at '1' thru whatever (with no holes in sequence) for each data set it looks at to generate reports.  So if I get 30 records from my query and the id number is 220 - 260 (obviously a hole in sequence in this example) I want my counter field to literally count 1-30 ....  

My approach may be off here and I may very well need to rethink how to tackle this problem.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24330792
But the query I gave you will created the 1-30 count, so long as the ID increased record by record, regardless of 'holes'.
0
 
LVL 44

Accepted Solution

by:
GRayL earned 375 total points
ID: 24330813
That is to say each record must have an ID, but there may be skips,  ie, 1,4,5,6,11,12,13, etc.  
0
 

Author Comment

by:romieb69
ID: 24330876
Gray ... yeah this is really weird.  I've taken queries I've written ... as well as your query.... copied the structure of the table I'm working with... added some nonsense records ... deleted a couple of records and then ran both my query and your query and it works exactly like I'd expect.  I get back into my main query that's pointed at my table and then I get unexpected results.  Make sure you examine the attachment I provided so you get an illustration of this.  I'm giving you the points because I know good and well your query works.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24330989
Do a Compact and Repair.  If that does not work, open a new mdb and copy all the objects from the old db to the new one.  Let me know if that resolves it.  I do not like B's.
0
 

Author Comment

by:romieb69
ID: 24331084
Already did the compact and repair. I'll isolate this table, the report, my code and my query into a new database and see what happens. I'll have to get on this in the morning though as I have to get on a production issue.  Appreciate all the suggestions and insight.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24344280
Anything yet?
0
 

Author Comment

by:romieb69
ID: 24356510
Same thing Gray ... pretty weird man... I set up a single database with one table, one report and this one query and got the same results. I then tried to isolate the records by hospital (facility) and created 4 databases .... same thing. What I don't understand is if I just add 10 or so records to the same table structure with nonsense data ... delete a few ... add a few more.. the counter works perfect. Your code works fine.. and my original code works fine. I'm actually headed out to LA to start a new contract using MSMQ. I'm not going to mess with this any longer. This was all a work around to get around a report export bug with Access 2007 concerning the new 'Attachment' fields. Problem is related to a memory issue. Some 'picture attachments' show up in the export and some don't. Microsoft will have a patch for the issue in about a month. At this point it's easier to use the 'temp fix code' that Microsoft provided for the export problems (exports one file per page) and then grep those reports for specific things the client needs vs using a query. Not pretty but it works for now until MS fixes the original bug.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24357384
Then it looks like you gave me a B because of a MS bug ;-)
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question