Need help to get Access to generate a report from multiple tables with cross referenced information

[rj068004]

I have a pretty large dataset in multiple tables that I'm having difficulty figuring out how to get the reports generated that I need. I'm wide open to suggestions at this point because although I have attempted some Reports and Queries, I can't figure out how to get what I'm needing from the data. I really only have 2 tables with the necessary information: "Current FY Practice Detail" and "Reviews - Practice" Both tables have "Practice Code" and "County" records that link. The information that I want to generate in a report is:



1. Count of Practices sorted by County (I think that I can muster this one, but am always open to best way possible)
2. Date of last review of Practice in County (Here's where I'm having problems) The basic criteria would be: If a practice that has been applied in a County (in table Current FY Practice Detail) has also had a review for the same practice in the same County (in table Reviews - Practice), then what was the most recent date that the review was completed on for that specific County and Practice (also in table Reviews - Practice).

An example of the dataset would be:

Reviews - Practice
ID	Practice	PracticeCounty	ReviewDate
1	512	Gx	11/7/2017
2	528	Gx	11/7/2016
3	342	Tx	11/14/2015
4	314	Tx	11/14/2014
5	329	Kx	10/24/2017
6	340	Kx	10/24/2017
7	512	Kx	10/24/2017
8	528	Kx	10/24/2017

Current FY Practice Detail
ID	Practice	County
1	512	Gx
2	561	Gx
3	512	Kx
4	528	Tx
5	528	Kx
6	342	Gx
7	600	Kx
8	528	Kx

For this example, I would like access to give me a report that says County Kx installed practice 512 1 time and it was last reviewed 10/24/2017. County Gx installed practice 561 1 time and has no date of last review.

Any help is appreciated!!!

[Micron]

What do you expect to see (how would it be grouped/shown) if a practice and county count was more than 1? Your examples only cover the situation where the count is 1. And if there is no review, you want to see the county/practice data in the report regardless?

[rj068004]

Below would be what I hope to see the report look like. I probably can't do this justice just typing, but will try:

County
Kx

Practice Amount Last Reviewed
512 1 10/24/2017
528 2 10/24/2017
600 1 "No Record"

Yes, I would like to see the record of practices no matter if there is a review record for that County and Practice or not. If there is no review record, it doesn't matter if it displays "No Record" or a null value or what, just as long as the practice and practice count is displayed.

[Micron]

If in query design view you place the small table on the left and the other on the right and join them on Practice fields, that will give only practices with reviews (assuming the data model is that if there is a practice value in both tables, there will always be a review date). If not, you will get the fields from the right table as long as the practice value appears in both, but you will only get the information that is contained in the reviews table records, whatever that may be regardless if there is no date for a given record.

If you change the join between tables (double click on it) so that the arrow points to the right table, you should get all records from the left (detail) and any from the right that match. If that works for you, build a report from that. If you group by practice (in the report) you ought to at least get a detail group on a county even if there are no review dates. This is basic query design, such that if it's news to you, consider finding some on line tutorials on how to build db components (tables/queries etc) in Access.

BTW- usually you don't repeat the foreign key data in a related table when you are using ID fields (yours appears to be an autonumber field). What really ought to be in the reviews table is the PK id number from the parent related table (e.g. 512 Kx is 3). If you correct an error and change 512 to 513 and have not set up table relationships with Cascade Update option, all your related 512 review records become orphans. In your case I suspect you also need a composite index (or composite primary field) so that there cannot be 2 records with 512 Kx.