Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-01-2019, 09:32 AM   #16
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,455
Thanks: 0
Thanked 572 Times in 568 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Creating a Report from Two Tables

Why would you use TempVar to supply data to report instead of pulling data from RecordSource?

This is the query that returns 52 records:

SELECT Weekly_Challenges.WeekNumber, Weekly_Challenges.FullName, Weekly_Challenges.StartDate, Weekly_Challenges.StandardAction AS [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
INNER JOIN Weekly_Challenges
ON (Weekly_Challenges.StandardAction = Weekly_StartTime_Challenges.StartTimeAction)
AND (Weekly_StartTime_Challenges.UserID = Weekly_Challenges.UserID)
AND (Weekly_StartTime_Challenges.WeekNumber = Weekly_Challenges.WeekNumber);

Again, problem is, there are actions in each table that are not in the other so regardless of join type, some data gets dropped. Need a dataset of all possible actions, join each table to that dataset and do the daily calcs. Actually, need a dataset of all possible User, Week, Action combinations.

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is online now   Reply With Quote
Old 09-01-2019, 10:45 AM   #17
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 463
Thanks: 21
Thanked 92 Times in 91 Posts
mike60smart will become famous soon enough
Re: Creating a Report from Two Tables

Hi

The Report now shows all data from both tables

It may help if you actually gave us an example of what you would expect to see in your Report?

TablesDB.zip
mike60smart is offline   Reply With Quote
Old 09-01-2019, 10:56 AM   #18
Lkwdmntr
Newly Registered User
 
Join Date: Jul 2019
Posts: 105
Thanks: 2
Thanked 0 Times in 0 Posts
Lkwdmntr is on a distinguished road
Re: Creating a Report from Two Tables

My reasoning behind using TmpVar's is when I am working with a certain user and week number, those are locked into TmpVar's for easy access. All my reports and forms center around narrowing records down to the UserID and the WeekNumber.

I am not sure how to go about making sure I get all the records with values(either timed or X's), but if you noticed every action has an index number. The timed actions with an index between 210 - 223 share the possibility of having an X value. Those X value actions have an index between 324 - 337. All the other actions will either have a timed value or an X value. Index's 1-109 are all timed only and 338 - 562 are X's only.

What if there were three different subreports, you would barely even notice the separation if placed properly on the report. This is how I put together the daily reports.

The tricky one for me is combining the action with both timed and X values.

Perhaps with this new information, a better solution can be found to include all the records, which is a must.

Again, I appreciate all the help and effort I am getting with this perplexing issue.

If you use UserID 4 and WeekNumber 2, we can see the results. If you use WeekNumber 1 there are no actions that have both timed and X values.

Lkwdmntr is offline   Reply With Quote
Old 09-01-2019, 11:03 AM   #19
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 463
Thanks: 21
Thanked 92 Times in 91 Posts
mike60smart will become famous soon enough
Re: Creating a Report from Two Tables

Hi

This shows all the data for UserID 4 and Week 1 from StartTimeAction table

StartTimeAction.PNG

This shows all the data for UserID 4 and Week 1 from StandardAction table

StandardAction.PNG

Note all fields contain data.

Please explain what you would expect to see in your Report.
mike60smart is offline   Reply With Quote
Old 09-01-2019, 11:06 AM   #20
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 463
Thanks: 21
Thanked 92 Times in 91 Posts
mike60smart will become famous soon enough
Re: Creating a Report from Two Tables

Hi

What is the purpose of Tab2 and Index fields??
mike60smart is offline   Reply With Quote
Old 09-01-2019, 11:16 AM   #21
Lkwdmntr
Newly Registered User
 
Join Date: Jul 2019
Posts: 105
Thanks: 2
Thanked 0 Times in 0 Posts
Lkwdmntr is on a distinguished road
Re: Creating a Report from Two Tables

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 is offline   Reply With Quote
Old 09-01-2019, 11:24 AM   #22
Lkwdmntr
Newly Registered User
 
Join Date: Jul 2019
Posts: 105
Thanks: 2
Thanked 0 Times in 0 Posts
Lkwdmntr is on a distinguished road
Re: Creating a Report from Two Tables

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.

Lkwdmntr is offline   Reply With Quote
Old 09-01-2019, 11:31 AM   #23
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 463
Thanks: 21
Thanked 92 Times in 91 Posts
mike60smart will become famous soon enough
Re: Creating a Report from Two Tables

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?
mike60smart is offline   Reply With Quote
Old 09-01-2019, 11:43 AM   #24
Lkwdmntr
Newly Registered User
 
Join Date: Jul 2019
Posts: 105
Thanks: 2
Thanked 0 Times in 0 Posts
Lkwdmntr is on a distinguished road
Re: Creating a Report from Two Tables

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.
Lkwdmntr is offline   Reply With Quote
Old 09-01-2019, 11:57 AM   #25
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 463
Thanks: 21
Thanked 92 Times in 91 Posts
mike60smart will become famous soon enough
Re: Creating a Report from Two Tables

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?
mike60smart is offline   Reply With Quote
Old 09-01-2019, 12:26 PM   #26
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,455
Thanks: 0
Thanked 572 Times in 568 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Creating a Report from Two Tables

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);
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 09-01-2019 at 07:25 PM.
June7 is online now   Reply With Quote
Old 09-01-2019, 06:29 PM   #27
Lkwdmntr
Newly Registered User
 
Join Date: Jul 2019
Posts: 105
Thanks: 2
Thanked 0 Times in 0 Posts
Lkwdmntr is on a distinguished road
Re: Creating a Report from Two Tables

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;
Attached Files
File Type: pdf LkwdmntrDBRpt.pdf (82.6 KB, 7 views)
Lkwdmntr is offline   Reply With Quote
Old 09-02-2019, 10:22 PM   #28
Lkwdmntr
Newly Registered User
 
Join Date: Jul 2019
Posts: 105
Thanks: 2
Thanked 0 Times in 0 Posts
Lkwdmntr is on a distinguished road
Re: Creating a Report from Two Tables

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.
Attached Files
File Type: pdf Report.pdf (85.3 KB, 1 views)
Lkwdmntr is offline   Reply With Quote
Old 09-02-2019, 10:30 PM   #29
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,455
Thanks: 0
Thanked 572 Times in 568 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Creating a Report from Two Tables

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?
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is online now   Reply With Quote
Old 09-03-2019, 07:39 AM   #30
Lkwdmntr
Newly Registered User
 
Join Date: Jul 2019
Posts: 105
Thanks: 2
Thanked 0 Times in 0 Posts
Lkwdmntr is on a distinguished road
Re: Creating a Report from Two Tables

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 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] creating a report form multiple tables mmcgrail Reports 6 11-06-2014 08:48 AM
Linking Tables and Creating Reports based on those linked tables rexmorgan Tables 1 01-30-2011 08:03 PM
Creating a report from Multiple Tables ddmcmullan Reports 1 06-10-2010 10:19 AM
Creating tables for a report nblade Reports 3 12-28-2006 02:44 PM
Creating a report from multiple tables/forms apples76 Queries 4 06-19-2006 10:47 AM




All times are GMT -8. The time now is 02:54 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World