Creating a Report from Two Tables (1 Viewer)

Lkwdmntr

Registered User.
Local time
Today, 07:45
Joined
Jul 10, 2019
Messages
193
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.
 

Attachments

Last edited:

June7

AWF VIP
Local time
Today, 06:45
Joined
Mar 9, 2014
Messages
3,076
This question is a continuation of https://access-programmers.co.uk/forums/showthread.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
 
Last edited:

theDBguy

I’m here to help
Local time
Today, 07:45
Joined
Oct 29, 2018
Messages
10,680
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...
 

Lkwdmntr

Registered User.
Local time
Today, 07:45
Joined
Jul 10, 2019
Messages
193
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;
 

Attachments

isladogs

CID Moderator
Staff member
Local time
Today, 15:45
Joined
Jan 14, 2017
Messages
13,377
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
 

June7

AWF VIP
Local time
Today, 06:45
Joined
Mar 9, 2014
Messages
3,076
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.
 

Lkwdmntr

Registered User.
Local time
Today, 07:45
Joined
Jul 10, 2019
Messages
193
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?
 

June7

AWF VIP
Local time
Today, 06:45
Joined
Mar 9, 2014
Messages
3,076
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."
 

Lkwdmntr

Registered User.
Local time
Today, 07:45
Joined
Jul 10, 2019
Messages
193
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:

June7

AWF VIP
Local time
Today, 06:45
Joined
Mar 9, 2014
Messages
3,076
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.
 
Last edited:

Lkwdmntr

Registered User.
Local time
Today, 07:45
Joined
Jul 10, 2019
Messages
193
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.
 

arnelgp

error reading drive A:
Local time
Today, 22:45
Joined
May 7, 2009
Messages
9,574
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.
 

mike60smart

Registered User.
Local time
Today, 07:45
Joined
Aug 6, 2017
Messages
533
Hi

Is the following the expected output you need:-

Query.PNG
 

mike60smart

Registered User.
Local time
Today, 07:45
Joined
Aug 6, 2017
Messages
533
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

View attachment TablesDB.zip
 

Lkwdmntr

Registered User.
Local time
Today, 07:45
Joined
Jul 10, 2019
Messages
193
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.
 

June7

AWF VIP
Local time
Today, 06:45
Joined
Mar 9, 2014
Messages
3,076
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.
 

Lkwdmntr

Registered User.
Local time
Today, 07:45
Joined
Jul 10, 2019
Messages
193
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.
 

mike60smart

Registered User.
Local time
Today, 07:45
Joined
Aug 6, 2017
Messages
533
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

Registered User.
Local time
Today, 07:45
Joined
Aug 6, 2017
Messages
533
Hi

What is the purpose of Tab2 and Index fields??
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom