Query Output to a Table 1

[nithink]

Hi,
I've a query which creates an output with many columns. I
want to transfer the whole query output to a new table..
The query output has some decimal values in the columns too
which I want to have it the same...It should not get
rounded... I just want to have the same values in the table
as it is in the query output...

Thanks much....

 

[flyover789]

Hi again nithink,

if you append your query result to an existing table:
make shure that the fields in your table, that should contain the decimal values, are formatted as 'number' with fieldsize 'double'.

HTH,
fly

Martin Serra Jr.

http://www.mserrasystems.de

 

[nithink]

Hi Martin,
HOw to export that query output to a new table ?
You were saying abt append query ... how to use that ?
What I did was I exported the query output to a .txt file
and then imported that .txt file to a new table.... But all
the decimal values was rounded... I dont want that to happen.. Is there any easier way ?

Thankx.....

 

[flyover789]

1st: can you please post the SQL of your output query

2nd: please answer this question: do you want to accumulate the output data in just one table over time, or do you want to create a new table each time, the output query is run?

see ya,
fly

Martin Serra Jr.

http://www.mserrasystems.de

 

[nithink]

Answer 1:

SELECT IIf(IsNumeric([dbarc].[acct_num]),IIf(Val([dbarc].[acct_num])>=680000,"70-","44-"),"44-") & [dbarc].[acct_num] AS cl_id, format(dbarc.ret_date-datepart("d",ret_date),'mm/dd/yyyy') AS post_date, 'M' AS post_type, IIf(IsNull([dbsr].[gim2000_id_2]),NZ(Switch([dbarc.rcat_cd]="TOTA","TOTAL",
[dbarc.rcat_cd]="TOTM","TOTM",
[dbarc.rcat_cd]="EQTY","EQTYALL",
[dbarc.rcat_cd]="FIXED","FIXEDALL"),[dbarc.rcat_cd]),[dbsr.gim2000_id_2]) AS perf_class, 'F' AS post_curr_code, 'N/A' AS sloc_cur, 'N' AS perf_cash, 0 AS mkt_val, 0 AS accr_inc, 0 AS accr_int, 0 AS accr_div, 0 AS d_net_tot_ror, 0 AS d_grs_tot_ror, 0 AS e_net_tot_ror, 0 AS e_grs_tot_ror, IIf(([dbsr].[gim2000_ID_3] & "")="N", [dbarc].[ret_pct],Null) AS p_net_tot_ror, IIf(IsNull([dbsr].[gim2000_ID_3]), [dbarc].[ret_pct],Null) AS p_grs_tot_ror, 0 AS d_net_prn_ror, 0 AS d_grs_prn_ror, 0 AS e_net_prn_ror, 0 AS e_grs_prn_ror, 0 AS p_net_prn_ror, 0 AS p_grs_prn_ror, 0 AS d_net_inc_ror, 0 AS d_grs_inc_ror, 0 AS e_net_inc_ror, 0 AS e_grs_inc_ror, 0 AS p_net_inc_ror, 0 AS p_grs_inc_ror, 0 AS flow, 0 AS contribution, 0 AS net_fees, 0 AS grs_fees, 0 AS bond_int, 0 AS txn_costs, 0 AS withhold, 0 AS income, 0 AS additions, 0 AS withdrawals, 0 AS interest, 0 AS dividends, 0 AS bond_int1, 0 AS grs_fees1, 0 AS net_fees1, 0 AS cost, 0 AS grs_wtd_flow, 0 AS net_wtd_flow, 0 AS txn_costs1, 0 AS withhold1, 0 AS wtd_bond_int, 0 AS wtd_txn_costs, 0 AS wtd_withhold
FROM dbo_acct AS dba, dbo_acct_ret_category AS dbarc, dbo_spec_rpt_1 AS dbsr
WHERE dbarc.acct_num = dba.acct_num and
dbsr.acct_num = dba.acct_num and
dbarc.acct_num = dbsr.acct_num and
dbarc.rcat_cd = dbsr.rcat_cd and
dbsr.gim2000_id_1 is null and
dbsr.availability = 'X'
ORDER BY dbarc.ret_date, dbarc.acct_num;

Answer2:
Each time a new table when the query is run..

 

[flyover789]

ok, so change the sql of your output query to the following:

SELECT IIf(IsNumeric([dbarc].[acct_num]),IIf(Val([dbarc].[acct_num])>=680000,"70-","44-"),"44-") & [dbarc].[acct_num] AS cl_id, format(dbarc.ret_date-datepart("d",ret_date),'mm/dd/yyyy') AS post_date, 'M' AS post_type, IIf(IsNull([dbsr].[gim2000_id_2]),NZ(Switch([dbarc.rcat_cd]="TOTA","TOTAL",
                                                                       [dbarc.rcat_cd]="TOTM","TOTM",
                                                                       [dbarc.rcat_cd]="EQTY","EQTYALL",
                                                                       [dbarc.rcat_cd]="FIXED","FIXEDALL"),[dbarc.rcat_cd]),[dbsr.gim2000_id_2]) AS perf_class, 'F' AS post_curr_code, 'N/A' AS sloc_cur, 'N' AS perf_cash, 0 AS mkt_val, 0 AS accr_inc, 0 AS accr_int, 0 AS accr_div, 0 AS d_net_tot_ror, 0 AS d_grs_tot_ror, 0 AS e_net_tot_ror, 0 AS e_grs_tot_ror, IIf(([dbsr].[gim2000_ID_3] & "")="N", [dbarc].[ret_pct],Null) AS p_net_tot_ror, IIf(IsNull([dbsr].[gim2000_ID_3]), [dbarc].[ret_pct],Null) AS p_grs_tot_ror, 0 AS d_net_prn_ror, 0 AS d_grs_prn_ror, 0 AS e_net_prn_ror, 0 AS e_grs_prn_ror, 0 AS p_net_prn_ror, 0 AS p_grs_prn_ror, 0 AS d_net_inc_ror, 0 AS d_grs_inc_ror, 0 AS e_net_inc_ror, 0 AS e_grs_inc_ror, 0 AS p_net_inc_ror, 0 AS p_grs_inc_ror, 0 AS flow, 0 AS contribution, 0 AS net_fees, 0 AS grs_fees, 0 AS bond_int, 0 AS txn_costs, 0 AS withhold, 0 AS income, 0 AS additions, 0 AS withdrawals, 0 AS interest, 0 AS dividends, 0 AS bond_int1, 0 AS grs_fees1, 0 AS net_fees1, 0 AS cost, 0 AS grs_wtd_flow, 0 AS net_wtd_flow, 0 AS txn_costs1, 0 AS withhold1, 0 AS wtd_bond_int, 0 AS wtd_txn_costs, 0 AS wtd_withhold
INTO tbl_output
FROM dbo_acct AS dba, dbo_acct_ret_category AS dbarc, dbo_spec_rpt_1 AS dbsr
WHERE dbarc.acct_num = dba.acct_num and
             dbsr.acct_num   = dba.acct_num and
             dbarc.acct_num = dbsr.acct_num and
             dbarc.rcat_cd = dbsr.rcat_cd and
             dbsr.gim2000_id_1 is null        and
             dbsr.availability = 'X'
ORDER BY dbarc.ret_date, dbarc.acct_num;

Attention:
this SQL overrides the old table tbl_output each time that it is run. to keep all the output tables stored in the DB, would require some VBA coding.

HTH,
fly

Martin Serra Jr.

http://www.mserrasystems.de

 

[nithink]

Thx Martin... It mite have been simple for yu but for me
as I'm totally new to Access I dont know these small
things... Thank yu and yu deserve a star...