Crosstab Query Troubles Again

G

Guest

Group,

I am having a problem. I have the following queries(#1-#3) that result in
the final answer of 0. That is not the problem, If I add
pip_count_2_weight_loss(#3) to my final query pip_REPORT_end(#4) I get the
following Error: The Microsoft Office Access database engine does not
recognize " as a valid field name or expression. I am not sure why it is
resulting in this error. I am sure it is cascaded from my crosstab query...

#1 weight_last_90_qry

PARAMETERS [Forms]![Open_PIP Report]![DateStart] DateTime;
TRANSFORM First(WeightTbl.Weight) AS FirstOfWeight1
SELECT WeightTbl.RID
FROM Residents INNER JOIN WeightTbl ON Residents.RID = WeightTbl.RID
WHERE (((WeightTbl.DateOf)>=[Forms]![Open_PIP Report]![DateStart] And
(WeightTbl.DateOf)<DateAdd("m",4,[Forms]![Open_PIP Report]![DateStart]))) OR
(((WeightTbl.DateOf)=nz([DateOf],0)))
GROUP BY WeightTbl.RID
PIVOT "Month" & DateDiff("m",Date()-120,[DateOf])+1;

#2 pip_two_weight_loss

SELECT weight_last_90_qry.RID, Residents.[Last, First, Initial]
FROM weight_last_90_qry INNER JOIN Residents ON weight_last_90_qry.RID =
Residents.RID
GROUP BY weight_last_90_qry.RID, Residents.[Last, First, Initial],
[Month1]-[Month2]
HAVING ((([Month1]-[Month2])<-2 Or ([Month1]-[Month2])>2));

#3 pip_count_2_weight_loss

SELECT Count(*) AS CountTwo
FROM pip_two_weight_loss;

#4 pip_REPORT_end

SELECT pip_pressure_end.[Facility aquired pressure wounds],
pip_count_of_residents_on_weekly_weights.[Count of Residents on Weekly
Weights], pip_census_no_longer_in_facility.Total, pip_fall1_count_end.[Falls
w/o Injury], sys_setup_values.[Weekly Weight Monitor Comment],
pip_fall2_count_end.[Falls w/ injury],
pip_restraints_siderails_end.[Residents w/ Siderails],
pip_restraints_other_end.[Residents w/o Siderails], how_many_residents.[Count
Of Residents], pip_hospice_end.[Residents in Hospice Care],
sys_setup_values.[# of Licensed Nurses Employed], sys_setup_values.[Licensed
Nurse Vacancy], sys_setup_values.[Licensed Nurse Vacancy Note],
pip_uti_end.[Residents w/ UTI], pip_nosocomial_end.Nosocomial,
sys_setup_values.[Pressure Comment], sys_setup_values.[Falls w/o Injury
Comment], sys_setup_values.[Falls w/ Injury Comment],
sys_setup_values.[Physical Restraints Comment], sys_setup_values.[Siderails
comment], sys_setup_values.[Hospice Care Comment], sys_setup_values.[Weight
Loss Comment], sys_setup_values.[UTI Comment], sys_setup_values.[Nosocomial
Comment], pip_census_no_longer_in_facility.Total
FROM pip_fall1_count_end, pip_pressure_end, pip_fall2_count_end,
pip_restraints_siderails_end, pip_restraints_other_end, pip_hospice_end,
pip_uti_end, pip_nosocomial_end, sys_setup_values, how_many_residents,
pip_census_no_longer_in_facility, pip_count_of_residents_on_weekly_weights;


I ran this through here once,...did I forget to include something? Thanks
to all who always help.

Scottie
 
G

Guest

Try change to
#3 pip_count_2_weight_loss

SELECT Sum(1) AS CountTwo
FROM pip_two_weight_loss;

--
Duane Hookom
Microsoft Access MVP


Scottie said:
Group,

I am having a problem. I have the following queries(#1-#3) that result in
the final answer of 0. That is not the problem, If I add
pip_count_2_weight_loss(#3) to my final query pip_REPORT_end(#4) I get the
following Error: The Microsoft Office Access database engine does not
recognize " as a valid field name or expression. I am not sure why it is
resulting in this error. I am sure it is cascaded from my crosstab query...

#1 weight_last_90_qry

PARAMETERS [Forms]![Open_PIP Report]![DateStart] DateTime;
TRANSFORM First(WeightTbl.Weight) AS FirstOfWeight1
SELECT WeightTbl.RID
FROM Residents INNER JOIN WeightTbl ON Residents.RID = WeightTbl.RID
WHERE (((WeightTbl.DateOf)>=[Forms]![Open_PIP Report]![DateStart] And
(WeightTbl.DateOf)<DateAdd("m",4,[Forms]![Open_PIP Report]![DateStart]))) OR
(((WeightTbl.DateOf)=nz([DateOf],0)))
GROUP BY WeightTbl.RID
PIVOT "Month" & DateDiff("m",Date()-120,[DateOf])+1;

#2 pip_two_weight_loss

SELECT weight_last_90_qry.RID, Residents.[Last, First, Initial]
FROM weight_last_90_qry INNER JOIN Residents ON weight_last_90_qry.RID =
Residents.RID
GROUP BY weight_last_90_qry.RID, Residents.[Last, First, Initial],
[Month1]-[Month2]
HAVING ((([Month1]-[Month2])<-2 Or ([Month1]-[Month2])>2));

#3 pip_count_2_weight_loss

SELECT Count(*) AS CountTwo
FROM pip_two_weight_loss;

#4 pip_REPORT_end

SELECT pip_pressure_end.[Facility aquired pressure wounds],
pip_count_of_residents_on_weekly_weights.[Count of Residents on Weekly
Weights], pip_census_no_longer_in_facility.Total, pip_fall1_count_end.[Falls
w/o Injury], sys_setup_values.[Weekly Weight Monitor Comment],
pip_fall2_count_end.[Falls w/ injury],
pip_restraints_siderails_end.[Residents w/ Siderails],
pip_restraints_other_end.[Residents w/o Siderails], how_many_residents.[Count
Of Residents], pip_hospice_end.[Residents in Hospice Care],
sys_setup_values.[# of Licensed Nurses Employed], sys_setup_values.[Licensed
Nurse Vacancy], sys_setup_values.[Licensed Nurse Vacancy Note],
pip_uti_end.[Residents w/ UTI], pip_nosocomial_end.Nosocomial,
sys_setup_values.[Pressure Comment], sys_setup_values.[Falls w/o Injury
Comment], sys_setup_values.[Falls w/ Injury Comment],
sys_setup_values.[Physical Restraints Comment], sys_setup_values.[Siderails
comment], sys_setup_values.[Hospice Care Comment], sys_setup_values.[Weight
Loss Comment], sys_setup_values.[UTI Comment], sys_setup_values.[Nosocomial
Comment], pip_census_no_longer_in_facility.Total
FROM pip_fall1_count_end, pip_pressure_end, pip_fall2_count_end,
pip_restraints_siderails_end, pip_restraints_other_end, pip_hospice_end,
pip_uti_end, pip_nosocomial_end, sys_setup_values, how_many_residents,
pip_census_no_longer_in_facility, pip_count_of_residents_on_weekly_weights;


I ran this through here once,...did I forget to include something? Thanks
to all who always help.

Scottie
Click to expand...
 
G

Guest

Duane,

I am receiving the same error....got any ideas?

Duane Hookom said:
Try change to
#3 pip_count_2_weight_loss

SELECT Sum(1) AS CountTwo
FROM pip_two_weight_loss;

--
Duane Hookom
Microsoft Access MVP


Scottie said:
Group,

I am having a problem. I have the following queries(#1-#3) that result in
the final answer of 0. That is not the problem, If I add
pip_count_2_weight_loss(#3) to my final query pip_REPORT_end(#4) I get the
following Error: The Microsoft Office Access database engine does not
recognize " as a valid field name or expression. I am not sure why it is
resulting in this error. I am sure it is cascaded from my crosstab query...

#1 weight_last_90_qry

PARAMETERS [Forms]![Open_PIP Report]![DateStart] DateTime;
TRANSFORM First(WeightTbl.Weight) AS FirstOfWeight1
SELECT WeightTbl.RID
FROM Residents INNER JOIN WeightTbl ON Residents.RID = WeightTbl.RID
WHERE (((WeightTbl.DateOf)>=[Forms]![Open_PIP Report]![DateStart] And
(WeightTbl.DateOf)<DateAdd("m",4,[Forms]![Open_PIP Report]![DateStart]))) OR
(((WeightTbl.DateOf)=nz([DateOf],0)))
GROUP BY WeightTbl.RID
PIVOT "Month" & DateDiff("m",Date()-120,[DateOf])+1;

#2 pip_two_weight_loss

SELECT weight_last_90_qry.RID, Residents.[Last, First, Initial]
FROM weight_last_90_qry INNER JOIN Residents ON weight_last_90_qry.RID =
Residents.RID
GROUP BY weight_last_90_qry.RID, Residents.[Last, First, Initial],
[Month1]-[Month2]
HAVING ((([Month1]-[Month2])<-2 Or ([Month1]-[Month2])>2));

#3 pip_count_2_weight_loss

SELECT Count(*) AS CountTwo
FROM pip_two_weight_loss;

#4 pip_REPORT_end

SELECT pip_pressure_end.[Facility aquired pressure wounds],
pip_count_of_residents_on_weekly_weights.[Count of Residents on Weekly
Weights], pip_census_no_longer_in_facility.Total, pip_fall1_count_end.[Falls
w/o Injury], sys_setup_values.[Weekly Weight Monitor Comment],
pip_fall2_count_end.[Falls w/ injury],
pip_restraints_siderails_end.[Residents w/ Siderails],
pip_restraints_other_end.[Residents w/o Siderails], how_many_residents.[Count
Of Residents], pip_hospice_end.[Residents in Hospice Care],
sys_setup_values.[# of Licensed Nurses Employed], sys_setup_values.[Licensed
Nurse Vacancy], sys_setup_values.[Licensed Nurse Vacancy Note],
pip_uti_end.[Residents w/ UTI], pip_nosocomial_end.Nosocomial,
sys_setup_values.[Pressure Comment], sys_setup_values.[Falls w/o Injury
Comment], sys_setup_values.[Falls w/ Injury Comment],
sys_setup_values.[Physical Restraints Comment], sys_setup_values.[Siderails
comment], sys_setup_values.[Hospice Care Comment], sys_setup_values.[Weight
Loss Comment], sys_setup_values.[UTI Comment], sys_setup_values.[Nosocomial
Comment], pip_census_no_longer_in_facility.Total
FROM pip_fall1_count_end, pip_pressure_end, pip_fall2_count_end,
pip_restraints_siderails_end, pip_restraints_other_end, pip_hospice_end,
pip_uti_end, pip_nosocomial_end, sys_setup_values, how_many_residents,
pip_census_no_longer_in_facility, pip_count_of_residents_on_weekly_weights;


I ran this through here once,...did I forget to include something? Thanks
to all who always help.

Scottie
Click to expand...
Click to expand...
 
G

Guest

I would try add the column headings into the Column Headings property of
query #1 weight_last_90_qry.

I also noticed there are no joins between query/tables in your final query.
What are you using sys_setup_values for? Is there only one record in this
table?


--
Duane Hookom
Microsoft Access MVP


Scottie said:
Duane,

I am receiving the same error....got any ideas?

Duane Hookom said:
Try change to
#3 pip_count_2_weight_loss

SELECT Sum(1) AS CountTwo
FROM pip_two_weight_loss;

--
Duane Hookom
Microsoft Access MVP


Scottie said:
Group,

I am having a problem. I have the following queries(#1-#3) that result in
the final answer of 0. That is not the problem, If I add
pip_count_2_weight_loss(#3) to my final query pip_REPORT_end(#4) I get the
following Error: The Microsoft Office Access database engine does not
recognize " as a valid field name or expression. I am not sure why it is
resulting in this error. I am sure it is cascaded from my crosstab query...

#1 weight_last_90_qry

PARAMETERS [Forms]![Open_PIP Report]![DateStart] DateTime;
TRANSFORM First(WeightTbl.Weight) AS FirstOfWeight1
SELECT WeightTbl.RID
FROM Residents INNER JOIN WeightTbl ON Residents.RID = WeightTbl.RID
WHERE (((WeightTbl.DateOf)>=[Forms]![Open_PIP Report]![DateStart] And
(WeightTbl.DateOf)<DateAdd("m",4,[Forms]![Open_PIP Report]![DateStart]))) OR
(((WeightTbl.DateOf)=nz([DateOf],0)))
GROUP BY WeightTbl.RID
PIVOT "Month" & DateDiff("m",Date()-120,[DateOf])+1;

#2 pip_two_weight_loss

SELECT weight_last_90_qry.RID, Residents.[Last, First, Initial]
FROM weight_last_90_qry INNER JOIN Residents ON weight_last_90_qry.RID =
Residents.RID
GROUP BY weight_last_90_qry.RID, Residents.[Last, First, Initial],
[Month1]-[Month2]
HAVING ((([Month1]-[Month2])<-2 Or ([Month1]-[Month2])>2));

#3 pip_count_2_weight_loss

SELECT Count(*) AS CountTwo
FROM pip_two_weight_loss;

#4 pip_REPORT_end

SELECT pip_pressure_end.[Facility aquired pressure wounds],
pip_count_of_residents_on_weekly_weights.[Count of Residents on Weekly
Weights], pip_census_no_longer_in_facility.Total, pip_fall1_count_end.[Falls
w/o Injury], sys_setup_values.[Weekly Weight Monitor Comment],
pip_fall2_count_end.[Falls w/ injury],
pip_restraints_siderails_end.[Residents w/ Siderails],
pip_restraints_other_end.[Residents w/o Siderails], how_many_residents.[Count
Of Residents], pip_hospice_end.[Residents in Hospice Care],
sys_setup_values.[# of Licensed Nurses Employed], sys_setup_values.[Licensed
Nurse Vacancy], sys_setup_values.[Licensed Nurse Vacancy Note],
pip_uti_end.[Residents w/ UTI], pip_nosocomial_end.Nosocomial,
sys_setup_values.[Pressure Comment], sys_setup_values.[Falls w/o Injury
Comment], sys_setup_values.[Falls w/ Injury Comment],
sys_setup_values.[Physical Restraints Comment], sys_setup_values.[Siderails
comment], sys_setup_values.[Hospice Care Comment], sys_setup_values.[Weight
Loss Comment], sys_setup_values.[UTI Comment], sys_setup_values.[Nosocomial
Comment], pip_census_no_longer_in_facility.Total
FROM pip_fall1_count_end, pip_pressure_end, pip_fall2_count_end,
pip_restraints_siderails_end, pip_restraints_other_end, pip_hospice_end,
pip_uti_end, pip_nosocomial_end, sys_setup_values, how_many_residents,
pip_census_no_longer_in_facility, pip_count_of_residents_on_weekly_weights;


I ran this through here once,...did I forget to include something? Thanks
to all who always help.

Scottie
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Duane,

Looks like the column heading was the problem...everything seems to be
calculating correctly now. The sys_setup_values table is to store the
various dates and numbers that apply to specific resident facilities. There
is only one record in the table. Just out of curiosity, how do you prevent
more records from being created while still being able to editt the one and
only record.

Thank you so much for helping me

Duane Hookom said:
I would try add the column headings into the Column Headings property of
query #1 weight_last_90_qry.

I also noticed there are no joins between query/tables in your final query.
What are you using sys_setup_values for? Is there only one record in this
table?


--
Duane Hookom
Microsoft Access MVP


Scottie said:
Duane,

I am receiving the same error....got any ideas?

Duane Hookom said:
Try change to
#3 pip_count_2_weight_loss

SELECT Sum(1) AS CountTwo
FROM pip_two_weight_loss;

--
Duane Hookom
Microsoft Access MVP


:

Group,

I am having a problem. I have the following queries(#1-#3) that result in
the final answer of 0. That is not the problem, If I add
pip_count_2_weight_loss(#3) to my final query pip_REPORT_end(#4) I get the
following Error: The Microsoft Office Access database engine does not
recognize " as a valid field name or expression. I am not sure why it is
resulting in this error. I am sure it is cascaded from my crosstab query...

#1 weight_last_90_qry

PARAMETERS [Forms]![Open_PIP Report]![DateStart] DateTime;
TRANSFORM First(WeightTbl.Weight) AS FirstOfWeight1
SELECT WeightTbl.RID
FROM Residents INNER JOIN WeightTbl ON Residents.RID = WeightTbl.RID
WHERE (((WeightTbl.DateOf)>=[Forms]![Open_PIP Report]![DateStart] And
(WeightTbl.DateOf)<DateAdd("m",4,[Forms]![Open_PIP Report]![DateStart]))) OR
(((WeightTbl.DateOf)=nz([DateOf],0)))
GROUP BY WeightTbl.RID
PIVOT "Month" & DateDiff("m",Date()-120,[DateOf])+1;

#2 pip_two_weight_loss

SELECT weight_last_90_qry.RID, Residents.[Last, First, Initial]
FROM weight_last_90_qry INNER JOIN Residents ON weight_last_90_qry.RID =
Residents.RID
GROUP BY weight_last_90_qry.RID, Residents.[Last, First, Initial],
[Month1]-[Month2]
HAVING ((([Month1]-[Month2])<-2 Or ([Month1]-[Month2])>2));

#3 pip_count_2_weight_loss

SELECT Count(*) AS CountTwo
FROM pip_two_weight_loss;

#4 pip_REPORT_end

SELECT pip_pressure_end.[Facility aquired pressure wounds],
pip_count_of_residents_on_weekly_weights.[Count of Residents on Weekly
Weights], pip_census_no_longer_in_facility.Total, pip_fall1_count_end.[Falls
w/o Injury], sys_setup_values.[Weekly Weight Monitor Comment],
pip_fall2_count_end.[Falls w/ injury],
pip_restraints_siderails_end.[Residents w/ Siderails],
pip_restraints_other_end.[Residents w/o Siderails], how_many_residents.[Count
Of Residents], pip_hospice_end.[Residents in Hospice Care],
sys_setup_values.[# of Licensed Nurses Employed], sys_setup_values.[Licensed
Nurse Vacancy], sys_setup_values.[Licensed Nurse Vacancy Note],
pip_uti_end.[Residents w/ UTI], pip_nosocomial_end.Nosocomial,
sys_setup_values.[Pressure Comment], sys_setup_values.[Falls w/o Injury
Comment], sys_setup_values.[Falls w/ Injury Comment],
sys_setup_values.[Physical Restraints Comment], sys_setup_values.[Siderails
comment], sys_setup_values.[Hospice Care Comment], sys_setup_values.[Weight
Loss Comment], sys_setup_values.[UTI Comment], sys_setup_values.[Nosocomial
Comment], pip_census_no_longer_in_facility.Total
FROM pip_fall1_count_end, pip_pressure_end, pip_fall2_count_end,
pip_restraints_siderails_end, pip_restraints_other_end, pip_hospice_end,
pip_uti_end, pip_nosocomial_end, sys_setup_values, how_many_residents,
pip_census_no_longer_in_facility, pip_count_of_residents_on_weekly_weights;


I ran this through here once,...did I forget to include something? Thanks
to all who always help.

Scottie
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

If you only want a single record in a table, you can only allow edits through
a form that doesn't allow inserting of records. You could also create an
autonumber primary key and set a constraint that doesn't allow values greater
than 1.
--
Duane Hookom
Microsoft Access MVP


Scottie said:
Duane,

Looks like the column heading was the problem...everything seems to be
calculating correctly now. The sys_setup_values table is to store the
various dates and numbers that apply to specific resident facilities. There
is only one record in the table. Just out of curiosity, how do you prevent
more records from being created while still being able to editt the one and
only record.

Thank you so much for helping me

Duane Hookom said:
I would try add the column headings into the Column Headings property of
query #1 weight_last_90_qry.

I also noticed there are no joins between query/tables in your final query.
What are you using sys_setup_values for? Is there only one record in this
table?


--
Duane Hookom
Microsoft Access MVP


Scottie said:
Duane,

I am receiving the same error....got any ideas?

:

Try change to
#3 pip_count_2_weight_loss

SELECT Sum(1) AS CountTwo
FROM pip_two_weight_loss;

--
Duane Hookom
Microsoft Access MVP


:

Group,

I am having a problem. I have the following queries(#1-#3) that result in
the final answer of 0. That is not the problem, If I add
pip_count_2_weight_loss(#3) to my final query pip_REPORT_end(#4) I get the
following Error: The Microsoft Office Access database engine does not
recognize " as a valid field name or expression. I am not sure why it is
resulting in this error. I am sure it is cascaded from my crosstab query...

#1 weight_last_90_qry

PARAMETERS [Forms]![Open_PIP Report]![DateStart] DateTime;
TRANSFORM First(WeightTbl.Weight) AS FirstOfWeight1
SELECT WeightTbl.RID
FROM Residents INNER JOIN WeightTbl ON Residents.RID = WeightTbl.RID
WHERE (((WeightTbl.DateOf)>=[Forms]![Open_PIP Report]![DateStart] And
(WeightTbl.DateOf)<DateAdd("m",4,[Forms]![Open_PIP Report]![DateStart]))) OR
(((WeightTbl.DateOf)=nz([DateOf],0)))
GROUP BY WeightTbl.RID
PIVOT "Month" & DateDiff("m",Date()-120,[DateOf])+1;

#2 pip_two_weight_loss

SELECT weight_last_90_qry.RID, Residents.[Last, First, Initial]
FROM weight_last_90_qry INNER JOIN Residents ON weight_last_90_qry.RID =
Residents.RID
GROUP BY weight_last_90_qry.RID, Residents.[Last, First, Initial],
[Month1]-[Month2]
HAVING ((([Month1]-[Month2])<-2 Or ([Month1]-[Month2])>2));

#3 pip_count_2_weight_loss

SELECT Count(*) AS CountTwo
FROM pip_two_weight_loss;

#4 pip_REPORT_end

SELECT pip_pressure_end.[Facility aquired pressure wounds],
pip_count_of_residents_on_weekly_weights.[Count of Residents on Weekly
Weights], pip_census_no_longer_in_facility.Total, pip_fall1_count_end.[Falls
w/o Injury], sys_setup_values.[Weekly Weight Monitor Comment],
pip_fall2_count_end.[Falls w/ injury],
pip_restraints_siderails_end.[Residents w/ Siderails],
pip_restraints_other_end.[Residents w/o Siderails], how_many_residents.[Count
Of Residents], pip_hospice_end.[Residents in Hospice Care],
sys_setup_values.[# of Licensed Nurses Employed], sys_setup_values.[Licensed
Nurse Vacancy], sys_setup_values.[Licensed Nurse Vacancy Note],
pip_uti_end.[Residents w/ UTI], pip_nosocomial_end.Nosocomial,
sys_setup_values.[Pressure Comment], sys_setup_values.[Falls w/o Injury
Comment], sys_setup_values.[Falls w/ Injury Comment],
sys_setup_values.[Physical Restraints Comment], sys_setup_values.[Siderails
comment], sys_setup_values.[Hospice Care Comment], sys_setup_values.[Weight
Loss Comment], sys_setup_values.[UTI Comment], sys_setup_values.[Nosocomial
Comment], pip_census_no_longer_in_facility.Total
FROM pip_fall1_count_end, pip_pressure_end, pip_fall2_count_end,
pip_restraints_siderails_end, pip_restraints_other_end, pip_hospice_end,
pip_uti_end, pip_nosocomial_end, sys_setup_values, how_many_residents,
pip_census_no_longer_in_facility, pip_count_of_residents_on_weekly_weights;


I ran this through here once,...did I forget to include something? Thanks
to all who always help.

Scottie
Click to expand...
Click to expand...
Click to expand...
 
Top