Combining different values in the same field in a query

  • Thread starter Thread starter Alp Bekisoglu
  • Start date Start date
A

Alp Bekisoglu

Hi,

Having a hard time coming up with (field names are simplified for ease of
reference):
1999 1 xxxxxxx & yyyyyyy 362 R
as a result from querying a table data which has entries like:
ya pid p_no days
1999 1 xxxxxxx 150
1999 1 xxxxxxx 62
1999 1 yyyyyyy 120
1999 1 yyyyyyy 30

R is a calculated value with respect to the total of "days"
ya, pid & days are numeric
p_no is text

There can be more than one p_no's in a year and that's where I'm stuck at
the moment. The actual SQL is:
SELECT gunler.YA, gunler.KISI_ID, gunler.Pasaport_no, cp4_a.citizenship,
Sum(gunler.DaysInMY) AS SumOfDaysInMY,
IIf(Left([citizenship],5)="Malay","Resident",IIf([SumOfDaysInMY]>=182,"Resid
ent","Non-Resident")) AS stat, cp4_a.y_a, Count(gunler.Pasaport_no) AS
CountOfPasaport_no
FROM gunler, cp4_a
GROUP BY gunler.YA, gunler.KISI_ID, gunler.Pasaport_no, cp4_a.citizenship,
cp4_a.y_a

which gives me 2 lines of result with respect to the two different p_no
values. The R also changes according to this which should not be the case
since it should depend on the total of "days" regardless of the "p_no" as
long as the "ya" is same and "pid" is same respectively.

Any help is much appreciated.

TIA

Alp
 
How many p_no's need concatentated? Is it always just two, or can it be
infinite?
 
Hi Steve,

This field actually is the passport number of a person and might be more
than two if the individual is travelling a lot but it can not be infinite of
course. We could accept a fixed value for instance as 5 or I guess it could
be obtained via the query itself for a given year since the query will
always deal with one year at a time.

Is there a way to achieve what I'm after under the light of the above info?

Thanks,

Alp

[MVP] S.Clark said:
How many p_no's need concatentated? Is it always just two, or can it be
infinite?

Alp Bekisoglu said:
Hi,

Having a hard time coming up with (field names are simplified for ease of
reference):
1999 1 xxxxxxx & yyyyyyy 362 R
as a result from querying a table data which has entries like:
ya pid p_no days
1999 1 xxxxxxx 150
1999 1 xxxxxxx 62
1999 1 yyyyyyy 120
1999 1 yyyyyyy 30

R is a calculated value with respect to the total of "days"
ya, pid & days are numeric
p_no is text

There can be more than one p_no's in a year and that's where I'm stuck at
the moment. The actual SQL is:
SELECT gunler.YA, gunler.KISI_ID, gunler.Pasaport_no, cp4_a.citizenship,
Sum(gunler.DaysInMY) AS SumOfDaysInMY,
Click to expand...
IIf(Left([citizenship],5)="Malay","Resident",IIf([SumOfDaysInMY]>=182,"Resid
ent","Non-Resident")) AS stat, cp4_a.y_a, Count(gunler.Pasaport_no) AS
CountOfPasaport_no
FROM gunler, cp4_a
GROUP BY gunler.YA, gunler.KISI_ID, gunler.Pasaport_no, cp4_a.citizenship,
cp4_a.y_a

which gives me 2 lines of result with respect to the two different p_no
values. The R also changes according to this which should not be the case
since it should depend on the total of "days" regardless of the "p_no" as
long as the "ya" is same and "pid" is same respectively.

Any help is much appreciated.

TIA

Alp
Click to expand...
Click to expand...
 
There is a generic Concatenate function that you can use to concatenate
values from multiple into a single expression. Check
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP


Alp Bekisoglu said:
Hi Steve,

This field actually is the passport number of a person and might be more
than two if the individual is travelling a lot but it can not be infinite of
course. We could accept a fixed value for instance as 5 or I guess it could
be obtained via the query itself for a given year since the query will
always deal with one year at a time.

Is there a way to achieve what I'm after under the light of the above info?

Thanks,

Alp

[MVP] S.Clark said:
How many p_no's need concatentated? Is it always just two, or can it be
infinite?
Click to expand...
IIf(Left([citizenship],5)="Malay","Resident",IIf([SumOfDaysInMY]>=182,"Resid
Click to expand...
 
Thanks Duane I'll try that.

Alp

Duane Hookom said:
There is a generic Concatenate function that you can use to concatenate
values from multiple into a single expression. Check
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP


Alp Bekisoglu said:
Hi Steve,

This field actually is the passport number of a person and might be more
than two if the individual is travelling a lot but it can not be
Click to expand...
infinite
of
course. We could accept a fixed value for instance as 5 or I guess it could
be obtained via the query itself for a given year since the query will
always deal with one year at a time.

Is there a way to achieve what I'm after under the light of the above info?

Thanks,

Alp

ease
of stuck
at
Click to expand...
IIf(Left([citizenship],5)="Malay","Resident",IIf([SumOfDaysInMY]>=182,"Resid
"p_no"
as
Click to expand...
Click to expand...
 
Back
Top