Go Back   Access World Forums > Microsoft Access Discussion > Reports

Thread Tools Rate Thread Display Modes
Prev Previous Post   Next Post Next
Old 09-01-2019, 09:32 AM   #18
June7's Avatar
Join Date: Mar 2014
Location: The Great Land
Posts: 2,423
Thanks: 0
Thanked 567 Times in 563 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.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote

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 01:17 PM.

Microsoft Access Help
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