Cross-quotient query

[dougie]

I have a table with 10 records, each record with columns A1, A2, A3 etc. A second table also has 10 records with columns B1, B2, B3 etc. I want to write a query that returns 10 records with columns A1/B1, A2/B2, A3/B3 etc. Is this possible without creating temporary tables?

[June7]

Do these tables have a common key field? Your first column set has A1, B1, C1 but not the A2 shown in the calcs. Also don't show C1 in any calc. Need complete sample of source data and description of output.

[dougie]

DB with sample tables attached. Typos in my original post corrected, sorry. The common primary key is CellID. The resultant query should have the same number of rows (one for each cell) and 12 columns, each column containing the An/Bn quotient.

[June7]

Then this is a simple SELECT with one-to-one (INNER) join of the tables and field calculations. This is what queries are for, manipulate data.

SELECT tblTable1.CellID, [tblTable1].[1]/[tblTable2].[1] AS Calc1
FROM tblTable2 INNER JOIN tblTable1 ON tblTable2.CellID = tblTable1.CellID;

I show one calculation, you can build the other 11. This is basic Access functionality and Access Help has guidelines for building queries with calcs if you need more info.

[dougie]

Thanks, June 7. My real problems begin when the number of columns (it's actually output from a crosstab, not a table) cannot be predicted. I suspect that temporary tables then become unavoidable --- unless you know different....

[June7]

Nope, if you are dealing with dynamic crosstabs, temp table probably it. Could use VBA code to automate the whole process. Would use a lot of looping structures, recordsets, recordset fields collection. Somewhat advanced programming, above novice level anyway.