Two Questions regarding make table query

grooner10 · #1 (**permalink**) 01-20-12, 10:07

Hi,

I have a make table query below.

SELECT Bulk_id_mapping.BulkID, Sum(import.[Premium Standard]+import.[Premium SubStandard]+import.[Flat Extra Premium]-import.[policy fee]) AS fyp, Sum(import.[Allowance Standard]+import.[Allowance SubStandard]+import.[Flat Extra Allowance]-import.[policy fee Allowance]+import.[Administration Fee Amount]) AS fya INTO firstyear1
FROM Bulk_id_mapping INNER JOIN import ON Bulk_id_mapping.[Sage Treaty] = import.Treaty
WHERE (((import.[Policy Duration])=1))
GROUP BY Bulk_id_mapping.BulkID;

Is it possible to make the query so that when the query is run, the user can input the name of the table? Instead of pre defining it (in this case it is firstyear1)

Also, is there a way of making the fields that are input to be in 'standard" number format and have two decimal places? The fields that are sum sometimes have really long decimal places like 10, and I want it so that there are only two.

thanks,

Kevin.

Sinndho · #2 (**permalink**) 01-20-12, 12:20

You can fix the number of decimal places using the Round function:

Code:

Round([Field1]/[Field2],2) AS x

For the name of the table, you can create the query with a PARAMETERS instruction:

Code:

PARAMETERS  NewTable Text;
SELECT Table1.Field1, Table1.Field2, Round([Field1]/[Field2],2) AS x 
INTO NewTable
FROM Table1

Internet Services

Web Development

Digital Marketing

Consumer Tech