make table query specifics

[vegak18]

Dear Experts,

I was was wondering if there was some way to specify output properties of a make table query. Specifically, I am haivng a problem with numbers being specified as text. while I realize I can go in and change the table properties after the table has been created, I create and replace this table quite often so that would become a laborious prcess. Is there some way I can make the query specify that for me. I am using access 2003 and the VBA script of the make talbe query is as follows:

SELECT Accounts.AccountNumber, Accounts.AccountName, Section1Budget.budgetedAmount, NZ([Section 1 Reappropriations]![budgetedAmount],0) AS Reappropriations, NZ(NZ([Section1Budget]![budgetedAmount])+NZ([Section 1 Reappropriations]![budgetedAmount]),0) AS [current appropriations], NZ([section1directPayVouchers]![SumOfAmount])+ NZ([Section1SPO]![SumOfAmount])+ NZ([Section1Requsitions]![SumOfAmount]) AS Encumbrances, NZ([current appropriations]-[Encumbrances],0) AS [budget afterencumbrances], NZ([appaidsection1]![SumOfAPPaidAmount],0) AS APPaid, NZ([current appropriations],0)-NZ([APPaid],0) AS [Expendable Funds] INTO section1report
FROM Section1Budget RIGHT JOIN (Section1Requsitions RIGHT JOIN (section1directPayVouchers RIGHT JOIN ([Section 1 Reappropriations] RIGHT JOIN (appaidsection1 RIGHT JOIN (Accounts LEFT JOIN Section1SPO ON Accounts.AccountNumber = Section1SPO.AccountNumber) ON appaidsection1.AccountNumber1 = Accounts.AccountNumber) ON [Section 1 Reappropriations].[Account Number] = Accounts.AccountNumber) ON section1directPayVouchers.Account = Accounts.AccountNumber) ON Section1Requsitions.Account = Accounts.AccountNumber) ON Section1Budget.[Account Number] = Accounts.AccountNumber;
 

any help you can give me would be greatly appreciated.

-Andy

[Scott Price]

Dear Experts,

I was was wondering if there was some way to specify output properties of a make table query. Specifically, I am haivng a problem with numbers being specified as text. while I realize I can go in and change the table properties after the table has been created, I create and replace this table quite often so that would become a laborious prcess. Is there some way I can make the query specify that for me. I am using access 2003 and the VBA script of the make talbe query is as follows:

SELECT Accounts.AccountNumber, Accounts.AccountName, Section1Budget.budgetedAmount, NZ([Section 1 Reappropriations]![budgetedAmount],0) AS Reappropriations, NZ(NZ([Section1Budget]![budgetedAmount])+NZ([Section 1 Reappropriations]![budgetedAmount]),0) AS [current appropriations], NZ([section1directPayVouchers]![SumOfAmount])+ NZ([Section1SPO]![SumOfAmount])+ NZ([Section1Requsitions]![SumOfAmount]) AS Encumbrances, NZ([current appropriations]-[Encumbrances],0) AS [budget afterencumbrances], NZ([appaidsection1]![SumOfAPPaidAmount],0) AS APPaid, NZ([current appropriations],0)-NZ([APPaid],0) AS [Expendable Funds] INTO section1report
FROM Section1Budget RIGHT JOIN (Section1Requsitions RIGHT JOIN (section1directPayVouchers RIGHT JOIN ([Section 1 Reappropriations] RIGHT JOIN (appaidsection1 RIGHT JOIN (Accounts LEFT JOIN Section1SPO ON Accounts.AccountNumber = Section1SPO.AccountNumber) ON appaidsection1.AccountNumber1 = Accounts.AccountNumber) ON [Section 1 Reappropriations].[Account Number] = Accounts.AccountNumber) ON section1directPayVouchers.Account = Accounts.AccountNumber) ON Section1Requsitions.Account = Accounts.AccountNumber) ON Section1Budget.[Account Number] = Accounts.AccountNumber;

any help you can give me would be greatly appreciated.

-Andy

Field names and data types are inherited from the source table in a make table query...

Are you saying that this isn't happening, and that instead some number data type fields are being transferred across into text fields? Which specific fields in the sql above is this happening with?

Regards,
Scott

[vegak18]

Field names and data types are inherited from the source table in a make table query...

Are you saying that this isn't happening, and that instead some number data type fields are being transferred across into text fields? Which specific fields in the sql above is this happening with?

Regards,
Scott

It is happening to the appropriations field, reappropriaions field, budget after encumbrances, and APPaid

[vegak18]

It is happening to the appropriations field, reappropriaions field, budget after encumbrances, and APPaid

and yeah... all the tables it works off of are all correct and represent the numbrs as numbers..... but for some reason when it translates into this query it turns the numbers into text fields

[Scott Price]

Strange... I'm not sure why it's doing it this way, when it should be inheriting the field attributes from your parent table.

There's no way that I know of to make an SQL make-table query reset the field attributes. To do this programmatically you'll need to explore the DAO tabledefs/indexes/fields/attributes collections.

Regards,
Scott

[MMcCarthy]

Firstly you haven't specified the value if Null on many of the nz() functions. I think the default is "". This may be your problem. Try the following:

SELECT Accounts.AccountNumber, Accounts.AccountName, Section1Budget.budgetedAmount, NZ([Section 1 Reappropriations]![budgetedAmount],0) AS Reappropriations, NZ(NZ([Section1Budget]![budgetedAmount],0)+NZ([Section 1 Reappropriations]![budgetedAmount],0) AS [current appropriations], NZ([section1directPayVouchers]![SumOfAmount],0)+ NZ([Section1SPO]![SumOfAmount],0)+ NZ([Section1Requsitions]![SumOfAmount],0) AS Encumbrances, NZ([current appropriations]-[Encumbrances]),0) AS [budget afterencumbrances], NZ([appaidsection1]![SumOfAPPaidAmount],0) AS APPaid, NZ([current appropriations],0)-NZ([APPaid],0) AS [Expendable Funds] INTO section1report
FROM Section1Budget RIGHT JOIN (Section1Requsitions RIGHT JOIN (section1directPayVouchers RIGHT JOIN ([Section 1 Reappropriations] RIGHT JOIN (appaidsection1 RIGHT JOIN (Accounts LEFT JOIN Section1SPO ON Accounts.AccountNumber = Section1SPO.AccountNumber) ON appaidsection1.AccountNumber1 = Accounts.AccountNumber) ON [Section 1 Reappropriations].[Account Number] = Accounts.AccountNumber) ON section1directPayVouchers.Account = Accounts.AccountNumber) ON Section1Requsitions.Account = Accounts.AccountNumber) ON Section1Budget.[Account Number] = Accounts.AccountNumber;
 

If that doesn't work there is a CCur() function you can use around the calculations which should force the appropriate output.

CCur(NZ([Section 1 Reappropriations]![budgetedAmount],0)) AS Reappropriations