List fields from one table & count another?

[TheWedgie]

I have taken over a database for a mobile medical centre, and need to add a report summarising each event they attend.

- Each event has its own table (easy enough to set the recordsource on loading the report).
- Each patient record in that table has a "PrimaryCondition" and "SecondaryCondition".
- PrimaryCondition and SecondaryCondition are each taken from another two tables called the same, where each PrimaryCondition has a few SecondaryConditions.
- The SecondaryCondition table has two fields: "PrimaryCondition" and "SecondaryCondition", where PrimaryCondition refers back to the PrimaryCondition table and it's data.

What I need to do is list a count of all the SecondaryCondition's (Grouped by PrimaryCondition) for an event - however as there is the possibility that conditions will be added/removed in the future, I can't just manually type labels and do a heap of DCount's.

Any ideas as to how I can dynamically list all of the Condition's and a count?

I assume it would have to be done in a Query, which I'll be playing with in the next few days when I get time, but figured I'd ask here in case someone has done something similar.

Cheers,
Nick

 

[PNGBill]

If you believe DCount won't work and a query will, then use both.
DLookup can get the result of a query.

 

[Galaxiom]

I would highly recommend you begin by restructuring the data because this database will become a increasingly clumsy as you try to extract summary information about the attendence of multiple events.

The events records should be in one table with an EventID field as a foreign key to indicate the event. The forms' recordsource queries will select the event based on this field.

This will also allow you to produce the report from a single aggregate query grouped by event. This will be much faster than using DCounts on multiple tables.

Fields should not be named the same a table. It complicates expressions on forms and I have seen Access choke on this.

 

[TheWedgie]

If you believe DCount won't work and a query will, then use both.
DLookup can get the result of a query.

Yeah - I think where I'm getting stuck is how to get it to dynamically list the fields from the SecondaryCondition table - then do a count based on what those fields are (ie if we delete or add one of the conditions, then the report list changes).

I would highly recommend you begin by restructuring the data because this database will become a increasingly clumsy as you try to extract summary information about the attendence of multiple events.

The events records should be in one table with an EventID field as a foreign key to indicate the event. The forms' recordsource queries will select the event based on this field.

This will also allow you to produce the report from a single aggregate query grouped by event. This will be much faster than using DCounts on multiple tables.

Fields should not be named the same a table. It complicates expressions on forms and I have seen Access choke on this.

As much as I'd love to restructure it, it'll be too much work to do at the moment. Essentially all I'm adding is this report and a couple of GUI changes, then will be rewriting it from scratch in C#.

 

[Galaxiom]

What I need to do is list a count of all the SecondaryCondition's (Grouped by PrimaryCondition) for an event - however as there is the possibility that conditions will be added/removed in the future.

Isn't this just a group by on the two condition fields with Count[SecondaryCondition] as another field? Adding more Condition types will just increase the number of records.

 

[TheWedgie]

Isn't this just a group by on the two condition fields with Count[SecondaryCondition] as another field? Adding more Condition types will just increase the number of records.

Essentially yes - but the messing around I've done it seems to print a row for each instance of each SecondaryCondition.

ie i get "Headache" appear three times if there's three Headache cases - rather than just once with a total next to it.

(I've got the DCount working fine, it's just that the report repeats each SecondaryCondition)