I need to find a querry to do this

[Emma Chapman 123]

Hi

Please can somebody help me with a querry for this. I have a table which
shows each members test results for each month. On a report that i am
building i need to show the price that the member would get for that
particular test result each month. For example If the result is between 0 &
50 they will receive 0 deductions. If it is between 51 & 60 they will receive
-0.3 pence. There are 23 different bandings for this result.
On my report i need to show it like this below:
200711 200712 200801 200802 200803 200804 200805 200806 200807
Price Price Price Price Price Price Price
Price Price

The actual test results are shown at the top of the report. Each member has
there own report to show the results and prices.

I think that i need a querry to do this but i don't know where to start. If
i was in excel i would use a lookup querry that looks up the bandings table
and looks for the value.

Please could somebosy help me.

Thanks

Emma

 

[Dale Fye]

Emma,

Create a banding table (tbl_Bands) with fields (Lower, Upper, and
Deduction). Then fill it in similar to:

Lower Upper Deduction
0 50 0
51 60 .3
61 65 .5
....

Then, create a query that looks something like:

SELECT T.Member, T.TestDate, T.Score, B.Deduction
FROM tbl_Test_Results as T, tbl_Bands as B
WHERE T.Score >= B.Lower
AND T.Score <= B.Upper

From this query, you can then create a Crosstab query that shows the
TestDates (year and month) across the top and the deduction as the cross tab
values.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Emma Chapman 123]

Hi thank you so much for getting back to me on this one. Just a couple of
questions. Do i need a primary key in the Bands table.
I have created the table like you said and the other table that i have is
called Bacto 12 month. The fields are named as followed:
Member Number 200711 200712 200801 200802 200803 (Field headings)
016832301 50 69 53 50 53
(Data)

I am struggling with the wording of the querry that you wrote and don't
understand what the T or B stands for. Could you help me with the names for
tables and fields that are above.

Again thank you for your help.

Emma

 

[Dale Fye]

Emma,

To start with, the structure of your other table looks like a spreadsheet,
not a relational database table. In a well structured data table, you will
never have a column name that is actually data. In your case, you should
have fields ([Member Number], TestMonth, and Score) and the data should look
like:

Member Number TestMonth Score
016832301 200711 50
016832301 200712 69

I would strongly recommend that you restructure this table in this format.
You can do so with a normalization query that looks something like the
following. You can create the first segment of this in the Query grid, but
you will have to switch to SQL view to add each of the successive TestMonths
data.

SELECT [Member Number], "200711" as TestMonth, [200711] as Score
FROM [Bacto 12 month]
UNION ALL
SELECT [Member Number], "200712" as TestMonth, [200712] as Score
FROM [Bacto 12 month]
UNION ALL
....
SELECT [Member Number], "200811" as TestMonth, [200811] as Score
FROM [Bacto 12 month]

Save this query as qry_TestScores

The T and B I reference in my query are aliases. They are short
abbreviations for the tables that make it easier to read a query, and
significantly shorter to type the SQL string. You don't have to use those if
you don't want to. Using the above query, as your normalizing query, the
query to determine the deductions for each member/month would look like:

SELECT qry_TestScores.Member,
qry_TestScores.TestMonth,
qry_TestScores.Score,
tbl_Bands.Deduction
FROM qry_TestScores, tbl_Bands
WHERE qry_TestScores.Score >= tbl_Bands.Lower
AND qry_TestScores.Score <= tbl_Bands.Upper

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Emma Chapman 123]

Hi

I have done that. But when i tried to run the first querry it says there is
an error saying there is an incomplete querry clause. What does this mean?

Regards

Emma

Emma,

To start with, the structure of your other table looks like a spreadsheet,
not a relational database table. In a well structured data table, you will
never have a column name that is actually data. In your case, you should
have fields ([Member Number], TestMonth, and Score) and the data should look
like:

Member Number TestMonth Score
016832301 200711 50
016832301 200712 69

I would strongly recommend that you restructure this table in this format.
You can do so with a normalization query that looks something like the
following. You can create the first segment of this in the Query grid, but
you will have to switch to SQL view to add each of the successive TestMonths
data.

SELECT [Member Number], "200711" as TestMonth, [200711] as Score
FROM [Bacto 12 month]
UNION ALL
SELECT [Member Number], "200712" as TestMonth, [200712] as Score
FROM [Bacto 12 month]
UNION ALL
...
SELECT [Member Number], "200811" as TestMonth, [200811] as Score
FROM [Bacto 12 month]

Save this query as qry_TestScores

The T and B I reference in my query are aliases. They are short
abbreviations for the tables that make it easier to read a query, and
significantly shorter to type the SQL string. You don't have to use those if
you don't want to. Using the above query, as your normalizing query, the
query to determine the deductions for each member/month would look like:

SELECT qry_TestScores.Member,
qry_TestScores.TestMonth,
qry_TestScores.Score,
tbl_Bands.Deduction
FROM qry_TestScores, tbl_Bands
WHERE qry_TestScores.Score >= tbl_Bands.Lower
AND qry_TestScores.Score <= tbl_Bands.Upper

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Hi thank you so much for getting back to me on this one. Just a couple of
questions. Do i need a primary key in the Bands table.
I have created the table like you said and the other table that i have is
called Bacto 12 month. The fields are named as followed:
Member Number 200711 200712 200801 200802 200803 (Field headings)
016832301 50 69 53 50 53
(Data)

I am struggling with the wording of the querry that you wrote and don't
understand what the T or B stands for. Could you help me with the names for
tables and fields that are above.

Again thank you for your help.

Emma

 

[Dale Fye]

Copy the SQL from you query and post it here. Chances are you left out a "
or [ or ] or actually inserted the "..." in the query.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Hi

I have done that. But when i tried to run the first querry it says there is
an error saying there is an incomplete querry clause. What does this mean?

Regards

Emma

Emma,

To start with, the structure of your other table looks like a spreadsheet,
not a relational database table. In a well structured data table, you will
never have a column name that is actually data. In your case, you should
have fields ([Member Number], TestMonth, and Score) and the data should look
like:

Member Number TestMonth Score
016832301 200711 50
016832301 200712 69

I would strongly recommend that you restructure this table in this format.
You can do so with a normalization query that looks something like the
following. You can create the first segment of this in the Query grid, but
you will have to switch to SQL view to add each of the successive TestMonths
data.

SELECT [Member Number], "200711" as TestMonth, [200711] as Score
FROM [Bacto 12 month]
UNION ALL
SELECT [Member Number], "200712" as TestMonth, [200712] as Score
FROM [Bacto 12 month]
UNION ALL
...
SELECT [Member Number], "200811" as TestMonth, [200811] as Score
FROM [Bacto 12 month]

Save this query as qry_TestScores

The T and B I reference in my query are aliases. They are short
abbreviations for the tables that make it easier to read a query, and
significantly shorter to type the SQL string. You don't have to use those if
you don't want to. Using the above query, as your normalizing query, the
query to determine the deductions for each member/month would look like:

SELECT qry_TestScores.Member,
qry_TestScores.TestMonth,
qry_TestScores.Score,
tbl_Bands.Deduction
FROM qry_TestScores, tbl_Bands
WHERE qry_TestScores.Score >= tbl_Bands.Lower
AND qry_TestScores.Score <= tbl_Bands.Upper

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Hi thank you so much for getting back to me on this one. Just a couple of
questions. Do i need a primary key in the Bands table.
I have created the table like you said and the other table that i have is
called Bacto 12 month. The fields are named as followed:
Member Number 200711 200712 200801 200802 200803 (Field headings)
016832301 50 69 53 50 53
(Data)

I am struggling with the wording of the querry that you wrote and don't
understand what the T or B stands for. Could you help me with the names for
tables and fields that are above.

Again thank you for your help.

Emma


:

Emma,

Create a banding table (tbl_Bands) with fields (Lower, Upper, and
Deduction). Then fill it in similar to:

Lower Upper Deduction
0 50 0
51 60 .3
61 65 .5
...

Then, create a query that looks something like:

SELECT T.Member, T.TestDate, T.Score, B.Deduction
FROM tbl_Test_Results as T, tbl_Bands as B
WHERE T.Score >= B.Lower
AND T.Score <= B.Upper

From this query, you can then create a Crosstab query that shows the
TestDates (year and month) across the top and the deduction as the cross tab
values.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

Hi

Please can somebody help me with a querry for this. I have a table which
shows each members test results for each month. On a report that i am
building i need to show the price that the member would get for that
particular test result each month. For example If the result is between 0 &
50 they will receive 0 deductions. If it is between 51 & 60 they will receive
-0.3 pence. There are 23 different bandings for this result.
On my report i need to show it like this below:
200711 200712 200801 200802 200803 200804 200805 200806 200807
Price Price Price Price Price Price Price
Price Price

The actual test results are shown at the top of the report. Each member has
there own report to show the results and prices.

I think that i need a querry to do this but i don't know where to start. If
i was in excel i would use a lookup querry that looks up the bandings table
and looks for the value.

Please could somebosy help me.

Thanks

Emma

 

[Emma Chapman 123]

SELECT [Member Number], "200711" as TestMonth, [200711] as Score
FROM [Bacto 12 month]
UNION ALL
SELECT [Member Number], "200712" as TestMonth, [200712] as Score
FROM [Bacto 12 month]
UNION ALL
SELECT [Member Number], "200801" as TestMonth, [200801] as Score
FROM [Bacto 12 month]
UNION ALL
SELECT [Member Number], "200802" as TestMonth, [200802] as Score
FROM [Bacto 12 month]
UNION ALL
SELECT [Member Number], "200803" as TestMonth, [200803] as Score
FROM [Bacto 12 month]
UNION ALL
SELECT [Member Number], "200804" as TestMonth, [200804] as Score
FROM [Bacto 12 month]
UNION ALL
SELECT [Member Number], "200805" as TestMonth, [200805] as Score
FROM [Bacto 12 month]
UNION ALL
SELECT [Member Number], "200806" as TestMonth, [200806] as Score
FROM [Bacto 12 month]
UNION ALL
SELECT [Member Number], "200807" as TestMonth, [200807] as Score
FROM [Bacto 12 month]
UNION ALL
SELECT [Member Number], "200808" as TestMonth, [200808] as Score
FROM [Bacto 12 month]
UNION ALL
SELECT [Member Number], "200809" as TestMonth, [200809] as Score
FROM [Bacto 12 month]
UNION ALL
SELECT [Member Number], "200810" as TestMonth, [200810] as Score
FROM [Bacto 12 month]
UNION ALL

Copy the SQL from you query and post it here. Chances are you left out a "
or [ or ] or actually inserted the "..." in the query.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Hi

I have done that. But when i tried to run the first querry it says there is
an error saying there is an incomplete querry clause. What does this mean?

Regards

Emma

Emma,

To start with, the structure of your other table looks like a spreadsheet,
not a relational database table. In a well structured data table, you will
never have a column name that is actually data. In your case, you should
have fields ([Member Number], TestMonth, and Score) and the data should look
like:

Member Number TestMonth Score
016832301 200711 50
016832301 200712 69

I would strongly recommend that you restructure this table in this format.
You can do so with a normalization query that looks something like the
following. You can create the first segment of this in the Query grid, but
you will have to switch to SQL view to add each of the successive TestMonths
data.

SELECT [Member Number], "200711" as TestMonth, [200711] as Score
FROM [Bacto 12 month]
UNION ALL
SELECT [Member Number], "200712" as TestMonth, [200712] as Score
FROM [Bacto 12 month]
UNION ALL
...
SELECT [Member Number], "200811" as TestMonth, [200811] as Score
FROM [Bacto 12 month]

Save this query as qry_TestScores

The T and B I reference in my query are aliases. They are short
abbreviations for the tables that make it easier to read a query, and
significantly shorter to type the SQL string. You don't have to use those if
you don't want to. Using the above query, as your normalizing query, the
query to determine the deductions for each member/month would look like:

SELECT qry_TestScores.Member,
qry_TestScores.TestMonth,
qry_TestScores.Score,
tbl_Bands.Deduction
FROM qry_TestScores, tbl_Bands
WHERE qry_TestScores.Score >= tbl_Bands.Lower
AND qry_TestScores.Score <= tbl_Bands.Upper

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

Hi thank you so much for getting back to me on this one. Just a couple of
questions. Do i need a primary key in the Bands table.
I have created the table like you said and the other table that i have is
called Bacto 12 month. The fields are named as followed:
Member Number 200711 200712 200801 200802 200803 (Field headings)
016832301 50 69 53 50 53
(Data)

I am struggling with the wording of the querry that you wrote and don't
understand what the T or B stands for. Could you help me with the names for
tables and fields that are above.

Again thank you for your help.

Emma


:

Emma,

Create a banding table (tbl_Bands) with fields (Lower, Upper, and
Deduction). Then fill it in similar to:

Lower Upper Deduction
0 50 0
51 60 .3
61 65 .5
...

Then, create a query that looks something like:

SELECT T.Member, T.TestDate, T.Score, B.Deduction
FROM tbl_Test_Results as T, tbl_Bands as B
WHERE T.Score >= B.Lower
AND T.Score <= B.Upper

From this query, you can then create a Crosstab query that shows the
TestDates (year and month) across the top and the deduction as the cross tab
values.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

Hi

Please can somebody help me with a querry for this. I have a table which
shows each members test results for each month. On a report that i am
building i need to show the price that the member would get for that
particular test result each month. For example If the result is between 0 &
50 they will receive 0 deductions. If it is between 51 & 60 they will receive
-0.3 pence. There are 23 different bandings for this result.
On my report i need to show it like this below:
200711 200712 200801 200802 200803 200804 200805 200806 200807
Price Price Price Price Price Price Price
Price Price

The actual test results are shown at the top of the report. Each member has
there own report to show the results and prices.

I think that i need a querry to do this but i don't know where to start. If
i was in excel i would use a lookup querry that looks up the bandings table
and looks for the value.

Please could somebosy help me.

Thanks

Emma

 

[John Spencer]

Sorry, to jump in.

DROP the last UNION ALL. The UNION (or UNION ALL) operator only goes
BETWEEN two queries.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Dale Fye]

John,

Your expertise is always welcome here!

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Sorry, to jump in.

DROP the last UNION ALL. The UNION (or UNION ALL) operator only goes
BETWEEN two queries.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

SELECT [Member Number], "200711" as TestMonth, [200711] as Score