Access World Forums

Access World Forums (https://www.access-programmers.co.uk/forums/index.php)
-   Reports (https://www.access-programmers.co.uk/forums/forumdisplay.php?f=10)
-   -   Creating a Report from Two Tables (https://www.access-programmers.co.uk/forums/showthread.php?t=306635)

Lkwdmntr 08-31-2019 09:28 AM

Creating a Report from Two Tables
 
1 Attachment(s)
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.

June7 08-31-2019 10:05 AM

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

theDBguy 08-31-2019 10:08 AM

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...

Lkwdmntr 08-31-2019 11:10 AM

Re: Creating a Report from Two Tables
 
1 Attachment(s)
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;

isladogs 08-31-2019 11:23 AM

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

June7 08-31-2019 03:05 PM

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.

Lkwdmntr 08-31-2019 04:27 PM

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?

June7 08-31-2019 05:03 PM

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."

Lkwdmntr 08-31-2019 05:19 PM

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.

June7 08-31-2019 08:43 PM

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.

Lkwdmntr 09-01-2019 05:28 AM

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.

arnelgp 09-01-2019 05:57 AM

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.

mike60smart 09-01-2019 07:27 AM

Re: Creating a Report from Two Tables
 
1 Attachment(s)
Hi

Is the following the expected output you need:-

Attachment 76310

mike60smart 09-01-2019 07:43 AM

uery1 and Query2
 
1 Attachment(s)
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

Attachment 76311

Lkwdmntr 09-01-2019 08:48 AM

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.


All times are GMT -8. The time now is 03:36 PM.

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