A possible solution is to RANK the items and then, use a crosstab query
on
the previous query which supplied the ranks.
To get the rank, a possible solution is:
SELECT a.id, a.item, COUNT(*) AS rank
FROM table2Fields AS a INNER JOIN table2Fields AS b
ON a.id = b.id AND a.item >= b.item
GROUP BY a.id, a.item
That should rank the items, between themselves, for each id values.
Saved
under the name of qu1, the final (crosstab) query can be:
TRANSFORM LAST(item)
SELECT id
FROM qu1
GROUP BY id
PIVOT rank
Hoping it may help,
Vanderghast, Access MVP
Mike I have another side question regarding this "project"
I will post a new thread for it, since it is sort of a different
question.
the title of the post will be MER - ACROSS, if you want to search for
it
and
reply there.
The body of the post will be:
Mike
I have a 2 field table: ID and item
this table is a "child" table. . . therefore the ID field can have the
same
value multiple times. Additionally, there is a limit of 4 itmes per
customer.
ID item
3401 82320
3401 79321
3401 86732
3402 52350
3402 49325
3402 76512
3402 91517
I want to have the result of a query list each ID uniquely once, and
all
the
items associated with it in that same output record:
ID item_1 item_2 item_3 item_4
3401 82320 79321 86732
3402 52350 49325 76512 91517
There used to be a 4GL named FOCUS. In Focus, you could write:
Table File MAINTABLE
List item across
by ID
end
and it would give me what I am looking for.
How do I do this in access?
:
Make a query like:
SELECT *
FROM procedurecodes AS procedurecodes_1
INNER JOIN (procedurecodes
INNER JOIN (ALF
INNER JOIN (pt_ALF
INNER JOIN (encounter
INNER JOIN diagnosis
ON encounter.diagcode = diagnosis.diagcode)
ON pt_ALF.ID_pt_ALF = encounter.ID_pt_ALF)
ON ALF.ALFid = pt_ALF.ALFid)
ON procedurecodes.procedure = encounter.proc_code)
ON procedurecodes_1.procedure = encounter.proc_code2
Note that the ON clause can only refer to tables in its scope. As
example:
(ALF
INNER JOIN (pt_ALF
INNER JOIN (encounter
INNER JOIN diagnosis
ON encounter.diagcode = diagnosis.diagcode)
ON pt_ALF.ID_pt_ALF = encounter.ID_pt_ALF)
ON ALF.ALFid = pt_ALF.ALFid)
the last ON clause can use ALF, pt_alf, encounter, or diagnosis, but
not
procedureCodes_1, neither proceduresCode.
When you are sure that this query 'works', then use its name in the
original query:
SELECT ...
FROM UPIN, HCFA_ADD_FAC32, HCFA_ADD_SUP33, PROVIDERTAXID,
savedQueryHere
GROUP BY ...
HAVING ...
Oh, last thing, the syntax is not [tableName.FieldName] but
[tableName].[fieldName]. Furthermore, since some of the fields will
now
come
from the query, you will have to use queryName.fieldname.
Vanderghast, Access MVP
SELECT UPIN.upin, pt_ALF.ins_PIN, pt_ALF.ins_assgnmt,
HCFA_ADD_SUP33.sup_name, HCFA_ADD_SUP33.sup_street,
HCFA_ADD_SUP33.sup_city_zip, HCFA_ADD_SUP33.sup_tele,
PROVIDERTAXID.provid_taxid, PROVIDERTAXID.SSN_EIN,
encounter.date0service,
pt_ALF.ALFid, pt_ALF.lname, pt_ALF.fname, pt_ALF.dob,
pt_ALF.ID_pt_ALF,
encounter.encounterID, pt_ALF.sex, pt_ALF.[MCR#], pt_ALF.MCR_letter,
pt_ALF.ssn, ALF.ALFname, ALF.ALFaddress, ALF.ALFcity, ALF.ALFzip,
ALF.ALFstate, ALF.ALFsite_tele, ALF.ALFadmin_cell,
ALF.ALFowner_cell,
IIf(Len([ALF.ALFsite_tele] &
"")>0,[ALF.ALFsite_tele],IIf(Len([ALF.ALFadmin_cell] &
"")>0,[ALF.ALFadmin_cell],IIf(Len([ALF.ALFowner_cell] &
"")>0,[ALF.ALFowner_cell],[ALF.ALFsite_tele]))) AS TELE,
encounter.proc_code,
procedurecodes.ins_proc_fee, encounter.modifier,
encounter.placecode,
diagnosis.diagcode, pt_ALF.sigonfiledate, encounter.currillness,
encounter.HoldToBill, pt_ALF.[MCD#], pt_ALF.Mpass_no,
pt_ALF.goldcard,
pt_ALF.INStype, encounter.currillness, encounter.billingnotes,
encounter.proc_code2, procedurecodes_1.ins_proc_fee AS fee2,
encounter.modifier2, IIf(Len([encounter.proc_code2] &
"")>0,[encounter.date0service],"") AS date0service2,
IIf(Len([encounter.proc_code2] & "")>0,[encounter.placecode],"") AS
placecode2, IIf(Len([encounter.proc_code3] & "")>0,"1","") AS unit2,
encounter.proc_code3, procedurecodes_2.ins_proc_fee AS fee3,
encounter.modifier3, IIf(Len([encounter.proc_code3] &
"")>0,[encounter.date0service],"") AS date0service3,
IIf(Len([encounter.proc_code3] & "")>0,[encounter.placecode],"") AS
placecode3, IIf(Len([encounter.proc_code3] & "")>0,"1","") AS unit3,
encounter.proc_code4, procedurecodes_3.ins_proc_fee AS fee4,
encounter.modifier4, fee4+fee3+fee2+procedurecodes.ins_proc_fee AS
TOTALFEEfour, IIf(Len([encounter.proc_code4] &
"")>0,[encounter.date0service],"") AS date0service4,
IIf(Len([encounter.proc_code4] & "")>0,[encounter.placecode],"") AS
placecode4, IIf(Len([encounter.proc_code4] & "")>0,"1","") AS unit4
FROM UPIN, HCFA_ADD_FAC32, HCFA_ADD_SUP33, PROVIDERTAXID,
procedurecodes
AS
procedurecodes_1 INNER JOIN (procedurecodes INNER JOIN ((ALF INNER
JOIN
pt_ALF ON ALF.ALFid = pt_ALF.ALFid) INNER JOIN (encounter INNER JOIN
diagnosis ON encounter.diagcode = diagnosis.diagcode) ON
pt_ALF.ID_pt_ALF
=
encounter.ID_pt_ALF) ON procedurecodes.procedure =
encounter.proc_code)
ON
procedurecodes_1.procedure = encounter.proc_code2
GROUP BY UPIN.upin, pt_ALF.ins_PIN, pt_ALF.ins_assgnmt,
HCFA_ADD_SUP33.sup_name, HCFA_ADD_SUP33.sup_street,
HCFA_ADD_SUP33.sup_city_zip, HCFA_ADD_SUP33.sup_tele,
PROVIDERTAXID.provid_taxid, PROVIDERTAXID.SSN_EIN,
encounter.date0service,
pt_ALF.ALFid, pt_ALF.lname, pt_ALF.fname, pt_ALF.dob,
pt_ALF.ID_pt_ALF,
encounter.encounterID, pt_ALF.sex, pt_ALF.[MCR#], pt_ALF.MCR_letter,
pt_ALF.ssn, ALF.ALFname, ALF.ALFaddress, ALF.ALFcity, ALF.ALFzip,
ALF.ALFstate, ALF.ALFsite_tele, ALF.ALFadmin_cell,
ALF.ALFowner_cell,
encounter.proc_code, procedurecodes.ins_proc_fee,
encounter.modifier,
encounter.placecode, diagnosis.diagcode, pt_ALF.sigonfiledate,
encounter.currillness, encounter.HoldToBill, pt_ALF.[MCD#],
pt_ALF.Mpass_no,
pt_ALF.goldcard, pt_ALF.INStype, encounter.currillness,
encounter.billingnotes, encounter.proc_code2,
procedurecodes_1.ins_proc_fee,
encounter.modifier2, encounter.proc_code3,
procedurecodes_2.ins_proc_fee,
encounter.modifier3, encounter.proc_code4,
procedurecodes_3.ins_proc_fee,
encounter.modifier4, encounter.billed
HAVING (((encounter.placecode)="13" Or (encounter.placecode)="31" Or
(encounter.placecode)="32" Or (encounter.placecode)="12") AND
((encounter.HoldToBill)=False) AND ((pt_ALF.INStype)="mcr") AND
((encounter.billed)=False));
this code works fine. as soon as you add multiple occurences of
procedurecodes you drop records. if you change the join to option 2
or
3
in
any combination, you get error message: ambiguous or unsupported.
