Can't figure out how to show unique programs in query (1 Viewer)

kvnd

Registered User.
Local time
Today, 05:15
Joined
Oct 9, 2018
Messages
16
What my dataset looks like:

It's a bunch of fire drills. A field called ID is the key, and associated with each is the Program which performed the drill, the day, etc. Every program runs multiple drills throughout the year

I want the report to show the name of each program and a calculation of the number of drills they have performed.

The problem is the continuous report seems to show each program name multiple times according to the number of drills they have run. For example if Program 1 ran 4 drills, it will repetitively list Program 1 four times before listing the next program. I don't want this, I want each program listed only once.

Here's my SQL for the report's record source. It has a lot of info on other fields contained within it, which I intend to list on the continuous report. SELECT DISTINCT at the start instead of just SELECT doesn't solve my issue.

SELECT Table.ProgramID, Table.ID, Table.[Drill Type], Table.Day, Table.[Drill Format], Table.Shift, Table.Unannounced, Table.Success, ProgramRequirements.FDRequirement, ProgramListMaster.Program, ProgramListMaster.Center, ProgramListMaster.[Division Name]
FROM (
INNER JOIN ProgramRequirements ON Table.ProgramID = ProgramRequirements.ProgramID) INNER JOIN ProgramListMaster ON ProgramRequirements.ProgramID = ProgramListMaster.ProgramID;
 

plog

Banishment Pending
Local time
Today, 04:15
Joined
May 11, 2011
Messages
11,611
Can you demonstrate what you want with data? Your explanation seems contradictory:

For example if Program 1 ran 4 drills, it will repetitively list Program 1 four times before listing the next program. I don't want this, I want each program listed only once.
...
It has a lot of info on other fields contained within it, which I intend to list on the continuous report

I mean if a program has 4 different Drill Types, you say you want to show those 4 drill types--how do you propose to do that if you show program only once?
So do you simply want Program and count, or do you want all the other stuff?

To get Program/Count its a simple aggregate query:

Code:
SELECT ProgramID, COUNT(ProgramID) AS ProgramCount
FROM Table

If that's not what you want, please demonstrate your issue with data.
 

kvnd

Registered User.
Local time
Today, 05:15
Joined
Oct 9, 2018
Messages
16
Can you demonstrate what you want with data? Your explanation seems contradictory:



I mean if a program has 4 different Drill Types, you say you want to show those 4 drill types--how do you propose to do that if you show program only once?
So do you simply want Program and count, or do you want all the other stuff?

To get Program/Count its a simple aggregate query:

Code:
SELECT ProgramID, COUNT(ProgramID) AS ProgramCount
FROM Table

If that's not what you want, please demonstrate your issue with data.


So do you simply want Program and count, or do you want all the other stuff?

Oh, sorry, I should have elaborated on the calculation. Yes, I want each program listed, but the other textbox on the form is ultimately going to be a complex calculation, I just (misleadingly) used a simple count as an example for the post

In reality:

A program may have to run a drill quarterly (within a specific 4 month time frame), and run a few different types of drills in a specific format.


I am also going to have another textbox on the report which generates text for the drills that are required but have not been run yet. This calculation will have to interact with those fields in IF statements and such even if they aren't featured on the report as a text or combo box or anything like that and only the programs and the calculations will be visible to the user on the report.

This means all those fields are still necessary in the record source, no?

To summarize: The report is going to be a list of all the programs and a text box showing % compliance with the drill requirements and another textbox telling them what they still need to do. Coding that requires using all the fields in a calculation.
 
Last edited:

plog

Banishment Pending
Local time
Today, 04:15
Joined
May 11, 2011
Messages
11,611
Lost me. Please demonstrate your issue with data, perhaps a mockup of the form/report.

Include 2 sets of data:

A. Starting data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show me what data you hope to end up with based on the starting data in A.

Again, data. Show me exactly what you want with data.
 

kvnd

Registered User.
Local time
Today, 05:15
Joined
Oct 9, 2018
Messages
16
Ok, here is some sample data attached, and an image of how I want the report to look.

As you can see, only the ProgramID is listed (uniquely), and a calculation based on that program ID. What the calculation actually is is irrelevant I guess, just know the calculation is going to be a count based on all the other fields in the dataset so all those fields have to be able to be used in the calculation
 

Attachments

  • sampledata.xlsx
    9.1 KB · Views: 102
  • accessreport.jpg
    accessreport.jpg
    23.3 KB · Views: 103

plog

Banishment Pending
Local time
Today, 04:15
Joined
May 11, 2011
Messages
11,611
Ugh. Data. Data. I need data. Specific data. Data in the results that tie back to the data in the starting data you provide.

With that said, here is a query that will produce exactly the results you demonstrated with the starting data you provided:

SELECT ProgramID, "Calc" AS Calc
FROM Table
GROUP BY ProgramID, "Calc"

Please reread my prior post and provide exactly what I want for B.
 

Users who are viewing this thread

Top Bottom