Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

Compare two Crosstabs

I need to do a field by field comparison on the result of a crosstab query and a table.

Table 1 contains a list of datapoints that I've collected and for each ID if the data was collected i've denoted it w/ a 1.
Table 2 contains a list of datapoints that has been processed and in table 2 processed data is also denoted w/ a 1.

Table 3 needs to contain hits (a 1)  for each datapoint that has been collected but has not been processed in essence creating a to do list
for each ID by datapoint

Sample Data:

table 1
ID  dp1  dp2  dp3  dp4  dp5
1   0      1      0      1     1
2   0      0      1      0     1
3   1      0      1      1     1
4   0      1      1      1     0
5   1      1      1      0     0  

table 2
ID  dp1  dp2  dp3  dp4  dp5
1   0      1      0      0     0
2   0      0      1      0     1
3   1      0      1      0     1
4   0      0      0      0     0
5   0      1      0      0     0

table 3
ID  dp1  dp2  dp3  dp4  dp5
1   0      0      0      1     1
2   0      0      0      0     0
3   0      0      0      1     0
4   0      1      1      1     0
5   1      0      1      0     0

0
swingler
Asked:
swingler
1 Solution
 
Eric FlammSenior ConsultantCommented:
Sounds like a logical "and" problem - bitwise arithmetic and all that. However, in Access, it's also pretty easy, I think.

I don't know if you need a general or specific solution  - here'a specific one:

create a query as follows:

select c.ID, c.dp1-p.dp1 as ToDoDp1, c.dp2-p.dp2 as ToDoDp2, c.dp3-p.dp3 as ToDoDp3, c.dp4-p.dp4 as ToDoDp4,
c.dp5-p.dp5 as ToDoDp5 from
Table1 c INNER JOIN Table2 p where c.ID=p.ID

note that the output field names are all prefaced with ToDo - that's just a field name, not a table alias like c and p.

I just used arithmetic - you could actually do this with Boolean datatypes (True/False) and the Nand (Not and) operator.

-ef
0
 
swinglerAuthor Commented:
perfect!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now