Re: Displaying row no/Record Counting

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 07/01/04 

Date: Thu, 1 Jul 2004 21:43:14 +0800

Okay. Let's assume your table is named "Table1", and it has fields:
- Table1ID AutoNumber primary key
- ClientID Number refers to ClientID in the Client table
- EnteredOn Date/Time when the record was added.

You create a query that contains Table1.
You can include the Client table to get names and addresses from there if
you wish.

On each row of your query, you need to count the number of records in Table1
that are ahead of this one. To refer to Table1 again, you will need use
another name (an alias) to refer to the 2nd one. That's why the subquery
gets its records:
    FROM Table1 AS Dupe
i.e. from another copy of Table1.

You have to count some field. Any field will do, as long as it always has a
value, so the primary key is the safest choice to count. That's why you want
to:
    SELECT Count(Table1ID)

Now you want to limit that count to ony the records that are ahead of the
current one. Since "ahead" is defined by the date field, you want the count
where the date/time field in the copy is earlier than the date/time in the
row being examined, i.e.:
    WHERE Dupe.EnteredOn < Table1.EnteredOn

Now, the count of records ahread of the first row is zero. You probably want
to count the first record as 1, and so on through all the records, so you
need to add 1 to the count.

Putting that together, your subquery is:

(SELECT Count(Table1ID) + 1) AS HowMany
    FROM Table1 AS Dupe
    WHERE (Dupe.EnteredOn < Table1.EnteredOn) 

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<anonymous@discussions.microsoft.com> wrote in message
news:2467801c45f6d$591f3d30$a501280a@phx.gbl...
> This seems to also be a solution to my situtation; does it
> appear so to you?  When I attempt to translate your example
> to my query I'm getting a syntax error, so evidently I
> don't guite get it yet.  Perhaps you could clarify your
> example for the SQL-challenged?
>
> I am creating a waiting list with the position on the list
> based on the date and time the record was added.  I've done
> that using a running-sum counter textbox in a report.  In
> order to generate an alphabetical listing of these people,
> I have to analyze the report in Excel and sort the
> spreadsheet based on the client's name.
>
> While this gets the job done, it's a bit of a kludge.  I'd
> prefer to set a calculated field within the query so the
> report can be sorted any way I like (position, name, zip
> code, etc) without having to leave Access (and without
> having to become a VB programmer).
>
> I found something of a solution in the KB
> <http://support.microsoft.com/default.aspx?scid=kb;en-us;
> 199679>, but hope a simpler solution exists!  I'm certain
> that if it does, someone here will be able to enlighten me!
>
> Thanks,
>
>  -Bob
>
> >-----Original Message-----
> >There is not a nice way to do this, but if you don't mind
> a read-only result
> >(e.g. if it's going to a report), you could use a subquery
> to count the
> >number of records ahead of this one.
> >
> >You type the subquery into a fresh column in the Field row
> in query design
> >view. Something like this:
> >Rank: (SELECT (Count([ID]) + 1) AS Rank FROM MyTable AS
> Dupe WHERE (Dupe.ID
> >< MyTable.ID))
> >
> >Where there is a tie, you get typical ranking results, e.
> g.:
> >    1
> >    2
> >    2
> >    4
> >    5
> >
> >If you need the records to be updatable, you can use
> DCount() to create the
> >calculated field, but it will be very slow.
> >
> >"Kerri Davies" <anonymous@discussions.microsoft.com> wrote
> in message
> >news:244bb01c45f50$876fa5c0$a501280a@phx.gbl...
> >> I am trying to include a row number in a query. Is this
> >> possible?
> >>
> >> I am compiling top 10's of product for 4 individual
> years
> >> and in order to link each individual query I need to
> >> calcaluate a position (position 1 in 2002 will link to
> >> position 1 in year 2001 etc)
> >>
> >> Is there a way to do this without having to create
> tables
> >> with an autonumber field?
> >>
> >> Any help would be appreciated
> >> K

Relevant Pages

  • Create Excel Spreadsheet from Access with 1 to many relationship
    ... Table1 has a variety of fields, with a key field, I'll call KEY. ... Each record in Table2 also has a memo field. ... The requirement is that I produce a report in Excel that contains all ... So if I did a left join query on the above, what I would get would be: ...
    (microsoft.public.access.queries)
  • Re: Combo Box and Relationship - giving query and report number no
    ... Fix that and your Report should be very ... on the paper form and needs to be check by computer on data entry, ... The only problem I have is that with one query I use. ... I use this table1 to fill out another table3. ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Lookup Problem
    ... put them both in the query and place the fields you want from table1 except ... > am concerned with are, ZIP, CITY, STATE, COUNTY. ... > problem comes when I want to put the information on a report. ... > I use a query to ask the user for a record to print. ...
    (microsoft.public.access.reports)
  • Re: How to add to a new field based on information in existing field?
    ... a query to join the two tables. ... For example, Table1 includes fields TestID ... Table2 includes fields Description and DescriptionFr. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Non-updateable query issue
    ... You have an unnormalized table (of course, as it came from a flat spreadsheet instead of a relational source), and is it has 100 columns. ... joined to the master, showing all the records, the query is not updateable. ... > Say Fred deletes record 79 from Table1, and then Betty changes the> phone ... > Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.queries)