Duplicates in Query result

[Lkwdmntr]

Hello,

I need to create a form that shows all the actions that have been chosen for each week. I am hoping to have all the actions for the first week in the first column and a column for each week's number up to 12. The only thing in the week field will be an "X" if they have chosen that action, which week one should have all X's. Otherwise, it should be blank on other weeks if the chose to pick another action. When actions are added each week, the additional action should show up after the last action in the first column. Here is a quick example.

Action Chosen Wk1 Wk2 Wk3 Wk4 Wk5 Wk6 Wk7

Wk1Action1 X X X X X X X
Wk1Action2 X X X
Wk1Action3 X X X X X X X
Wk1Action4 X X X X
Wk2Action5 X X X X X X
Wk3Action6 X X X X X
Wk3Action7 X X X X X
Wk4Action8 X X X X
Wk4Action9 X X X X
Wk5Action10 X X X
Wk7Action11 X​

The client is allowed to add one action per week and can swap an action; that is why there are two actions for weeks 3 and 4. Notice on week 6 there is no action added, but they chose to bring back a previous action.

I am pulling the data from two different tables. One table has TimedActions and the other are just to be accounted for. There are about 12 actions that can have a timed value and an accountable value. That is where I'm getting duplicates. I created a query that pulls all the information for week 1.

SELECT UserID, WeekNumber, StandardAction AS [Standard Action], Index
FROM Weekly_Challenges
WHERE Weekly_Challenges.UserID=TempVars!TmpUserID And Weekly_Challenges.WeekNumber=TempVars!TmpWeekNumber
ORDER BY Weekly_Challenges.Index
UNION SELECT UserID, WeekNumber,StartTimeAction, Index
FROM Weekly_StartTime_Challenges
WHERE Weekly_StartTime_Challenges.UserID=TempVars!TmpUserID And Weekly_StartTime_Challenges.WeekNumber=TempVars!TmpWeekNumber
ORDER BY Weekly_Challenges.Index;​

This gets all the information I need, but I need to find a way to filter out the duplicate actions and get the added actions for each week on the same form.

If anyone has some ideas on how to approach this, I would greatly appreciate it.

 

[June7]

Data doesn't line up with columns so hard to follow. I really don't follow why weeks are numbered both vertical and horizontal. How do you know a previous action was 'brought back'. There are 52 weeks in a year so why do you show only 7? In other words, none of this makes sense to me. Post raw data from both tables. If you want to provide db for analysis, follow instructions at bottom of my post.

 

[isladogs]

There are two ways of displaying data in this type of format.
1. Use a crosstab query
2. Use multiple union queries
However both of these will result in a read only form.

If you want the data to be editable on your form, some changes will be required

 

[Lkwdmntr]

Hi June7,

Sorry, it has taken so long for me to get back. I had other things that came up. I'm trying to attack this issue again am willing to send you my database. It's all just made up information for testing purposes, but its 4MB and too big to upload here. Any other suggestions on how I might get to you, maybe an email?

 

[Deleted member 147267]

Did you remember to compact then zip it first?

 

[Lkwdmntr]

It has a lot of forms and reports

 

[Lkwdmntr]

My file was too big to upload but I have a google drive link that has the whole project. If anyone can be of assistance, it would greatly be appreciated. Once you have the database and have any questions on what is actually needed, don't hesitate to ask. All the data in the database is fictional and have just been using it for testing purposes.

https://drive.google.com/file/d/1w55FEZzLKvqma2WMVxexur_IQFLTvmBB/view?usp=sharing

 

[June7]

Okay, now which table/query/form?

 

[Lkwdmntr]

Hi June7,

Thanks for the help with this. So here is the query that I came up with. It does what I want, but I need to weed out the duplicates. Some actions can be both timed actions and accountable actions.

SELECT UserID, WeekNumber, StandardAction AS [Standard Action], Index
FROM Weekly_Challenges
WHERE Weekly_Challenges.UserID=TempVars!TmpUserID And Weekly_Challenges.WeekNumber=TempVars!TmpWeekNumber
ORDER BY Weekly_Challenges.Index
UNION SELECT UserID, WeekNumber,StartTimeAction, Index
FROM Weekly_StartTime_Challenges
WHERE Weekly_StartTime_Challenges.UserID=TempVars!TmpUserID And Weekly_StartTime_Challenges.WeekNumber=TempVars!TmpWeekNumber
ORDER BY Weekly_Challenges.Index;​

I was trying to start somewhere. I actually need a report that looks similar to the attached excel file. The client has the choice to swap an action and add an action each week. I need to be able to access this report throughout their program; which lasts 12 weeks. I know it's a little confusing, but I think once we're both on the same page, it won't be that bad.

 

[June7]

I ran that SQL and no records return. Why? Does a particular form need to be open so the TempVars are set? I've never used TempVars.

Again, what objects are involved in this process - table, query, form, report?

I know the tables now but how are TempVars set? What static values should I use to test?

That Excel sample looks like a CROSSTAB result but do you need that for each user?

Why UNION? Why not 1 table to start with? What is purpose of each table?

Records are not duplicates because Index is different. If you don't really care about the Index value, then calculate a substitute in query:

SELECT UserID, FullName, WeekNumber, StandardAction, "X" AS Idx FROM Weekly_Challenges
UNION SELECT UserID, Fullname, WeekNumber, StartTimeAction, "X" FROM Weekly_StartTime_Challenges;

UNION query will not allow duplicate records unless you use UNION ALL.

I would not use an alias name that has space. There is really no need for alias on StandardAction field if you just want to use that field name as UNION field name. Maybe AS Action would be better.

Then use that query in CROSSTAB.

TRANSFORM First(Query1.Idx) AS FirstOfIdx
SELECT Query1.UserID, Query1.FullName, Query1.StandardAction
FROM (SELECT UserID, FullName, WeekNumber, StandardAction AS Action, "X" AS Idx FROM Weekly_Challenges
UNION SELECT UserID, Fullname, WeekNumber, StartTimeAction, "X" FROM Weekly_StartTime_Challenges) AS Query1
GROUP BY Query1.UserID, Query1.FullName, Query1.StandardAction
PIVOT Query1.WeekNumber IN (1,2,3,4,5,6,7,8,9,10,11,12);

 

[Lkwdmntr]

That's exactly right.

What I usually do is start to open a report. Enter - Reporting Forms - Select a person"Rick Smith" is one I have been using the most for testing. - Select a week and a Day - Next.

Once you hit Next the TempVars are assigned and you can go into the design view. They TempVars will stay until you go back to the Main Menu and make a selection, that clears any TempVars.

 

[Lkwdmntr]

Also, that query is named "ActionsByPlanWeek"

 

[Lkwdmntr]

Like I said I was just trying stuff. Union was just a guess.

Yes, I will be needing pulling up the report per user at any time throughout the 12 weeks.

 

[Lkwdmntr]

If you want to use a static value, use "4" for the UserId.

The two tables represent all the actions that the client has chosen to be responsible for. I need to make two separate tables because of the timed value in the "Weekly_StartTime_Challenges" and the "X" value in the "Weekly_Challenges" table.

I hope I answered all your questions.

 

[Lkwdmntr]

June7

I am a little lost with what is going on with that crosstab query, but after making the "Query1" and using the SQL to create the 2nd query; it is asking me for a parameter "Query1.StandardAction"

Also, it is showing all the users when I need the report to be designed around only one client at a time. I put a where clause in Query1 and Query2.

SELECT UserID, FullName, WeekNumber, StandardAction, "X" AS Idx FROM Weekly_Challenges
WHERE UserID=TempVars!TmpUserId
UNION SELECT UserID, Fullname, WeekNumber, StartTimeAction, "X" FROM Weekly_StartTime_Challenges
WHERE UserID=TempVars!TmpUserId;​

It seemed to work, but another question. How can I ignore a couple of actions? I have "Basic Action" and "Basic Action - Medical" in the table to use as headers in a few of my forms. Is there a way I can grab all the action with the exception of those?

 

[June7]

The queries I posted work. The second SQL I posted has the UNION query nested within CROSSTAB.

I really don't know why yours is prompting for StandardAction.

You can include that additional criteria in WHERE clause of each UNION SELECT line or in the CROSSTAB. However, might need to use PARAMETERS clause for the variable input provided by TempVars. Review http://allenbrowne.com/ser-67.html#Param.

 

[Lkwdmntr]

I'm not sure why it didn't work either. I figured out how created a new crosstab query in the design view and got it to work(thanks to your example). My problem now is getting it to recognize a temporary variable. It gives me an error when I use them in either query. Here's what my queries look like now. I need them to only show the user that has the TmpUserID value.

Query1

SELECT UserID, FullName, WeekNumber, StandardAction, "X" AS Idx, Index
FROM Weekly_Challenges
WHERE Weekly_Challenges.StandardAction<>"Basic Action" AND Weekly_Challenges.StandardAction<>"Basic Action - Medical"
ORDER BY WeekNumber, Index
UNION SELECT UserID, Fullname, WeekNumber, StartTimeAction, "X" , Index
FROM Weekly_StartTime_Challenges
WHERE Index < 200
ORDER BY UserID, WeekNumber, Index;​

Query1_Crosstab

TRANSFORM First(Query1.Idx) AS FirstOfIdx
SELECT Query1.UserID, Query1.StandardAction
FROM Query1
GROUP BY Query1.UserID, Query1.StandardAction
PIVOT Query1.WeekNumber;
​

What would be ideal is if I could create a small popup form when you hit the "History/Reports" button on the Main Menu. In the popup would be a drop-down list with all the users that would give the UserID as the parameter. Would I be able to use that field value as the parameter for the query? It would probably look something like this in the Criteria portion of the design view of the crosstab query (Forms![FRM_Select_Users]![SelectUserID]) Something is telling me that if it won't take a TempVars then it won't do the Forms! either.

Any thoughts?

 

[June7]

Did you read the link I posted about dynamic criteria in CROSSTAB?

Don't bother with ORDER BY in each SELECT line. UNION query only recognizes last one which will apply to entire dataset and the order will be irrelevant to CROSSTAB anyway.

You are not using the IN ( ) phrase I used.

 

[Lkwdmntr]

I will probably use the IN phase, I didn't forget about it, but was pleased to know that It will grab as many weeks that are in the database. I just updated the last entry, let me know what you think and I'll look over that link you sent.

 

[Lkwdmntr]

Ok, I'm going to bed after this post, but I wanted to get this out there. I took out the ORDER BY statements in Query1 and was able to add the TempVars as a Parameter(Thanks for that link).

PARAMETERS [TempVars]![TmpUserID] Long;
TRANSFORM First(Query1.Idx) AS FirstOfIdx
SELECT Query1.UserID, Query1.StandardAction
FROM Query1
WHERE (((Query1.UserID)=[TempVars]![TmpUserID]))
GROUP BY Query1.UserID, Query1.StandardAction
ORDER BY Query1.WeekNumber
PIVOT Query1.WeekNumber;
​

I still need to get these sorted properly. I think it is going to be a bit of a challenge, but not sure. My brain is a little fried and I'm not thinking clearly.

If you look at the excel document I attached earlier, all the actions should be sorted by the index for the first two weeks and then after that as the actions are added each week, they should end up on the bottom of the actions column.

God, I hope that makes sense. Catch up with you tomorrow. Thanks for all your help thus far.