Not sure why!

[PeterM]

I have a table:
Pain diary
PD_Date
PD_Med_1_Name
PD_Med_1_Dosage
PD_Med_2_Name
PD_Med_2_Dosage
PD_Med_3_Name
PD_Med_3_Dosage
PD_Med_4_Name
PD_Med_4_Dosage

I plan on running 4 insert into queries to populate the table PancreasMeds

I need to sum the dosages of each MedName, so I created the following query
to sum the values for Med 1

INSERT INTO PancreasMeds
SELECT PD_Date AS PD_Date, PD_Med_1_Name AS PD_Med_1_Name,
Sum(PD_Med_1_Dosage) AS [Sum Of PD_Med_1_Dosage]
FROM Pain_Diary
GROUP BY Pain_Diary.PD_Date, Pain_Diary.PD_Med_1_Name;

which works fine.... I need to do this for the other 3 meds so I created

INSERT INTO PancreasMeds
SELECT PD_Date AS PD_Date, PD_Med_2_Name AS PD_Med_2_Name,
Sum(PD_Med_2_Dosage) AS [Sum Of PD_Med_2_Dosage]
FROM Pain_Diary
GROUP BY Pain_Diary.PD_Date, Pain_Diary.PD_Med_2_Name;

and repeated it for the other 2 meds. When I go to run the query above (Med
2) it complains "THE INTO STATEMENT CONTAINS THE FOLLOWING UNKNOWN FIELD
NAME: PD_MED_2_NAME. MAKE SURE YOU HAVE TYPED THE NAME CORRECTLY, AND TRY
THE OPERATION LATER.

I verified that all references are typed correctly. I get the same message
for the queries for Med 3 and Med 4.... What am I doing wrong?

Thank you very much!

 

[KARL DEWEY]

What am I doing wrong?
Well first off your table structure is wrong - more later.

The problem is where you use an existing field name for a new field name.
PD_Date AS PD_Date,
PD_Med_2_Name AS PD_Med_2_Name,

Just do this --
INSERT INTO PancreasMeds
SELECT PD_Date, PD_Med_2_Name, Sum(PD_Med_2_Dosage) AS [Sum Of
PD_Med_2_Dosage]
FROM Pain_Diary
GROUP BY Pain_Diary.PD_Date, Pain_Diary.PD_Med_2_Name;

Your table needs to be like this --
Pain_diary --
Pain_MedID - primary key
PatientID - foreign key relation to patient table
PD_Date
PD_Med_Name
PD_Med_Dosage

When there are multiple medication in a day they get entered in separate
records. Then it is so EASY to add them up.

 

[PeterM]

Karl...

Thanks for responding so quickly, However I copied and pasted your solution
into a new query and got the same error message.

BTW, I would NEVER design a table with the structure that I show. I would
normalize it the way you indicated, but I inherited this problem. Do you
have any other ideas why it won't work?

Thanks again!

Well first off your table structure is wrong - more later.

The problem is where you use an existing field name for a new field name.
PD_Date AS PD_Date,
PD_Med_2_Name AS PD_Med_2_Name,

Just do this --
INSERT INTO PancreasMeds
SELECT PD_Date, PD_Med_2_Name, Sum(PD_Med_2_Dosage) AS [Sum Of
PD_Med_2_Dosage]
FROM Pain_Diary
GROUP BY Pain_Diary.PD_Date, Pain_Diary.PD_Med_2_Name;

Your table needs to be like this --
Pain_diary --
Pain_MedID - primary key
PatientID - foreign key relation to patient table
PD_Date
PD_Med_Name
PD_Med_Dosage

When there are multiple medication in a day they get entered in separate
records. Then it is so EASY to add them up.


--
Build a little, test a little.

I have a table:
Pain diary
PD_Date
PD_Med_1_Name
PD_Med_1_Dosage
PD_Med_2_Name
PD_Med_2_Dosage
PD_Med_3_Name
PD_Med_3_Dosage
PD_Med_4_Name
PD_Med_4_Dosage

I plan on running 4 insert into queries to populate the table PancreasMeds

I need to sum the dosages of each MedName, so I created the following query
to sum the values for Med 1

INSERT INTO PancreasMeds
SELECT PD_Date AS PD_Date, PD_Med_1_Name AS PD_Med_1_Name,
Sum(PD_Med_1_Dosage) AS [Sum Of PD_Med_1_Dosage]
FROM Pain_Diary
GROUP BY Pain_Diary.PD_Date, Pain_Diary.PD_Med_1_Name;

which works fine.... I need to do this for the other 3 meds so I created

INSERT INTO PancreasMeds
SELECT PD_Date AS PD_Date, PD_Med_2_Name AS PD_Med_2_Name,
Sum(PD_Med_2_Dosage) AS [Sum Of PD_Med_2_Dosage]
FROM Pain_Diary
GROUP BY Pain_Diary.PD_Date, Pain_Diary.PD_Med_2_Name;

and repeated it for the other 2 meds. When I go to run the query above (Med
2) it complains "THE INTO STATEMENT CONTAINS THE FOLLOWING UNKNOWN FIELD
NAME: PD_MED_2_NAME. MAKE SURE YOU HAVE TYPED THE NAME CORRECTLY, AND TRY
THE OPERATION LATER.

I verified that all references are typed correctly. I get the same message
for the queries for Med 3 and Med 4.... What am I doing wrong?

Thank you very much!

 

[PeterM]

Got it... when I changed all of the "as" names to the names of the column in
the table, it worked fine....thanks again for your help!

Karl...

Thanks for responding so quickly, However I copied and pasted your solution
into a new query and got the same error message.

BTW, I would NEVER design a table with the structure that I show. I would
normalize it the way you indicated, but I inherited this problem. Do you
have any other ideas why it won't work?

Thanks again!

What am I doing wrong?

Well first off your table structure is wrong - more later.

The problem is where you use an existing field name for a new field name.
PD_Date AS PD_Date,
PD_Med_2_Name AS PD_Med_2_Name,

Just do this --
INSERT INTO PancreasMeds
SELECT PD_Date, PD_Med_2_Name, Sum(PD_Med_2_Dosage) AS [Sum Of
PD_Med_2_Dosage]
FROM Pain_Diary
GROUP BY Pain_Diary.PD_Date, Pain_Diary.PD_Med_2_Name;

Your table needs to be like this --
Pain_diary --
Pain_MedID - primary key
PatientID - foreign key relation to patient table
PD_Date
PD_Med_Name
PD_Med_Dosage

When there are multiple medication in a day they get entered in separate
records. Then it is so EASY to add them up.


--
Build a little, test a little.

I have a table:
Pain diary
PD_Date
PD_Med_1_Name
PD_Med_1_Dosage
PD_Med_2_Name
PD_Med_2_Dosage
PD_Med_3_Name
PD_Med_3_Dosage
PD_Med_4_Name
PD_Med_4_Dosage

I plan on running 4 insert into queries to populate the table PancreasMeds

I need to sum the dosages of each MedName, so I created the following query
to sum the values for Med 1

INSERT INTO PancreasMeds
SELECT PD_Date AS PD_Date, PD_Med_1_Name AS PD_Med_1_Name,
Sum(PD_Med_1_Dosage) AS [Sum Of PD_Med_1_Dosage]
FROM Pain_Diary
GROUP BY Pain_Diary.PD_Date, Pain_Diary.PD_Med_1_Name;

which works fine.... I need to do this for the other 3 meds so I created

INSERT INTO PancreasMeds
SELECT PD_Date AS PD_Date, PD_Med_2_Name AS PD_Med_2_Name,
Sum(PD_Med_2_Dosage) AS [Sum Of PD_Med_2_Dosage]
FROM Pain_Diary
GROUP BY Pain_Diary.PD_Date, Pain_Diary.PD_Med_2_Name;

and repeated it for the other 2 meds. When I go to run the query above (Med
2) it complains "THE INTO STATEMENT CONTAINS THE FOLLOWING UNKNOWN FIELD
NAME: PD_MED_2_NAME. MAKE SURE YOU HAVE TYPED THE NAME CORRECTLY, AND TRY
THE OPERATION LATER.

I verified that all references are typed correctly. I get the same message
for the queries for Med 3 and Med 4.... What am I doing wrong?

Thank you very much!

 

[KARL DEWEY]

You can use a union query to align the data --
SELECT PD_Date, PD_Med_1_Name AS PD_MED, PD_Med_1_Dosage AS PD_Dosage
FROM Pain_Diary
WHERE PD_Med_1_Name Is Not Null AND PD_Med_1_Dosage Is Not Null
UNION ALL SELECT PD_Date, PD_Med_2_Name AS PD_MED, PD_Med_2_Dosage AS
PD_Dosage
FROM Pain_Diary
WHERE PD_Med_2_Name Is Not Null AND PD_Med_2_Dosage Is Not Null
UNION ALL SELECT PD_Date, PD_Med_3_Name AS PD_MED, PD_Med_3_Dosage AS
PD_Dosage
FROM Pain_Diary
WHERE PD_Med_3_Name Is Not Null AND PD_Med_3_Dosage Is Not Null
UNION ALL SELECT PD_Date, PD_Med_4_Name AS PD_MED, PD_Med_4_Dosage AS
PD_Dosage
FROM Pain_Diary
WHERE PD_Med_4_Name Is Not Null AND PD_Med_4_Dosage Is Not Null;

Then it will be easy to total.

--
Build a little, test a little.

Got it... when I changed all of the "as" names to the names of the column in
the table, it worked fine....thanks again for your help!

Karl...

Thanks for responding so quickly, However I copied and pasted your solution
into a new query and got the same error message.

BTW, I would NEVER design a table with the structure that I show. I would
normalize it the way you indicated, but I inherited this problem. Do you
have any other ideas why it won't work?

Thanks again!

What am I doing wrong?
Well first off your table structure is wrong - more later.

The problem is where you use an existing field name for a new field name.
PD_Date AS PD_Date,
PD_Med_2_Name AS PD_Med_2_Name,

Just do this --
INSERT INTO PancreasMeds
SELECT PD_Date, PD_Med_2_Name, Sum(PD_Med_2_Dosage) AS [Sum Of
PD_Med_2_Dosage]
FROM Pain_Diary
GROUP BY Pain_Diary.PD_Date, Pain_Diary.PD_Med_2_Name;

Your table needs to be like this --
Pain_diary --
Pain_MedID - primary key
PatientID - foreign key relation to patient table
PD_Date
PD_Med_Name
PD_Med_Dosage

When there are multiple medication in a day they get entered in separate
records. Then it is so EASY to add them up.


--
Build a little, test a little.


:

I have a table:
Pain diary
PD_Date
PD_Med_1_Name
PD_Med_1_Dosage
PD_Med_2_Name
PD_Med_2_Dosage
PD_Med_3_Name
PD_Med_3_Dosage
PD_Med_4_Name
PD_Med_4_Dosage

I plan on running 4 insert into queries to populate the table PancreasMeds

I need to sum the dosages of each MedName, so I created the following query
to sum the values for Med 1

INSERT INTO PancreasMeds
SELECT PD_Date AS PD_Date, PD_Med_1_Name AS PD_Med_1_Name,
Sum(PD_Med_1_Dosage) AS [Sum Of PD_Med_1_Dosage]
FROM Pain_Diary
GROUP BY Pain_Diary.PD_Date, Pain_Diary.PD_Med_1_Name;

which works fine.... I need to do this for the other 3 meds so I created

INSERT INTO PancreasMeds
SELECT PD_Date AS PD_Date, PD_Med_2_Name AS PD_Med_2_Name,
Sum(PD_Med_2_Dosage) AS [Sum Of PD_Med_2_Dosage]
FROM Pain_Diary
GROUP BY Pain_Diary.PD_Date, Pain_Diary.PD_Med_2_Name;

and repeated it for the other 2 meds. When I go to run the query above (Med
2) it complains "THE INTO STATEMENT CONTAINS THE FOLLOWING UNKNOWN FIELD
NAME: PD_MED_2_NAME. MAKE SURE YOU HAVE TYPED THE NAME CORRECTLY, AND TRY
THE OPERATION LATER.

I verified that all references are typed correctly. I get the same message
for the queries for Med 3 and Med 4.... What am I doing wrong?

Thank you very much!