Re: query unique count for field

I'm afraid I'm still struggling with this. From your valuable help, I now
have a table of records, then a grouping of those records, and then a query
with the same number of records as the first table, but with sequential
numbers for each group. I am trying to apply this new grouped sequence to the
first set of records. I am not looking for a 1 to n sequence across the whole
set, but the sequence must reset at the satart if each group.

I am encouraged that the two tables I wish to join have an equal number of
records, but every time I try to join them by various methods, I get a
multiple of records for each of the records in the original table. Or if I
manage to get the correct number of records, certain records are repeated. I
have an ID field in the original table, but I can't find how to go about
ranking this and then applying the new number to my ranked record.

Am I doing something obvious? Please bear in mind I'm not familiar with SQL
although I will try, but I do work extensively with Functions.

Thanks

"Michel Walsh" wrote:

> Hi,
>
>
>
> If you have other columns that make each record different, in reality, you
> can use them to "rank" your data.
>
>
> You also have another possibility using a temporary table: append the data
> into a temp table that has all the fields of the first table, plus an
> autonumber field (autoincrement by 1). Start with such a temp table empty of
> records, then append the existing data, sorted:
>
>
> query1:
>
> SELECT *
> FROM myTable
> ORDER BY f1
>
>
> is sorting the data, then execute something like:
>
> INSERT INTO temp( f1, f2, ...., fn) SELECT f1, f2, ..., fn FROM query1
>
> to fill the temp table. This table temp will then have all its record
> numbered from 1 to N, in its autonumber field,
>
>
> Get the minimum value for each value of the groups:
>
>
> query2:
>
> SELECT f1, MIN(autonumberFieldName) as minGroup
> FROM tempTable
> GROUP BY f1
>
>
>
>
> and then:
>
>
> SELECT tempTable.f1, f2, ..., fn, 1+(autonumberFieldName-minGroup)
> FROM tempTable INNER JOIN query2
> ON tempTable.f1 = query2.f1
>
>
> will finally supply the desired result.
>
>
>
> Hoping it may help,
> Vanderghast, Access MVP
>
>
>
> "Lancslad" <Lancslad@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:052B8E17-AE72-4FA7-81B4-20898B8826B3@xxxxxxxxxxxxxxxx
> > Please forgive me for hijacking flc123's thread, but I think we are both
> > trying to achieve the same goal, and so may be experiencing similar
> > problems.
> >
> > Having succeeded with Michel's advice, I now have a query with the correct
> > group numbering, but am having problems linking this query back to the
> > query
> > holding specific information on each row within the groups. How do I go
> > about
> > applying my new numbers to the relevant query record?
> >
> > "Michel Walsh" wrote:
> >
> >> Hi,
> >>
> >>
> >> If you can get the maximum count(*) per group, another solution is to
> >> make a
> >> join with a driver table.
> >>
> >>
> >> Query q1:
> >>
> >> SELECT number, COUNT(*) as c
> >> FROM myTable
> >> GROUP BY number
> >>
> >>
> >>
> >> Query q2:
> >>
> >>
> >> SELECT q1.number, iotas.itoa
> >> FROM q1 INNER JOIN iotas
> >> ON iotas.iota <= q1.c
> >>
> >>
> >>
> >>
> >> where table Iotas has a single field, iota, with values from 1 to nnn,
> >> nnn
> >> being the largest count you can expect.
> >>
> >>
> >> You are not obliged to fill by hand such a table. Instead, make a table
> >> Ds,
> >> one field, d, with 10 records with values from 0 to 9. Make a query:
> >>
> >> SELECT 1+ ds.d + 10*ds_1.d + 100* ds_2.d + 1000* ds_3.d As iota
> >> FROM ds, ds As ds_1, ds As ds_2, ds As ds_3
> >>
> >>
> >> will nicely to the job, but make a table, Iotas, out of it, and index the
> >> field iota (or make it a primary key). That takes 3 minutes and you have
> >> your Iotas table filled with values from 1 to 10 000. Much faster than
> >> typing all that data, or even, these instructions about how to do it.
> >>
> >>
> >> Hoping it may help,
> >> Vanderghast, Access MVP
> >>
> >>
> >>
> >> "Lancslad" <Lancslad@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:1DB3C96D-D8F8-4290-B7D9-C9275C9694E0@xxxxxxxxxxxxxxxx
> >> > In Excel, I would do this by counting how many times a particular
> >> > record
> >> > has
> >> > occured above that row using Countif, and then adding one to the
> >> > result. I
> >> > suppose Access has the advantage of grouping which should help. But is
> >> > there
> >> > any way Access can look UP the query to count records within a group,
> >> > but
> >> > without including the records below?
> >> >
> >> > "John Spencer" wrote:
> >> >
> >> >> Unless you have some other field to determine the ranking order of
> >> >> your
> >> >> same
> >> >> values, I know of no way to display the results you want using SQL.
> >> >> You
> >> >> could probably write a VBA function to do this or if your order was
> >> >> static
> >> >> you could write some VBA to step through the recordset and assign the
> >> >> value
> >> >> to a (new) field in your table.
> >> >>
> >> >>
> >> >> "Lancslad" <Lancslad@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> >> news:3A9F9C54-808C-4F38-889E-72043EDFA6D9@xxxxxxxxxxxxxxxx
> >> >> > I'm not sure this does what is required here. The SQL will group and
> >> >> > count
> >> >> > each record, but I understand that a sequential running sum is
> >> >> > required
> >> >> > for
> >> >> > each record within a group. This should be fairly simple, but I've
> >> >> > been
> >> >> > puzzling over it for weeks. Anyone able to help?
> >> >> >
> >> >> > "KARL DEWEY" wrote:
> >> >> >
> >> >> >> Create a TOTALS query. Edit the SQL below with your table and
> >> >> >> field
> >> >> >> names.
> >> >> >>
> >> >> >> SELECT YourTable.YourNumberField, Count(YourTable.YourNumberField)
> >> >> >> AS
> >> >> >> CountOfNumber
> >> >> >> FROM [YourTable]
> >> >> >> GROUP BY YourTable.YourNumberField;
> >> >> >>
> >> >> >>
> >> >> >> "flc123" wrote:
> >> >> >>
> >> >> >> > I am trying to count a sorted number field in a query. For each
> >> >> >> > identical
> >> >> >> > number I want the count to increment by one.
> >> >> >> > For example
> >> >> >> >
> >> >> >> > Number Count
> >> >> >> > 183 1
> >> >> >> >
> >> >> >> > 222 1
> >> >> >> > 222 2
> >> >> >> > 222 3
> >> >> >> >
> >> >> >> > 343 1
> >> >> >> >
> >> >> >> >
> >> >> >> > thanks
> >> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
.

Relevant Pages

  • Re: query unique count for field
    ... Start with such a temp table empty of ... > Having succeeded with Michel's advice, I now have a query with the correct ... >> where table Iotas has a single field, iota, with values from 1 to nnn, ... >> Vanderghast, Access MVP ...
    (microsoft.public.access.queries)
  • Re: Spin calling John 100% identity (Human & Gekko)
    ... Unfortunately there are no other sequences to use in checking the accuracy of that EST, i.e. no Gekko atpase sequences, and the study the sequence comes from is unpublished. ... Query 4427 ... Sbjct 6 ... TCTGACATGGGGCCACCCCACAGGTCAGAGTGGTGGTAGAACCCCTTCAGGACTCCCAGC 245 ...
    (talk.origins)
  • Re: Informix vs Oracle vs DB2. SQL Query optimization.
    ... but I think you're confused because you don't know Oracle. ... the query chooses to limit TAB_A by the col_id. ... initially we have the collection as geo1. ... case of 270,000 rows in the collection, and 60,000 rows in the temp ...
    (comp.databases.informix)
  • Re: Relationships, back end
    ... query the same, using the linked file directly rather than a temp table. ... |> into tblMovements but into a temp table, ... | adds the EquipmentID field the the temp table". ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Group By Sequential Records
    ... Is there any way to speed up the query? ... in each "sequence" where Group and AltID are the same and ID numbers are ... remains the same throughout each "sequence". ... FROM AddrCent AS Sub ...
    (microsoft.public.access.queries)