Count Query

[Lkwdmntr]

Hello, I seem to be a little stuck on how to go about doing this. I have a report with four subforms, created from two tables, with separate headers.

My hope is to:

- count all the records that will show up on the report.
- make an if statement that will make a page break visible if the record count
is greater than 42.

The records come from two tables, "Weekly_Challenges" and "Weekly_StartTime_Challenges". Since the reports are daily, I am focusing on a day at a time. So the records being counted are any records that have a value in the Weekly_StartTime_Challenges "Monday" field (Is Not Null), and the Weekly_Challenges "Monday" field that is equal to "X". Also, the UserID is equal to 4 and the WeekNumber is equal to 1 in both tables. I know, can I get anymore confusing. Sorry. This is the SQL that my query produced, but it seems to be counting all the ID's, all the WeekNumbers, all the values in both tables each separately and adding them together. I am getting this huge number "483", when I should be getting something like "46". Please any help would be appreciated.

SELECT Count(*) AS WCMonTot
FROM Weekly_Challenges, Weekly_StartTime_Challenges
WHERE ((([Weekly_Challenges]![UserID])=4) AND
(([Weekly_Challenges]![WeekNumber])=1) AND (([Weekly_Challenges]!
[Monday])="X") AND ((Weekly_StartTime_Challenges.UserID)=4) AND
((Weekly_StartTime_Challenges.WeekNumber)=1) AND
((Weekly_StartTime_Challenges.Monday) Is Not Null));

 

[theDBguy]

Hi. Perhaps you need to group the fields/columns first to reduce the number of records to count. For example, you could create a grouped query and then use a Count() query on that query. Also, another possible approach is put a "row counter" on your report and base your code on that to show the page break.

 

[arnelgp]

you have two tables in your query which are not joined, so it is creating a cartesian product,meaning (table1 records x table2 records).

you should split this into two queries.
then make another query comparing the results of the two query and outputting which results is the greatest.

 

[theDBguy]

you have two tables in your query which are not joined, so it is creating a cartesian product,meaning (table1 records x table2 records).

you should split this into two queries.
then make another query comparing the results of the two query and outputting which results is the greatest.

Ah, good eye! I didn't even notice that.

 

[plog]

You've got bigger problems than this query:

...
[Weekly_Challenges]! 
[Monday])="X")... AND 
((Weekly_StartTime_Challenges.Monday)...

"Monday" should not be in a field name. Doing so means you've improperly structured your tables. When you do that you are storing values in the field name which should be in a value in the record (or more likely calculated in a query).

I fear that if you continue with an improper structure, even if you do over come this issue, even more issues will crop up in the future.

 

[Lkwdmntr]

What is your suggestion? I have had no problems so far. Would a simple change like mon, tue, or wed work? of even mDay,tDay?

 

[plog]

It really depends on how you are using it. do 2 things:

1. Give us a simple (no database jargon) explanation of what it is your organization does. Pretend its career day at an elementary school and you are telling the kids what you actually do. Then explain (with minimal database jargon) what it is your database is helping you do.

2. Post a screenshot of your Relationship Tool showing all the tables and fields in your database.

 

[Lkwdmntr]

So, I am working with this start-up company that deals with reversal therapy. I am using Access to make forms to gather data and then create reports for the clients to keep track of their goals. Attached is the relationships between my tables, but just want everyone to know, this is my first time doing anything like this. I never worked with access before this, I know a little SQL and have never worked with VBA. That being said, I think I have done very well with this project.

I have two problems that I am trying to get through.

- The one I posted earlier about counting all the records in the two tables that have the proper values for a specific user id and week number.

- My boss just asked me to make a report that shows the week at a glance. I would need to gather all the Weekly_Startime_Challenges for a specific user and week (only the actions that have a value). Also the Weekly_Challenges for a specific user and week, but only the values that are marked with an "X".

I know queries are going to be the way here, but can't seem to get this narrowed down properly with all these conditions.

 

[Lkwdmntr]

Opps, forgot the attachment

 

[arnelgp]

both Weekly_Challenges and Weekly_StartTime_Challenges tables have same structure.
do they have same data as well?

1. query:

SELECT DISTINCT M.WEEKNUMBER, M.UserID, (SELECT Count(*)
FROM Weekly_Challenges AS T1
WHERE (((T1.UserID)=M.[UserID]) AND ((T1.WEEKNUMBER)=M.[WeekNumber]) AND (Not (T1.MONDAY) Is Null))) AS Monday
FROM Weekly_Challenges AS M
WHERE (((M.WEEKNUMBER)=1) AND ((M.UserID)=4));

2. query

SELECT DISTINCT M.WEEKNUMBER, M.[UserID], (SELECT Count(*)
FROM Weekly_Challenges AS T1
WHERE (((T1.UserID)=M.[UserID]) AND ((T1.WEEKNUMBER)=M.[WeekNumber]) AND (Not (T1.MONDAY) Is Null))) AS Monday, (SELECT Count(*)
FROM Weekly_Challenges AS T1
WHERE (((T1.UserID)=M.[UserID]) AND ((T1.WEEKNUMBER)=M.[WeekNumber]) AND (Not (T1.Tuesday) Is Null))) AS Tuesday, (SELECT Count(*)
FROM Weekly_Challenges AS T1
WHERE (((T1.UserID)=M.[UserID]) AND ((T1.WEEKNUMBER)=M.[WeekNumber]) AND (Not (T1.Wednesday) Is Null))) AS Wednesday, (SELECT Count(*)
FROM Weekly_Challenges AS T1
WHERE (((T1.UserID)=M.[UserID]) AND ((T1.WEEKNUMBER)=M.[WeekNumber]) AND (Not (T1.Thursday) Is Null))) AS Thursday, (SELECT Count(*)
FROM Weekly_Challenges AS T1
WHERE (((T1.UserID)=M.[UserID]) AND ((T1.WEEKNUMBER)=M.[WeekNumber]) AND (Not (T1.Friday) Is Null))) AS Friday, (SELECT Count(*)
FROM Weekly_Challenges AS T1
WHERE (((T1.UserID)=M.[UserID]) AND ((T1.WEEKNUMBER)=M.[WeekNumber]) AND (Not (T1.Saturday) Is Null))) AS Saturday, (SELECT Count(*)
FROM Weekly_Challenges AS T1
WHERE (((T1.UserID)=M.[UserID]) AND ((T1.WEEKNUMBER)=M.[WeekNumber]) AND (Not (T1.Sunday) Is Null))) AS Sunday
FROM Weekly_Challenges AS M
WHERE (((M.UserID)=4) AND ((M.WeekNumber)=1));

 

[Lkwdmntr]

Thanks for the assist, I will try these later on in the day. The only difference in the two tables data is that Weekly_Starttime_Challenges, Mon-Sun values are a timed value, like 7:00 AM. Weekly_Challenges is some of the same data but where there is a timed value for the specific action, there is a "T" in the Mon-Sun fields. Otherwise, the fields will have either an "X" or nothing. I only want the fields with the "X"'s.

 

[arnelgp]

I see, I thought if the Day (Monday, etc.) is not blank. so you just replace "Is Null" in the query with = "X" (equals to X).

 

[Lkwdmntr]

Thanks for the help, the first query worked well, but I think you misunderstood my second issue. I am creating a report that shows "A week at a Glance" for a certain UserID and a certain week. This report will have the "Standard Action" or "StartTime Action" for each day of the week. The biggest problem I foresee here is, if there is a Start Time Action, it will be a time, like 7:00 AM. If it is a Standard Action, there will be an "X" and if there is no value in either for a certain day, it will be left blank. The problem lies in the fact that the value can be either in the time format or a string. Not sure how to handle this, and the query you provided was for counting.
Action Mon Tue Wed Thur Fri Sat Sun
Rise Time 7:00 AM 7:00 AM X 7:00AM X

This is an example of what one line of the report might look like. Hope you can help with this, I have been racking my brain for a while. This project is part of co-op for school and I have no mentors to ask for help. Thank god for this site and people like you.

 

[arnelgp]

from which table does your example comes from?

 

[arnelgp]

SELECT T1.userID, T1.FullName, T1.WeekNumber, 
Switch(IsNull(T1.Monday) And IsNull(T2.Monday), Null, IsNull(T1.Monday), Format(T2.Monday, "hh\:nn AM/PM"), IsNull(T2.Monday), T1.Monday, True, Null) As Monday, 
Switch(IsNull(T1.Tuesday) And IsNull(T2.Tuesday), Null, IsNull(T1.Tuesday), Format(T2.Tuesday, "hh\:nn AM/PM"), IsNull(T2.Tuesday), T1.Tuesday, True, Null) As Tuesday,
Switch(IsNull(T1.Wednesday) And IsNull(T2.Wednesday), Null, IsNull(T1.Wednesday), Format(T2.Wednesday, "hh\:nn AM/PM"), IsNull(T2.Wednesday), T1.Wednesday, True, Null) As Wednesday, 
Switch(IsNull(T1.Thursday) And IsNull(T2.Thursday), Null, IsNull(T1.Thursday), Format(T2.Thursday, "hh\:nn AM/PM"), IsNull(T2.Thursday), T1.Thursday, True, Null) As Thursday, 
Switch(IsNull(T1.Friday) And IsNull(T2.Friday), Null, IsNull(T1.Friday), Format(T2.Friday, "hh\:nn AM/PM"), IsNull(T2.Friday), T1.Friday, True, Null) As Friday, 
Switch(IsNull(T1.Saturday) And IsNull(T2.Saturday), Null, IsNull(T1.Saturday), Format(T2.Saturday, "hh\:nn AM/PM"), IsNull(T2.Saturday), T1.Saturday, True, Null) As Saturday, 
Switch(IsNull(T1.Sunday) And IsNull(T2.Sunday), Null, IsNull(T1.Sunday), Format(T2.Sunday, "hh\:nn AM/PM"), IsNull(T2.Sunday), T1.Sunday, True, Null) As Sunday
From Weekly_Challenges As T1 Inner Join Weekly_StartTime_Challenges As T2 
On T1.UserID = T2.UserID And T1.WeekNumber = T2.WeekNumber
Where ((T1.UserID = 4)  And (T1.WeekNumber = 1))

 

[Lkwdmntr]

Wow, there is a lot going on in that statement. Maybe I'm not explaining what needs to happen clearly. When I run your statement I get 828 records with the UserID, FullName, and WeekNumber. All the days of the week are empty. All the values should be showing in the given day. Again I need the Time values for each day from the Weekly_StartTime_Challenges table and the Actions that have an "X" in the day as a value. I should end up with about 40 records in total. I will be using the Name, UserID, and Weeknumber in the heading of the report and will be referencing temporary variables for the values of those fields. Instead of using UserID=4 and WeekNumber=1, I will be using TmpUserID, TmpFullName, TmpWeekNumber. The actual rows of the report will be like the one I did in the prior message.

Action Monday Tuesday Wednesday Thursday Friday Saturday Sunday
RiseTime 7:00 AM 7:00 AM X X 7:00 AM X

So in this example, their are values for Monday, Tuesday and Saturday in the Weekly_StartTime_Challenges table and "X" values for Wed, Thurs, and Sunday and Friday has no value in either table, so is left blank. Every row could be different and the fields can switch back and forth from the date format or string format.

Does that Help or did I just make it more confusing?

 

[arnelgp]

I am only working on the relationship chart that you gave me.
I don't know which fields the Time or the X are stored.
I only assume that they are stored in Monday, Tuesday field.
but as to which table, I just guessed it.
if you can post the table or tables, it will help us both.

 

[Lkwdmntr]

I really don't know how to just send the two tables. I feel a little weird sending you the whole project, but I am trusting that you be discrete and not do anything unethical with it. The file size is almost 5 MB any suggestions on how to get it to you?