Report\Cross tab queries not working please help

[hrg]

Hi Here goes

Problem 1

I have two tables populated with data, then i have a a query and a report. What i want to do is insert two fields one field from table A & one field from table B into the single report i am having problems so far due to the record source (as it only allows one linked table). How do i solve this problem.

Problem 2

I have data which is represented by numbers in two colums. The numbers go up to 10 i.e.

Product 1 = 1
Product 2 = 2
Product 3 = 3
Product 4 = 4
Product 5 = 5
Product 6 = 6
Product 7 = 7
Product 8 = 8
Product 9 = 9
Product 10 = 10

so the columns look like as follow

Column 1 Column2
1 1
1 1
3 8
1 1
1 1
1 1
6 10
7 7

How can i manipulate this data so that i can insert it into the query i.e.

IE get it so i can see how many 1, 3, 6 's etc there are. I tried using cross tab queries but it seems to long winded.

e.g.

Column 1 Column 2

1 = 5 1 = 5
3 = 1 8 = 1
6 = 1 10 = 1
7 = 1 7 = 1

so this query would tell me there is a 5 number 1's in coulmn A and 5 in column B and so on.

Please can anyone help i would really appreicate it.

Cheers

H

 

[mflancour]

For problem 2 u need to have 2 queries. One for each column you want to show totals. Add the column you want to total and one other (second column is unimportant). Select View-Totals from your menu bar on top. In the colmn you wish to count under the total field now showing select "group by" in the second, random column total field select "count". Below is the sql code for this (tempTable should be replaced by your table name)

SELECT tempTable1.column1, Count(tempTable1.column2) AS CountOfcolumn2
FROM tempTable1
GROUP BY tempTable1.column1;

 

[mflancour]

Problem 1:

I'm not entirly sure I understand what you are attempting with this. If I understand what you are attempting then you simply need to combine these 2 tables using a query first then place that query in the record source.