Creating a Report from Two Tables

[Lkwdmntr]

Mike60smart,
All the records aren't there. If you notice in the Weekly_Challenges table there are "T"'s and "X"'s or nothing. That is how I need the report to look, but the actual time that is in the Weekly_StartTime_Challenges Tabel. For instance:

Action Mon Tue Wed Thur Fri Sat Sun

HouseKeeping Routine X 2:00PM X 2:00PM X 2:00PM
Exercise Routine 3:00PM X 3:00PM X 3:00PM 3:00PM

Don't concern or worry about the Main Header. I will plug all of that information in. I hope this gives you a better idea of what is needed. Imagine everything in columns, it's hard to replicate what is needed in this textbox. Sunday has no timed value or X value, so it is blank.

 

[Lkwdmntr]

The tabs and yes/no fields are for the daily reports. I pull the data using the index numbers. In the daily reports, I have three subreports that separate the data, the index numbers identify what types of actions they are. I posted what the differences are a couple of posts back. I think the solution lies in separating the report into three sections as well.

 

[mike60smart]

Hi

Sorry but trying to combine fields from different tables into 1 record indicates bad table design.

If I understood your process maybe I could suggest a different structure.

What does T, X & Y represent in the Days Columns?

What are you trying to achieve with this database?

 

[Lkwdmntr]

The "T" means there is a Timed Value. The "Y" is used with two actions that are kind of being used as separators. I use the "Y" value in a condition to make those actions font bold. I don't know about bad table design or if what is being asked of me is just not possible. I told my boss that we would have problems with this part, but she doesn't seem to care. The time format and the string values are what is screwing everything up. That's why did the subreports.

 

[mike60smart]

Hi

I have asked numerous times for an explanation of your process.

Can you explain what it is you are trying to record on a daily basis?

 

[June7]

I have never used TempVars.

I also never use dynamic parameter queries. I prefer VBA to build filter criteria.

Use TempVars to apply filter to report when opening but bind textboxes to data in RecordSource.

Mike's queries sort of do what I suggested you need to do. Did you look at in 17?

Expanding on my query suggestion and comments, slightly different approach:

Query1: ActionUNION

SELECT UserID, WeekNumber, StandardAction AS Action FROM Weekly_Challenges
UNION SELECT UserID, WeekNumber, StartTimeAction FROM Weekly_StartTime_Challenges;

Query2:

SELECT ActionUNION.UserID, ActionUNION.WeekNumber, ActionUNION.Action,
IIf([Weekly_Challenges].[Monday]="X","X",[Weekly_StartTime_Challenges].[Monday]) AS Mon,
IIf([Weekly_Challenges].[Tuesday]="X","X",[Weekly_StartTime_Challenges].[Tuesday]) AS Tue,
IIf([Weekly_Challenges].[Wednesday]="X","X",[Weekly_StartTime_Challenges].[Wednesday]) AS Wed,
IIf([Weekly_Challenges].[Thursday]="X","X",[Weekly_StartTime_Challenges].[Thursday]) AS Thu,
IIf([Weekly_Challenges].[Friday]="X","X",[Weekly_StartTime_Challenges].[Friday]) AS Fri,
IIf([Weekly_Challenges].[Saturday]="X","X",[Weekly_StartTime_Challenges].[Saturday]) AS Sat,
IIf([Weekly_Challenges].[Sunday]="X","X",[Weekly_StartTime_Challenges].[Sunday]) AS Sun
FROM Weekly_StartTime_Challenges
RIGHT JOIN (Weekly_Challenges RIGHT JOIN ActionUNION
ON (Weekly_Challenges.StandardAction = ActionUNION.Action) AND (Weekly_Challenges.WeekNumber = ActionUNION.WeekNumber)
AND (Weekly_Challenges.UserID = ActionUNION.UserID)) ON (Weekly_StartTime_Challenges.StartTimeAction = ActionUNION.Action)
AND (Weekly_StartTime_Challenges.WeekNumber = ActionUNION.WeekNumber) AND (Weekly_StartTime_Challenges.UserID = ActionUNION.UserID);

 

[Lkwdmntr]

I worked the past few hours on what I am hoping the final report will look like. Attached is a PDF of the report. I did what I said I was planning on doing and used 4 subreports to get all the data I needed. The only thing I need to do is combine the 2nd and 3rd subreport into one. Maybe having a visual will help a little more. These are the two SQL statements that I used for the subreports.

Actions with the Times

SELECT Weekly_StartTime_Challenges.StartTimeAction, Weekly_StartTime_Challenges.Monday, Weekly_StartTime_Challenges.Tuesday, Weekly_StartTime_Challenges.Wednesday, Weekly_StartTime_Challenges.Thursday, Weekly_StartTime_Challenges.Friday, Weekly_StartTime_Challenges.Saturday, Weekly_StartTime_Challenges.Sunday
FROM Weekly_StartTime_Challenges
WHERE (((Weekly_StartTime_Challenges.Index)>200 And (Weekly_StartTime_Challenges.Index)<300) AND ((Weekly_StartTime_Challenges.UserID)=4) AND ((Weekly_StartTime_Challenges.WeekNumber)=2))
ORDER BY Weekly_StartTime_Challenges.Index;
​

Actions with the X value

SELECT Weekly_Challenges.StandardAction, Weekly_Challenges.Monday, Weekly_Challenges.Tuesday, Weekly_Challenges.Wednesday, Weekly_Challenges.Thursday, Weekly_Challenges.Friday, Weekly_Challenges.Saturday, Weekly_Challenges.Sunday
FROM Weekly_Challenges
WHERE (((Weekly_Challenges.Index)>323 And (Weekly_Challenges.Index)<338) AND ((Weekly_Challenges.UserID)=4) AND ((Weekly_Challenges.WeekNumber)=2))
ORDER BY Weekly_Challenges.Index;​

 

[Lkwdmntr]

Ok, so I am trying something else here. I combined the data with the timed values and X values into the same query. Here is the SQL I used.

SELECT Weekly_StartTime_Challenges.StartTimeAction, Weekly_StartTime_Challenges.Monday AS MondayT, Weekly_StartTime_Challenges.Tuesday AS TuesdayT, Weekly_StartTime_Challenges.Wednesday AS WednesdayT, Weekly_StartTime_Challenges.Thursday AS ThursdayT, Weekly_StartTime_Challenges.Friday AS FridayT, Weekly_StartTime_Challenges.Saturday AS SaturdayT, Weekly_StartTime_Challenges.Sunday AS SundayT, Weekly_Challenges.StandardAction, Weekly_Challenges.Monday AS MondayX, Weekly_Challenges.Tuesday AS TuesdayX, Weekly_Challenges.Wednesday AS WednesdayX, Weekly_Challenges.Thursday AS ThursdayX, Weekly_Challenges.Friday AS FridayX, Weekly_Challenges.Saturday AS SaturdayX, Weekly_Challenges.Sunday AS SundayX
FROM Weekly_StartTime_Challenges, Weekly_Challenges
WHERE (((Weekly_StartTime_Challenges.StartTimeAction)=[Weekly_Challenges]![StandardAction]) AND ((Weekly_Challenges.StandardAction)=[Weekly_StartTime_Challenges]![StartTimeAction]) AND ((Weekly_StartTime_Challenges.Index)>200 And (Weekly_StartTime_Challenges.Index)<300) AND ((Weekly_StartTime_Challenges.UserID)=4) AND ((Weekly_StartTime_Challenges.WeekNumber)=2) AND ((Weekly_Challenges.UserID)=4) AND ((Weekly_Challenges.WeekNumber)=2) AND ((Weekly_Challenges.Index)>323 And (Weekly_Challenges.Index)<338))
ORDER BY Weekly_StartTime_Challenges.Index And Weekly_Challenges.Index;​

In my report, I made a subreport with the query above. I lined up the rows parallel to each other with the timed on top and the X's on the bottom. I then used VBA to hide the boxes for the Weekly_StartTime_Challenges when there is an "X" in the Weekly_Challenges and if there is anything other then an "X", the Timed boxes show and the X boxes disappear. The code looks good and this should be working. Not getting an error or anything, the boxes are just not disappearing. Heres the VBA code I used.

Private Sub Report_Load()

If Not IsNull(Me.MondayT.Value) Then
Me!MondayT.Visible = True
Me!MondayX.Visible = False
Else
Me!MondayT.Visible = False
Me!MondayX.Visible = True
End If
If Me.TuesdayX.Value = "X" Then
Me.TuesdayX.Visible = True
Me.TuesdayT.Visible = False
Else
Me.TuesdayX.Visible = False
Me.TuesdayT.Visible = True
End If
If Me.WednesdayX.Value = "X" Then
Me.WednesdayX.Visible = True
Me.WednesdayT.Visible = False
Else
Me.WednesdayX.Visible = False
Me.WednesdayT.Visible = True
End If
If Me.ThursdayX.Value = "X" Then
Me.ThursdayX.Visible = True
Me.ThursdayT.Visible = False
Else
Me.ThursdayX.Visible = False
Me.ThursdayT.Visible = True
End If
If Me.FridayX.Value = "X" Then
Me.FridayX.Visible = True
Me.FridayT.Visible = False
Else
Me.FridayX.Visible = False
Me.FridayT.Visible = True
End If
If Me.SaturdayX.Value = "X" Then
Me.SaturdayX.Visible = True
Me.SaturdayT.Visible = False
Else
Me.SaturdayX.Visible = False
Me.SaturdayT.Visible = True
End If
If Me.SundayX.Value = "X" Then
Me.SundayX.Visible = True
Me.SundayT.Visible = False
Else
Me.SundayX.Visible = False
Me.SundayT.Visible = True
End If

End Sub​

A copy of the report is attached. Please Help, I'm almost there.

 

[June7]

Must use section OnFormat event to dynamically control visibility of controls. OnFormat events only execute for direct to printer or PrintPreview.

Did queries in post 26 not provide desired output?

 

[Lkwdmntr]

Hi June7,
When I ran the queries, I saw 180 records and thought it wasn't working. After doing this again and looking a liitle closer, it is working. Could you possibly help me narrow down the search? I want to use UserID=4 and WeekNumber=2 to test it and then I will plug in the temporary variables. I also wanted things sorted by the time, but I don't know if that will work. If not by time then the index would have to be included in the Select Statement and sorted by index. The index will not be shown on the report. I really appreciate your help. Today is my birthday and this would be the best present ever if this works. I have a meeting in less than 2 hours to show this. Hopefully, you will get this soon. If I end up going the other way, there is no OnFormat in the properties. Do I just make a subroutine that uses OnFormat?

 

[Lkwdmntr]

Hi, I figured that part out, got 44 records, which is perfect. I think what I would like to do is use the report I have and use the index's to narrow down the records so the subreport is only the Timed vales and X values for the indexes >210 < 338 in the Weekly_StartTime_Challenges and >323 < 338 in the Weekly_Challenges table. I really like having the RiseTime, BedTime and all the meals separate, and the Basic Action and Measured and Logged Actions sperate as well. Wow, almost there. I might actually make it for the meeting.

 

[Lkwdmntr]

I just wanted to thank everyone that helped me get this problem solved. It is working well. Haven't run a lot of tests on it yet, but my hopes are high. Thanks Again.

 

[theDBguy]

I just wanted to thank everyone that helped me get this problem solved. It is working well. Haven't run a lot of tests on it yet, but my hopes are high. Thanks Again.

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

 

[June7]

Recommend not having dynamic parameters in query. Apply filter criteria to report when it is opened. Example:

DoCmd.OpenReport "reportName", , , "UserID=" & Me.tbxUser & " And WeekNumber=" & Me.tbxWeek

If you prefer, can probably use TempVars in place of the textbox references, but I have never used TempVars.

Here is a tutorial showing how to dynamically build filter criteria in VBA http://allenbrowne.com/ser-62.html

Also, set sort criteria in report design, not in query. Any sorting or grouping set in report design will override query sort.

And again, why 'plug in' TempVars for display on report? Should be able to pull values from report RecordSource.