Make Table Query creates wrong field types...

J

John

I have a make table query that looks great on the screen, but when I run it
some of my number or currency fields get created as text fields in the new
table. How can I get the make table query to match the data type I want? I
tried right-clicking and formating it to currency and/or standard, but it
doesn't seem to work.

Here's the SQL (it's kind of hairy):

SELECT tblMonthly.act AS ActID, Trim([adesc]) AS ActTitle,
tblProjInfo.Period, tblProjInfo.Period_Dte AS Rpt_Dte, tblMonthly.quan_cur AS
BQty, tblMonthly.quan_td AS AQty, tblMonthly.quan_cur-tblMonthly.quan_td AS
RemQty, tblMonthly.um AS UOM, tblMonthly.pcompl_td AS PCT,
tblMonthly.budgt_cur AS BCst, Nz((SELECT TOP 1 tblDupe.budgt_cur FROM
tblMonthly AS tblDupe WHERE tblDupe.act = tblMonthly.act AND tblDupe.FnDte <
tblMonthly.FnDte ORDER BY tblDupe.FnDte Desc, tblDupe.act),0) AS PP_BCst,
[budgt_cur]-Nz((SELECT TOP 1 tblDupe.budgt_cur FROM tblMonthly AS tblDupe
WHERE tblDupe.act = tblMonthly.act AND tblDupe.FnDte < tblMonthly.FnDte ORDER
BY tblDupe.FnDte Desc, tblDupe.act),0) AS Prd_BCst, tblMonthly.ebudgt_td AS
ECst, Nz((SELECT TOP 1 tblDupe.ebudgt_td FROM tblMonthly AS tblDupe WHERE
tblDupe.act = tblMonthly.act AND tblDupe.FnDte < tblMonthly.FnDte ORDER BY
tblDupe.FnDte Desc, tblDupe.act),0) AS PP_ECst, [ebudgt_td]-Nz((SELECT TOP 1
tblDupe.ebudgt_td FROM tblMonthly AS tblDupe WHERE tblDupe.act =
tblMonthly.act AND tblDupe.FnDte < tblMonthly.FnDte ORDER BY tblDupe.FnDte
Desc, tblDupe.act),0) AS Prd_ECst, tblMonthly.actualttd AS ACst, Nz((SELECT
TOP 1 tblDupe.actualttd FROM tblMonthly AS tblDupe WHERE tblDupe.act =
tblMonthly.act AND tblDupe.FnDte < tblMonthly.FnDte ORDER BY tblDupe.FnDte
Desc, tblDupe.act),0) AS PP_ACst, [actualttd]-Nz((SELECT TOP 1
tblDupe.actualttd FROM tblMonthly AS tblDupe WHERE tblDupe.act =
tblMonthly.act AND tblDupe.FnDte < tblMonthly.FnDte ORDER BY tblDupe.FnDte
Desc, tblDupe.act),0) AS Prd_ACst, tblMonthly.whourbudg AS BHrs, Nz((SELECT
TOP 1 tblDupe.whourbudg FROM tblMonthly AS tblDupe WHERE tblDupe.act =
tblMonthly.act AND tblDupe.FnDte < tblMonthly.FnDte ORDER BY tblDupe.FnDte
Desc, tblDupe.act),0) AS PP_BHrs, [whourbudg]-Nz((SELECT TOP 1
tblDupe.whourbudg FROM tblMonthly AS tblDupe WHERE tblDupe.act =
tblMonthly.act AND tblDupe.FnDte < tblMonthly.FnDte ORDER BY tblDupe.FnDte
Desc, tblDupe.act),0) AS Prd_BHrs, nz([whourerntd],0) AS EHrs, Nz((SELECT TOP
1 tblDupe.whourerntd FROM tblMonthly AS tblDupe WHERE tblDupe.act =
tblMonthly.act AND tblDupe.FnDte < tblMonthly.FnDte ORDER BY tblDupe.FnDte
Desc, tblDupe.act),0) AS PP_EHrs, [whourerntd]-Nz((SELECT TOP 1
tblDupe.whourerntd FROM tblMonthly AS tblDupe WHERE tblDupe.act =
tblMonthly.act AND tblDupe.FnDte < tblMonthly.FnDte ORDER BY tblDupe.FnDte
Desc, tblDupe.act),0) AS Prd_EHrs, nz([whouracttd],0) AS AHrs, Nz((SELECT TOP
1 tblDupe.whouracttd FROM tblMonthly AS tblDupe WHERE tblDupe.act =
tblMonthly.act AND tblDupe.FnDte < tblMonthly.FnDte ORDER BY tblDupe.FnDte
Desc, tblDupe.act),0) AS PP_AHrs, [whouracttd]-Nz((SELECT TOP 1
tblDupe.whouracttd FROM tblMonthly AS tblDupe WHERE tblDupe.act =
tblMonthly.act AND tblDupe.FnDte < tblMonthly.FnDte ORDER BY tblDupe.FnDte
Desc, tblDupe.act),0) AS Prd_AHrs, IIf([AHrs]=0 Or [EHrs]=0,0,[AHrs]/[EHrs])
AS PF_TD INTO tblXTab_CMIS
FROM (tblMonthly LEFT JOIN tblActMap ON tblMonthly.act = tblActMap.Act) LEFT
JOIN tblProjInfo ON tblMonthly.FnDte = tblProjInfo.Period_Dte
ORDER BY tblMonthly.act, tblProjInfo.Period;
 
C

Chris O'C via AccessMonster.com

First, I'd try conversion functions like CCur and CLng in the sql. Example:

SELECT CCur(amountowed - amountpaid) as balance, CLng(monthsowed - monthspaid)
as monthstogo
FROM payments

If that doesn't work, create a table first with the right data types and then
write an append query to append the data.

Chris
Microsoft MVP

I have a make table query that looks great on the screen, but when I run it
some of my number or currency fields get created as text fields in the new
table. How can I get the make table query to match the data type I want? I
tried right-clicking and formating it to currency and/or standard, but it
doesn't seem to work.

Here's the SQL (it's kind of hairy):

SELECT tblMonthly.act AS ActID, Trim([adesc]) AS ActTitle,
tblProjInfo.Period, tblProjInfo.Period_Dte AS Rpt_Dte, tblMonthly.quan_cur AS
BQty, tblMonthly.quan_td AS AQty, tblMonthly.quan_cur-tblMonthly.quan_td AS
RemQty, tblMonthly.um AS UOM, tblMonthly.pcompl_td AS PCT,
tblMonthly.budgt_cur AS BCst, Nz((SELECT TOP 1 tblDupe.budgt_cur FROM
tblMonthly AS tblDupe WHERE tblDupe.act = tblMonthly.act AND tblDupe.FnDte <
tblMonthly.FnDte ORDER BY tblDupe.FnDte Desc, tblDupe.act),0) AS PP_BCst,
[budgt_cur]-Nz((SELECT TOP 1 tblDupe.budgt_cur FROM tblMonthly AS tblDupe
WHERE tblDupe.act = tblMonthly.act AND tblDupe.FnDte < tblMonthly.FnDte ORDER
BY tblDupe.FnDte Desc, tblDupe.act),0) AS Prd_BCst, tblMonthly.ebudgt_td AS
ECst, Nz((SELECT TOP 1 tblDupe.ebudgt_td FROM tblMonthly AS tblDupe WHERE
tblDupe.act = tblMonthly.act AND tblDupe.FnDte < tblMonthly.FnDte ORDER BY
tblDupe.FnDte Desc, tblDupe.act),0) AS PP_ECst, [ebudgt_td]-Nz((SELECT TOP 1
tblDupe.ebudgt_td FROM tblMonthly AS tblDupe WHERE tblDupe.act =
tblMonthly.act AND tblDupe.FnDte < tblMonthly.FnDte ORDER BY tblDupe.FnDte
Desc, tblDupe.act),0) AS Prd_ECst, tblMonthly.actualttd AS ACst, Nz((SELECT
TOP 1 tblDupe.actualttd FROM tblMonthly AS tblDupe WHERE tblDupe.act =
tblMonthly.act AND tblDupe.FnDte < tblMonthly.FnDte ORDER BY tblDupe.FnDte
Desc, tblDupe.act),0) AS PP_ACst, [actualttd]-Nz((SELECT TOP 1
tblDupe.actualttd FROM tblMonthly AS tblDupe WHERE tblDupe.act =
tblMonthly.act AND tblDupe.FnDte < tblMonthly.FnDte ORDER BY tblDupe.FnDte
Desc, tblDupe.act),0) AS Prd_ACst, tblMonthly.whourbudg AS BHrs, Nz((SELECT
TOP 1 tblDupe.whourbudg FROM tblMonthly AS tblDupe WHERE tblDupe.act =
tblMonthly.act AND tblDupe.FnDte < tblMonthly.FnDte ORDER BY tblDupe.FnDte
Desc, tblDupe.act),0) AS PP_BHrs, [whourbudg]-Nz((SELECT TOP 1
tblDupe.whourbudg FROM tblMonthly AS tblDupe WHERE tblDupe.act =
tblMonthly.act AND tblDupe.FnDte < tblMonthly.FnDte ORDER BY tblDupe.FnDte
Desc, tblDupe.act),0) AS Prd_BHrs, nz([whourerntd],0) AS EHrs, Nz((SELECT TOP
1 tblDupe.whourerntd FROM tblMonthly AS tblDupe WHERE tblDupe.act =
tblMonthly.act AND tblDupe.FnDte < tblMonthly.FnDte ORDER BY tblDupe.FnDte
Desc, tblDupe.act),0) AS PP_EHrs, [whourerntd]-Nz((SELECT TOP 1
tblDupe.whourerntd FROM tblMonthly AS tblDupe WHERE tblDupe.act =
tblMonthly.act AND tblDupe.FnDte < tblMonthly.FnDte ORDER BY tblDupe.FnDte
Desc, tblDupe.act),0) AS Prd_EHrs, nz([whouracttd],0) AS AHrs, Nz((SELECT TOP
1 tblDupe.whouracttd FROM tblMonthly AS tblDupe WHERE tblDupe.act =
tblMonthly.act AND tblDupe.FnDte < tblMonthly.FnDte ORDER BY tblDupe.FnDte
Desc, tblDupe.act),0) AS PP_AHrs, [whouracttd]-Nz((SELECT TOP 1
tblDupe.whouracttd FROM tblMonthly AS tblDupe WHERE tblDupe.act =
tblMonthly.act AND tblDupe.FnDte < tblMonthly.FnDte ORDER BY tblDupe.FnDte
Desc, tblDupe.act),0) AS Prd_AHrs, IIf([AHrs]=0 Or [EHrs]=0,0,[AHrs]/[EHrs])
AS PF_TD INTO tblXTab_CMIS
FROM (tblMonthly LEFT JOIN tblActMap ON tblMonthly.act = tblActMap.Act) LEFT
JOIN tblProjInfo ON tblMonthly.FnDte = tblProjInfo.Period_Dte
ORDER BY tblMonthly.act, tblProjInfo.Period;
Click to expand...
 
J

John W. Vinson/MVP

I have a make table query that looks great on the screen, but when I run it
some of my number or currency fields get created as text fields in the new
table.
Click to expand...

The first question I'd ask is... why do a make-table query AT ALL?

It's rather rare in practice to even need one. You can base a Report,
a Form, another Query, an export - almost anything you want - on a
Select query, *without* the overhead, hassle, and inefficiency of
running a make-table query.

What is the purpose of this table, once you've made it?
 
J

John

John - I would agree with you and tried to get this to work without making
the table. It was in fact, "plan b". The intent of this record set is to
create a cross tab query that is my "tblMontly.Act" as row headers,
"tblProjInfo.Period" as Column Headers, and various values as the "Value"
(Performance Factor, Quantity, Actual Cost, etc.) The problem I ran into was
a very irritating error: "The Microsft Jet database engin does not recognize
'[tblMonthly]![Act]' as a valid field name or expression." I have tried all
kinds of things (even this forum) and got no solution, perhaps you can help?
Here's the SQL with the code that produces the error:

TRANSFORM Format([tblMonthly]![actualttp]-(SELECT TOP 1 tblDupe.actualttd
FROM tblMonthly AS tblDupe WHERE [tblDupe]![act] = [tblMonthly]![act] AND
tblDupe.FnDte < tblMonthly.FnDte ORDER BY tblDupe.FnDte Desc,
tblDupe.act),"$#,###") AS ActualCost
SELECT Val(tblMonthly!act) AS Activity_ID,
Min(IIf(tblMonthly!actualttd=0,Null,tblProjInfo!period)) AS Start,
Min(IIf(tblMonthly!pcompl_td<100,Null,tblProjInfo!period)) AS [End],
Last(tblMonthly.adesc) AS [Activity Title], Last(tblMonthly.budgt_cur) AS
[Bud Cost], Last(tblMonthly.ebudgt_td) AS [Ern Cost],
Last(tblMonthly.actualttd) AS [Act Cost], Last(tblMonthly.pcompl_td) AS PCT
FROM tblMonthly LEFT JOIN tblProjInfo ON tblMonthly.period =
tblProjInfo.CMIS_Mo
GROUP BY Val(tblMonthly!act)
ORDER BY Val(tblMonthly!act)
PIVOT tblProjInfo.Period;
 
J

John W. Vinson/MVP

The problem I ran into was
a very irritating error: "The Microsft Jet database engin does not recognize
'[tblMonthly]![Act]' as a valid field name or expression."
Click to expand...

Try using a period rather than an exclamation point as a delimiter.
It's not clear from the SQL which instance of [tblMonthly]![Act] is
causing the problem - there *is* a field of that name in the table I
presume?
 
J

John

John,

Fair question... yes tblMonthly.act is in the table. I have even use the
expression builder to "pick" the filed from the table to be sure.

I changed all the "!" to "." and get the same results. To save you some
time, I have also tried all the fields with brackets [] and without, tried
taking all the "AS" out and just used the raw field names, removed the ORDER
BY statement, and removed any other nonessential formatting type functions.
My only conculsion is that cross tab queries can't be used with a query that
uses a subquery. (?)

Here's the SQL without the "!" in it:

TRANSFORM CCur([tblMonthly]![actualttd]- (SELECT TOP 1 tblDupe.actualttd
FROM tblMonthly AS tblDupe WHERE tblDupe.act = tblMonthly.act AND
tblDupe.FnDte < tblMonthly.FnDte ORDER BY tblDupe.FnDte Desc, tblDupe.act))
AS ActualCost
SELECT tblMonthly.act AS Activity_ID,
Min(IIf(tblMonthly.actualttd=0,Null,tblProjInfo.period)) AS Start,
Min(IIf(tblMonthly.pcompl_td<100,Null,tblProjInfo.period)) AS [End],
Last(tblMonthly.adesc) AS [Activity Title], Last(tblMonthly.budgt_cur) AS
[Bud Cost], Last(tblMonthly.ebudgt_td) AS [Ern Cost],
Last(tblMonthly.actualttd) AS ActCost, Last(tblMonthly.pcompl_td) AS PCT
FROM tblMonthly LEFT JOIN tblProjInfo ON tblMonthly.period =
tblProjInfo.CMIS_Mo
GROUP BY tblMonthly.act
ORDER BY tblMonthly.act
PIVOT tblProjInfo.Period;

Thanks again for your input...

--
Thanks - John


John W. Vinson/MVP said:
The problem I ran into was
a very irritating error: "The Microsft Jet database engin does not recognize
'[tblMonthly]![Act]' as a valid field name or expression."
Click to expand...

Try using a period rather than an exclamation point as a delimiter.
It's not clear from the SQL which instance of [tblMonthly]![Act] is
causing the problem - there *is* a field of that name in the table I
presume?
Click to expand...
 
J

John W. Vinson/MVP

John,

Fair question... yes tblMonthly.act is in the table. I have even use the
expression builder to "pick" the filed from the table to be sure.

I changed all the "!" to "." and get the same results. To save you some
time, I have also tried all the fields with brackets [] and without, tried
taking all the "AS" out and just used the raw field names, removed the ORDER
BY statement, and removed any other nonessential formatting type functions.
My only conculsion is that cross tab queries can't be used with a query that
uses a subquery. (?)

Here's the SQL without the "!" in it:

TRANSFORM CCur([tblMonthly]![actualttd]- (SELECT TOP 1 tblDupe.actualttd
FROM tblMonthly AS tblDupe WHERE tblDupe.act = tblMonthly.act AND
tblDupe.FnDte < tblMonthly.FnDte ORDER BY tblDupe.FnDte Desc, tblDupe.act))
AS ActualCost
SELECT tblMonthly.act AS Activity_ID,
Min(IIf(tblMonthly.actualttd=0,Null,tblProjInfo.period)) AS Start,
Min(IIf(tblMonthly.pcompl_td<100,Null,tblProjInfo.period)) AS [End],
Last(tblMonthly.adesc) AS [Activity Title], Last(tblMonthly.budgt_cur) AS
[Bud Cost], Last(tblMonthly.ebudgt_td) AS [Ern Cost],
Last(tblMonthly.actualttd) AS ActCost, Last(tblMonthly.pcompl_td) AS PCT
FROM tblMonthly LEFT JOIN tblProjInfo ON tblMonthly.period =
tblProjInfo.CMIS_Mo
GROUP BY tblMonthly.act
ORDER BY tblMonthly.act
PIVOT tblProjInfo.Period;
Click to expand...

I'm not seeing anything obvious! Try copying the SQL out to a file (or
just to the newsgroup, since you have already done so); delete the
query; compact the database; and recreate the query by copying the SQL
back in.
 
J

John W. Vinson/MVP

I have a make table query that looks great on the screen, but when I run it
some of my number or currency fields get created as text fields in the new
table.
Click to expand...

Second thought:

MakeTable queries can be pretty unreliable about datatypes. Consider
creating your output table, empty, with the desired field types, and
using an Append query instead.
 
J

John

John,

Chris O'C provided a solution that worked (CCur() and CLng()). I'll try the
append query solution and see which works better. I thought of making
several queries all containing the Act field, then put the period, prior
period, and difference between the two, then using a "master" query to join
them all together.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

CrossTab Help! 6
SubQuery Assistance... 2
Dinamically Build a CrossTab 2
Repeating a Total in Mutiple Records. 2
Subquery Assistance 3
Top 3 per group 3
help with ranking in a query 2
rank in select query not right 1

Top