By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,142 Members | 1,946 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,142 IT Pros & Developers. It's quick & easy.

make table query specifics

P: 9
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:
Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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;
  3.  
any help you can give me would be greatly appreciated.

-Andy
Sep 19 '07 #1
Share this Question
Share on Google+
5 Replies


Scott Price
Expert 100+
P: 1,384
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:

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
Sep 20 '07 #2

P: 9
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
Sep 20 '07 #3

P: 9
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
Sep 20 '07 #4

Scott Price
Expert 100+
P: 1,384
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
Sep 20 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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:

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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;
  3.  
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
Sep 30 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.