How can I create a report that shows datavalues that are not used?

[IceKing]

Hi,

First off, let me appologies for the description of this thread/question. I'm sure it does not describ my question properly.

Here is the background to my question:
I schedule ice for a minor hockey associaition. I decided to use Access this year, instead of Excel for it's reporting capabilities. I have created a simple report that shows, by day, all the ice that I have, and what teams are on it.

Now I'd like to create a report that shows, by day, what teams are not on the ice, or can not use the ice, that day.

The next step would be to merge these 2 reports. But I'll cross that bridge another day.

Any help would be appreciated.

Thanks!

 

[IceKing]

Sorry. Forgot to add some useful information, and I can't seem to figure out how to edit posts in this forum.

All the pertinent data is in a single table. Which would be ScheduleDate, HomeTeam and VisitorTeam.

So, basically what I want is to list the date and every team that doesn't show up in HomeTeam and VisitorTeam for taht date.

Thanks again!

 

[dhookom]

Do you have a table of all unique teams? If not, you should create one. The first step I would take is to create a union query like:

SELECT ScheduleDate, HomeTeam as Team, "H" as HV
FROM [single table]
UNION ALL
SELECT ScheduleDate, VisitorTeam, "V"
FROM [single table]

You could then create a query for dates where records from your team table aren't in the schedule table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[IceKing]

Duane,

Thanks for the tip. I have a table of unique teams. I actually have 2. One for Home and one for Visitor. Probably not the best solution, but something required me to do it. (I just can't remember what at the moment.)

The table of unique teams also has division, level, and coach contact information in it.

Not sure how to create the query for dates from here.

 

[SkipVought]

Hi,

"...what teams are not on the ice, or can not use the ice, that day..."

I can understand when a team is not scheduled for ice on a particular day, but what determines that a team "can not use the ice, that day"?

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[IceKing]

Can not use the ice that day" refers to what we call "blackout dates". They are mainly for tournaments, but could be for many reasons.

In hind sight, I actually have blackout as a facility, so they show up in my regular schedules, and therefore are not needed in the "not on the ice" report.

 

[IceKing]

Ok...

I have my table of unique team names as suggested by dhookom. I have made a form based on the query to "translate" the Team # to the team name and have used it as a subform to list the teams on the ice based on ScheduleDate. However, I can't seem to get the opposite (teams not on the ice) to happen.

Any help here would be greatly appreciated.

Thanks!

 

[dhookom]

I didn't suggest to create a table of unique team names. If your teams were identified by a team code then there would be a table of the numbers.

Did you create the union query to get a list of all unique teams whether they were home or visitors?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[IceKing]

Duane,

Sorry for the misunderstanding. Yes, I created the union query to get a list of all unique teams whether they were home on visitor.

I then tried to figure out how to use that query to determine which teams didn't appear on a particular night. But I keep getting a result with no teams. I think it is due to not filtering properly for the given day. I am stumped on how to do this.

 

[dhookom]

You can use a subquery like:

SELECT *
FROM tblAllTeams
WHERE TeamID Not IN (SELECT TeamID FROM quniTeamsSchedule WHERE ScheduleDate = #10/1/2007#)

If you can't figure this out, come back with significant information such as your union query name and SQL, your schedule table structure and your table containing all teams.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[IceKing]

Duane,

I'm stumped. I tested out your query with some dummy data and the logic seems to work. But, when I apply it to my live database, it doesn't work.

Here is what I think is all the significant info:
Table: MasterIceSchedule (Contains all the ice time info and teams on the ice)
ScheduleDate > format = Short Date
HomeId > format = Long Integer
VisitorId > format = Long Integer

TeamQuery: (Extracts just the teams from our town)
SELECT HomeName, HomeId AS TeamId
FROM HomeTeams
WHERE HomeName Like "OurTown*";

TOIQuery: (This is from your first query above)
SELECT ScheduleDate, HomeId as TeamId, "H" as HV
FROM MasterIceSchedule
UNION ALL SELECT ScheduleDate, VisitorId, "V"
FROM MasterIceSchedule;

TNOIQuery: (This doesn't work, but I think it follows your logic.)
SELECT TeamId
FROM TeamQuery WHERE TeamId NOT IN (SELECT TeamId FROM TOIQuery WHERE ScheduleDate=#10/10/2007#);

Where am I going wrong?

 

[dhookom]

Do all of your priliminary queries return the expected results?

Do you understand the difference between "format" and "data type"? The format is used to display a value and has no effect on the stored value/data type. Does your ScheduleDate contain both the date and time?

Try this:

SELECT TeamId FROM TOIQuery WHERE ScheduleDate = #10/10/2007#;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[IceKing]

The preliminary queries return the expect results. The code above works and returns the 5 teams that are on the ice that day. I have 6 ice times that day. So there is a potential for up to 12 teams to be on the ice. It just happens that right now, I only have 5 scheduled in.

ScheduleDate only contains a date. I have StartTime and EndTime values that contain the time portion.

I do understand the difference between format and data type. But just to be clear, here are the data types:

ScheduleDate > data type = Date/Time
HomeId > format = Number
VisitorId > format = Number

 

[dhookom]

The issue may be that you have separate tables with teams. What happens if you have two home teams playing each other? If you aren't worried about the visitor teams, don't use the union query.

SELECT TeamId
FROM TeamQuery WHERE TeamId NOT IN (SELECT HomeId FROM MasterIceSchedule WHERE ScheduleDate=#10/10/2007#);

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[IceKing]

Unforetunately, I need to worry about the visitor team. We have house league teams that play within our association and against other associations.

I tried not using the union query (as above) and I get similar results. Of course, this time it is only 6 records that are returned in the primary query.

Hmmm...

 

[dhookom]

You have stated "it doesn't work" and "similar results" but we have no idea what you mean by these statements.

Are there any team ID values common to the home team table and the visitor team table?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[IceKing]

Duane,

Yes, there are 39 team ID values that are the same in the home team table and visitor team table. The tables are essentially copies of each other. As mentioned near the begining there was a reason I had to make 2 tables with virtually the same info. However, I am not using either of those tables in these querries. Everything is being derived from the MasterIceSchedule table.

What I mean by "similar results", is that when I tested the primary query, with or without the union query, the results are correct. With the union query, I receive 12 records, including the 5 TeamIDs representing the teams on the ice, and 7 null values. Running the query on just the Home team, I receive 6 recoeds, including the 3 TeamIDs representing the teams on the ice, and 3 null values.

What I mean by "it doesn't work", is that the results for the "Not IN" query results in a single null value only. There should be 34 resulting records.

 

[dhookom]

However, I am not using either of those tables in these querries

TeamQuery: (Extracts just the teams from our town)
SELECT HomeName, HomeId AS TeamId
FROM HomeTeams
WHERE HomeName Like "OurTown*";

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[IceKing]

Sorry. With the many different approaches I have been using to solve this problem I forgot which one I quoted. I also tried this with a query that extracts the home association TeamIds straight from MasterIceSchedule.

In both scenarios, the resultant record set it null.

 

[dhookom]

Tell me the results of this query such as the values and are they left or right aligned in the datasheet

SELECT TeamId
FROM TeamQuery;

Provide the same information for:

SELECT TeamId 
FROM TOIQuery 
WHERE ScheduleDate=#10/10/2007#;

Provide the same information for:

SELECT TeamId
FROM TeamQuery WHERE TeamId NOT IN (SELECT TeamId FROM TOIQuery WHERE ScheduleDate=#10/10/2007#);

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]