MS-Access Unmatched records to be set as default value (1 Viewer)

venkateshr28

New member
Local time
Today, 10:56
Joined
Apr 2, 2019
Messages
13
Hi Folks,

Need your expert advice on MS access query, below the scenario for your reference.

I have 2 different Table (Table1 & Table2)

Table1:
TeamActivityJan-2019Feb-2019
Group1Type11020
Group2Type11522

Table2:
TeamActivityFromToPercentage
AllAllJan-2019Feb-20195%
Group1AllJan-2019Feb-20194%

Table1 is master and Table2 has matching or unmatching records to Table1
I need a query which Can identify Table2-> Having value "All" which will be unmatched records to Table1 and this unmatching records(percentage Column) to be set as default value in the query result for each month for each row.

Kinldy suggest best way to arrive the result.

Regards,
Venkatesh
 

Ranman256

Well-known member
Local time
Today, 13:56
Joined
Apr 9, 2015
Messages
4,339
Table 1 is designed wrong,
It should be
Group1, jan-2019, 10
Group 1, feb2019,20

Then you can make an outer join to pull unmatched records.
 

venkateshr28

New member
Local time
Today, 10:56
Joined
Apr 2, 2019
Messages
13
Hi Ranman256,

Thank you for your time, really appreciate it,

Unfortunatelhy team is maintaining the records in this manner and since this is bank environment unable to change the source. Any solutionto the the current design that i have please.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:56
Joined
Feb 19, 2013
Messages
16,553
Agree with Ranman's comments

to clarify what you are asking - if group2 in table1 does not have a matching record in table2, you want it to match to the 'All'

I don't understand what this means 'to be set as default value in the query result for each month for each row.'

Suggest from the data you have provided, show the outcome you require. Also - do these tables have autonumber primary keys? if so, provide them.

Your example data is very brief - can there be multiple records for either table for a specific group?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:56
Joined
May 7, 2009
Messages
19,169
it would be easy if you can also show the expected result.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:56
Joined
Feb 19, 2013
Messages
16,553
Unfortunatelhy team is maintaining the records in this manner and since this is bank environment unable to change the source. Any solutionto the the current design that i have please.
in that case definitely need to see some more example data - what happens to table1 for March, April etc?
 

venkateshr28

New member
Local time
Today, 10:56
Joined
Apr 2, 2019
Messages
13
Hi CJ_London & Arnelgp,

Thank you for your time, really appreciate your efforts.

I have modified the table 2 below for better understanding. Basically this Table2 values is from a userfrom which will be input by Head and will have single row.

Table1 will have multiple rows.

Table2 having all values means the percentage in that table applies to all rows in table1

Table1:
TeamActivityJan-2019Feb-2019
Group1Type11020
Group2Type11522
Group1Type2510
Group1Type32131

Table2:
TeamActivityFromToPercentage
AllAllJan-2019Jan-20195%


Expected Result:

Table1:
TeamActivityJan-2019Feb-2019Jan-2019 PercentageFeb-2019 Percentage
Group1Type110205%0%
Group2Type115225%0%
Group1Type25105%0%
Group1Type321315%0%

Thank you for your help / Advice in advance.
 

venkateshr28

New member
Local time
Today, 10:56
Joined
Apr 2, 2019
Messages
13
Hi CJ,

Apologies for not being very clear in one go.
Table1 will have Jan till Dec columns, I tried to change the design unfortunately that will impact some of the automations that is already running based on this design hence trying to find alternate solution with the current desgin.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:56
Joined
Feb 19, 2013
Messages
16,553
that is a completely different example to your first one.

Group1 has disappeared from Table2 and table2 is now just showing Jan-Jan, not Jan-Feb

Will also need to know your data types for dates - are they text or a date/time datatype formatted to Jan-2019?
 

venkateshr28

New member
Local time
Today, 10:56
Joined
Apr 2, 2019
Messages
13
Table1 may have values for any number of month between a year say jan-2019 to Dec-2019.
Data Type would be MMM-YYYY
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:56
Joined
Feb 19, 2013
Messages
16,553
Data Type would be MMM-YYYY
that is not a datatype, that is a format. Open the table in design view, what does it say there

and what about group1 that was in table2 - are you saying there is only the one record?
 

venkateshr28

New member
Local time
Today, 10:56
Joined
Apr 2, 2019
Messages
13
Hi CJ, Yes table2 will have one row
and the format is Date/Time in both the tables
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:56
Joined
Feb 19, 2013
Messages
16,553
so what about Feb -Dec in Table2 - are you saying there is only a value for Jan and nothing for the rest of the year - or next year for that matter?
 

venkateshr28

New member
Local time
Today, 10:56
Joined
Apr 2, 2019
Messages
13
Hi CJ,
Once again thank you for the effort that your putting on this.

data for table2 is from userform input so each time the user enter the input old or existing data in table2 gets deleted and the new row will be added. Then the from and to month in table2 it could be anything between jan-2019 to dec-2019. And the percent should be against the month in the output. In the current example that I have given jan-2019 so the rest of the month should be 0 in my query. What I mean is It should create jan-2019 to dec-2019 in my query in which the percent value should be reflecting against all where ever applicable months as per table2 from and to column
 

venkateshr28

New member
Local time
Today, 10:56
Joined
Apr 2, 2019
Messages
13
Hi Gasman, yes I had posted there in excel forum as well seeking for help, but later realised access forum would be the right one to do so for access related help.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:56
Joined
Feb 19, 2013
Messages
16,553
whew - you really make things complicated. So your output could have up to 26 columns - team, activity plus up to 12 months 'value' columns followed by up to 12 months 'percentage' columns and all columns are the same year and this is for a query, not to be used in a form or report and there is only one record in table2 this is what I suggest.

Edit: See this is cross posted (thanks Gasman). In all honesty given the data is so denormalised, an Excel solution is probably a better solution
 

Users who are viewing this thread

Top Bottom