using a query on two other queries

[Layth]

I have made two queries based on the same table. One is a cross tab table to count a the number of a certain type of record. The other calculates a mathematical equation based on a field in the record and returns a new field with the answer to the new field.

When I try to query both of these queries I get an error saying:

You have chosen fields from record sources which the wizard can't connect. You may have chosen fields from a table and from a query based on that table. If so try choosing fields from only the table or only the query.

 

[BNPMike]

Can you post the SQL from each of the two initial queries and then tell us what you are trying to do with the two queries to make the third.

Are you doing this in the Query-by-Example Screen? If so how are you joining the two queries?

 

[Layth]

I can't get to the code until tomorrow, but definitely will post then.

thanks,

Layth

 

[Layth]

Okay this is what I'm trying to do, the code is posted at the bottom. I have a report that I'm making for our customers. In the table I have fields of ALTACCTNBR, AMPORDERFK, SLUGLINE, BASEAMOUNT, RUNDATE, REALMILLIMETERS among other fields.

What I need to do is count the number of AMPORDERFK that are the same for each ALTACCTNBR, there are multiple, which is returned in insertion count crosstab query.

I also need a mathematical query that converts the millimeters field into a field with inches, I also have a mathematical query that then applies the inches to a rate, but I left this out because if you can help with the next step, I should be able to figure the rest on my own.

The problem is that I need to be able to access the information REALINCHES and Total of AMPORDERFK in the same query and also access other fields from the original table as I have more calculations to do.

The original table has linked tables to another database that is updating moderately often, so I don't think I can just export the data into another table, because I will lose the data. Is there any way of joining the two queries to make one, The only info that I need from the crosstab is the Total of AMPORDERFK as that is the number of times I need to apply the rate.



Code for insertion count crosstab query:

TRANSFORM Count([Folio Sepation by rate].AMPORDERFK) AS CountOfAMPORDERFK
SELECT [Folio Sepation by rate].ALTACCTNBR, [Folio Sepation by rate].SLUGLINE, [Folio Sepation by rate].BASEAMOUNT, Count([Folio Sepation by rate].AMPORDERFK) AS [Total Of AMPORDERFK]
FROM [Folio Sepation by rate]
GROUP BY [Folio Sepation by rate].ALTACCTNBR, [Folio Sepation by rate].SLUGLINE, [Folio Sepation by rate].BASEAMOUNT
PIVOT Format([RUNDATE],"Short Date");

Code for Math procedure query:

SELECT REALMILLIMETERS, AMPORDERFK, Round((REALMILLIMETERS*(1/25.4))*REALCOLS,2) AS REALINCHES
FROM [Folio Sepation by rate];