Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-31-2019, 09:28 AM   #1
Lkwdmntr
Newly Registered User
 
Join Date: Jul 2019
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Lkwdmntr is on a distinguished road
Creating a Report from Two Tables

I need to make a report to show all the values in the fields Monday thru Sunday. The difference between the tables is that the "Day" fields in one table are a "Time" format 7:00 AM, and the other table the "Day" fields are a "String" format. I only need the records with a time value in the one table or an "X" as the value in the other table. I am using criteria that narrow the records to the UserID and the WeekNumber. in the final report, I will be using Temporary Variables for the UserID, WeekNumber, FullName, and StartDate. For this example, we could just use UserID = 4 and WeekNumber = 1. This is what I am hoping a few lines will look like.

Action Mon Tue Wed Thu Fri Sat Sun
RiseTime 7:00 AM X X 7:00 AM 7:00 AM
Lunch 12:00 PM 12:00 PM 12:00 PM X 12:00 PM
BedTime X X 11:00PM X 1:00 PM

(This is actually in columns)

You can see that this is going to be a problem because the times are coming from my Weekly_StartTime_Challenges table and the X's are coming from my Weekly_Challanges table. The empty values mean that there are no values for that day. I am going to send the two tables which will I think will help things make a little more sense. I really hope I can get some help with this. I am working a co-op for school and the company is just starting up. I have nobody to turn to for help, so I am turning to you wonderful people. Thanks.
Attached Files
File Type: zip TablesDB.zip (51.2 KB, 5 views)


Last edited by Lkwdmntr; 08-31-2019 at 10:34 AM.
Lkwdmntr is offline   Reply With Quote
Old 08-31-2019, 10:05 AM   #2
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,274
Thanks: 0
Thanked 533 Times in 529 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Creating a Report from Two Tables

This question is a continuation of https://access-programmers.co.uk/for...d.php?t=306508

You have misspelled weekly in table name: Weeikly_Challenges.

Trying to understand data. As others advised in your other thread, structure does not appear to be normalized.

Consider:
Code:
SELECT Weeikly_Challenges.WeekNumber, Weeikly_Challenges.FullName, Weeikly_Challenges.StartDate, 
Weeikly_Challenges.StandardAction AS StandAction, Weekly_StartTime_Challenges.StartTimeAction AS StartAction, 
IIf([Weeikly_Challenges].[Monday]="X","X",[Weekly_StartTime_Challenges].[Monday]) AS Mon, 
IIf([Weeikly_Challenges].[Tuesday]="X","X",[Weekly_StartTime_Challenges].[Tuesday]) AS Tue, 
IIf([Weeikly_Challenges].[Wednesday]="X","X",[Weekly_StartTime_Challenges].[Wednesday]) AS Wed, 
IIf([Weeikly_Challenges].[Thursday]="X","X",[Weekly_StartTime_Challenges].[Thursday]) AS Thu, 
IIf([Weeikly_Challenges].[Friday]="X","X",[Weekly_StartTime_Challenges].[Friday]) AS Fri, 
IIf([Weeikly_Challenges].[Saturday]="X","X",[Weekly_StartTime_Challenges].[Saturday]) AS Sat, 
IIf([Weeikly_Challenges].[Sunday]="X","X",[Weekly_StartTime_Challenges].[Sunday]) AS Sun
FROM Weekly_StartTime_Challenges 
INNER JOIN Weeikly_Challenges 
ON (Weekly_StartTime_Challenges.StartTimeAction = Weeikly_Challenges.StandardAction) 
AND (Weekly_StartTime_Challenges.WeekNumber = Weeikly_Challenges.WeekNumber) 
AND (Weekly_StartTime_Challenges.FullName = Weeikly_Challenges.FullName);
Then you can try RIGHT JOIN and LEFT JOIN instead of INNER JOIN. Any of these get what you want?

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.

I would not use dynamic filters in query. I use VBA to build criteria and apply to report. Review http://allenbrowne.com/ser-62.html
__________________
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; 08-31-2019 at 10:37 AM.
June7 is offline   Reply With Quote
Old 08-31-2019, 10:08 AM   #3
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,650
Thanks: 50
Thanked 1,068 Times in 1,049 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Creating a Report from Two Tables

Hi. I must be missing something. I tried this but not sure if it's what you want.
Code:
SELECT Weeikly_Challenges.UserID, Weeikly_Challenges.FullName, Weeikly_Challenges.WeekNumber, Weekly_StartTime_Challenges.StartTimeAction, IIf([Weeikly_Challenges].[Monday]="X","X",[Weekly_StartTime_Challenges].[Monday]) AS Mon, IIf([Weeikly_Challenges].[Tuesday]="X","X",[Weekly_StartTime_Challenges].[Tuesday]) AS Tue, IIf([Weeikly_Challenges].[Wednesday]="X","X",[Weekly_StartTime_Challenges].[Wednesday]) AS Wed, IIf([Weeikly_Challenges].[Thursday]="X","X",[Weekly_StartTime_Challenges].[Thursday]) AS Thu, IIf([Weeikly_Challenges].[Friday]="X","X",[Weekly_StartTime_Challenges].[Friday]) AS Fri, IIf([Weeikly_Challenges].[Saturday]="X","X",[Weekly_StartTime_Challenges].[Saturday]) AS Sat, IIf([Weeikly_Challenges].[Sunday]="X","X",[Weekly_StartTime_Challenges].[Sunday]) AS Sun
FROM Weeikly_Challenges INNER JOIN Weekly_StartTime_Challenges ON Weeikly_Challenges.UserID = Weekly_StartTime_Challenges.UserID
 WHERE (((Weeikly_Challenges.UserID)=[User ID]) AND ((Weeikly_Challenges.WeekNumber)=[Week Number]));

Edit: Oops, too slow...

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 08-31-2019, 11:10 AM   #4
Lkwdmntr
Newly Registered User
 
Join Date: Jul 2019
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Lkwdmntr is on a distinguished road
Re: Creating a Report from Two Tables

Thanks for the quick input. I corrected the spelling in the table and re-sent it. I also changed some of the data in the tables to test the statement. If you use UserId=4 and WeekNumber=2, then I can see if it is working. This is the statement I used and I am getting 828 records. It seems to be working a little but something is off with a record count like that. I should be getting around 40 records total.

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_StartTime_Challenges.WeekNumber = Weekly_Challenges.WeekNumber) AND (Weekly_StartTime_Challenges.UserID = Weekly_Challenges.UserID)
WHERE Weekly_StartTime_Challenges.UserID=4 AND Weekly_StartTime_Challenges.WeekNumber=2;
Attached Files
File Type: zip TablesDBFixed.zip (50.5 KB, 10 views)
Lkwdmntr is offline   Reply With Quote
Old 08-31-2019, 11:23 AM   #5
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,366
Thanks: 112
Thanked 2,845 Times in 2,593 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Creating a Report from Two Tables

There are some serious issues with your table designs including
1. No primary key fields
2. Duplicated data in the two tables
3. Many fields that should not exist in a database . This is a spreadsheet layout and will make everything you do in Access difficult.

You need to redesign your tables to ensure the data is normalised
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 08-31-2019, 03:05 PM   #6
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,274
Thanks: 0
Thanked 533 Times in 529 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Creating a Report from Two Tables

Downloaded new db. Copy/pasted my suggested query (fixed the table name). Without filtering I get 52 records. You get more because you did not include join on action description.

If you have multi-year data, the year will have to be extracted and that also included in the join.
__________________
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 offline   Reply With Quote
Old 08-31-2019, 04:27 PM   #7
Lkwdmntr
Newly Registered User
 
Join Date: Jul 2019
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Lkwdmntr is on a distinguished road
Re: Creating a Report from Two Tables

All of my other tables do have primary keys and they all result in these two tables. These two tables have several records that pertain to the specific UserID and WeekNumber. I can't use a primary key on these tables because the two fields have several records. As I mentioned before, I am very new at this, if anyone has any suggestions on how to normalize these two tables, I'm open to suggestions.

June7, Where did you put your suggested query?

Lkwdmntr is offline   Reply With Quote
Old 08-31-2019, 05:03 PM   #8
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,274
Thanks: 0
Thanked 533 Times in 529 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Creating a Report from Two Tables

Copy/paste in SQLView of query builder.

But you can just add a link on the action fields in your query.

These two tables are showing FullName not UserID, similarly the action is a full description, not an ID. It is unclear why some fields (Tab1, Tab2, Index, and the 4 yes/no fields) are duplicated between tables.

It is a balancing act between normalization and ease of data entry/output. "Normalize until it hurts, denormalize until it works."
__________________
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 offline   Reply With Quote
Old 08-31-2019, 05:19 PM   #9
Lkwdmntr
Newly Registered User
 
Join Date: Jul 2019
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Lkwdmntr is on a distinguished road
Re: Creating a Report from Two Tables

I just checked the tables and they both have a UserID field, which is a number either 4 or 6 right now. The StartTime Actions and Standard Actions are supposed to be descriptions.

You said to "Copy/paste in SQLView of query builder", but I see no Select Statement. Am I missing something here?

The other fields are needed for Daily reports.

Last edited by Lkwdmntr; 08-31-2019 at 05:25 PM.
Lkwdmntr is offline   Reply With Quote
Old 08-31-2019, 08:43 PM   #10
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,274
Thanks: 0
Thanked 533 Times in 529 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Creating a Report from Two Tables

Oh, sorry about the UserID fields. Don't know how I missed them.

Open a new query, switch to SQLView. Only thing you should see is SELECT;. Can copy/paste or type into that area. Or just build query in Design View and establish the three field links by click and drag. For the query to work, the action descriptions must be common to both tables. Otherwise, as I said, some data will be dropped. If you don't link on action fields you get a Cartesian relationship. There are 36 distinct actions in Weekly_Challenges and 23 distinct actions in Weekly_StartTime_Challenges - the actions in each table will associate with each action in other table which would result in 828 pairings of just the 2 action fields pulled into query. Your query without filter shows 3312 records. Adding the additional field link reduces to 52 records.

I can understand you need fields for reports, I don't understand why they are in both tables. But that is not important to this issue.
__________________
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; 08-31-2019 at 08:51 PM.
June7 is offline   Reply With Quote
Old 09-01-2019, 05:28 AM   #11
Lkwdmntr
Newly Registered User
 
Join Date: Jul 2019
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Lkwdmntr is on a distinguished road
Re: Creating a Report from Two Tables

Could you send me the SQL statement that worked for you and gave you the 52 records? I keep having problems getting this to work. I think it is in the join.

So, as far as those other fields, they are very important and why these tables were created in the first place. I have daily reports that have all the actions sorted by the indexes into subreports and the client checks the boxes if they met their challenge for the day. It's all about accountability.

I appreciate all the help, I just really need to get this final weekly report fixed. I am hoping that the actions that are the same but have time values and X values show up in one line, and the actions that aren't duplicates show up as well.
Lkwdmntr is offline   Reply With Quote
Old 09-01-2019, 05:57 AM   #12
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,406
Thanks: 68
Thanked 2,704 Times in 2,589 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Creating a Report from Two Tables

if you would analyse module1, it has same logic as putting "X" if there is "X" in weekly_challenges, date if there is a Date on weekly_starttime_challenges or "blank" if none.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-01-2019, 07:27 AM   #13
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 428
Thanks: 21
Thanked 90 Times in 89 Posts
mike60smart will become famous soon enough
Re: Creating a Report from Two Tables

Hi

Is the following the expected output you need:-

Query.PNG
mike60smart is offline   Reply With Quote
Old 09-01-2019, 07:43 AM   #14
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 428
Thanks: 21
Thanked 90 Times in 89 Posts
mike60smart will become famous soon enough
uery1 and Query2

Hi

In the attached the "rptPeople" gives you a Total of 43 Records.

I changed the structure slightly by creating a table for People with PeopleID as PK

Linked this table to PeopleID in the 2 tables

Then created a Union Query based on Query1 and Query2

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

Mike60smart,
Thanks for the effort, but this is not what I need. My report will have the name, start date and user id in the header, which will be put in with temporary variables. The detail portion of the report only needs the Action(StandardAction or StartTimeAction), and the days of the week. If you use UserID 4 and WeekNumber 2, there will be many rows that have both timed values and X values for the shared action, but there will never be an X and a timed value under the same day. Some days could also be blank. The timed values are to show that the client committed to a specific time to carry out that particular action and the X is to show that the client is committed to the action, but it can be done at any time throughout the day. Blank means the client is not accountable for the action on that day at all. I hope that makes a little sense.

You shouldn't need an additional table if the UserID and WeekNumer are coming from a Temp Var.

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 08:09 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