Help with a database report?

[jthorneuk]

Hi,

I hope somebody can help and im sure what I am trying to do is very
easy. I am working on a staff database and I need help displaying
data on a report or a form in a set way to mirror a hand written paper
report the client currently has.

I have 3 tables

Staff
Qualifications
SQualifications

Staff - This is updated via a form and holds staff information. The
fields are "staffid, fname, sname, dob"

Qualifications - This is updated via a form and holds Qualifications
the staff either need or have. The fields are "Qualification,
frequency"

SQualifications - Enteries to this table are created via a form and
they link the staffid to a Qualification. The fields in this table are
"staffid, qualifaction, dtaken, dexpire"


This is all working fine and dandy. However the client needs the data
to be displayed in a set way. This is the part i cant work out.


What they want is.....

Staff members listed down the left hand side (Y Axis) and
Qualifications listed accross the top (X Axis) they then want either a
green or a red box showen where the Y Axis and X Axis cross for each
person. The box needs to be Green if the dexpire is in the future and
Red if its in the past.


I cant work out how to display the data in such a way. I know I could
create a form with the names and Qualifications pre set and then get
the data to display in the middle using condition formatting. However
if another Qualifications or staff member is added I would need to
modify the form each time.


Any help would be fantastic!

 

[Douglas J. Steele]

You need to create a query that joins the three tables so that your
resultant recordset returns rows containing Staff name, Qualification name
and dexpire. Then, create a crosstab query on that query to return the data
in the format you describe. Create a report based on that crosstab query and
use conditional formatting as you describe.

 

[jthorneuk]

You need to create a query that joins the three tables so that your
resultant recordset returns rows containing Staff name, Qualification name
and dexpire. Then, create a crosstab query on that query to return the data
in the format you describe. Create a report based on that crosstab query and
use conditional formatting as you describe.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)















- Show quoted text -

Hi,

I have given this a try and it does indeed to what I wanted. However
there is one problem in that the reports although they show the data
correctly. If anything is added to the Qualifications tables (ie a new
qualification is added) The report only shows what fields there were
in the table at the time the report was created. Any further colums
need to be added to the report manually. This defeats the object of
this as I need the report to show the detail automaticly.

Is there anyway of making a report generate itself from the table each
time rather than just holding static colums from the last time it was
run?

 

[Douglas J. Steele]

You could take a look at the CreateReport and CreateReportControl functions,
but be aware that it can be a lot of work to do this.

It's also mandatory that your application be split into a front-end and
back-end, since you cannot create reports in a shared database.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I have given this a try and it does indeed to what I wanted. However
there is one problem in that the reports although they show the data
correctly. If anything is added to the Qualifications tables (ie a new
qualification is added) The report only shows what fields there were
in the table at the time the report was created. Any further colums
need to be added to the report manually. This defeats the object of
this as I need the report to show the detail automaticly.

Is there anyway of making a report generate itself from the table each
time rather than just holding static colums from the last time it was
run?

 

[Brynden]

Yes you do need to create a query that joins three tables.