very stuck

[capdownlondon]

I have a table that has the follwoing fields:

-student name
-pesent percentage
-compare value 1 (a number)
-compare value 2(a number)

now i want a query to display the following fields as an output

-Student name
-Compare value
-present percentage 1
-present percentage 2

so where "compare values" 1 and 2 are equal it will display 1 record in the query that will show both the values. in respective fields.

 

[capdownlondon]

been driving me mad btw so it would be very much appreciated for he help.
the reason i need the ifo displayed in this manner is for graphing purposes.

thanks

adam

 

[ajetrumpet]

In order to help, you are going to have to explain what significance the "compare" field values have in the database and how they relate (if at all) to the "present percentage".

ex - How do you get 2 percentage values from 1??

what are the comparisons of these numbers??​

how do they relate to each other??​

any mathematical operations going to be performed here??​

 

[capdownlondon]

basically, there are more fields in the table.

the percentags are manually input for each student for a given date (when i say date i mean a given name for a term for example, so not an ACTUAL date).

what i want to be able to do is have a list of all the dates for a particular child on a form, and select the order i want to compare them. which is pretty easy based on just the table.

then i want to be able to generae a graph based on the selection.

so say dates (and the percentages on that date) x, z and y, hav been assigned to be 1, 2 , and 3 respectively. and dates c, t, and b have been assigned to 1, 2 , and 3 on the other side of the form. then i want to be able to make a line graph that compares percentages with 1,2,3 along x axis and percentage up the y axis with 2 lines showing the respective termdate percentages.

kinda complicated, but it basically requires me to have a query that outputs the data in that way, and i've left out the childs name querying bit etc, just coz i can do that no problem. i just need to know how to get "compare1" and "compare2" into putting the percentage from those records on percentage 1 and percentage 2 in a query.

complicated i know, but i can't think of anotherway of doing it.

 

[capdownlondon]

ok problem solved:
here's the code i finaly used:

TRANSFORM Sum([AttendancePercentTable Query].Present) AS SumOfPresent
SELECT [AttendancePercentTable Query].CompareField1
FROM [AttendancePercentTable Query]
WHERE ((([AttendancePercentTable Query].CompareField1) Is Not Null) AND (([AttendancePercentTable Query].StudentName)=10))
GROUP BY [AttendancePercentTable Query].CompareField1
PIVOT [AttendancePercentTable Query].CompareField2;