Jeff,
Assuming your crosstab query is working the way you wanted, you can do a
few things. The SELECT * INTO syntax, always ways create a new table,
even if that table existed already. In that case, it asks you for
permission to overwwrite it. Which I gather is probably your intend.
Having said that, you can blow out the table by:
DROP TABLE MyTable
Then, run the SELECT * INTO query.
Now you your query to do the SELECT * INTO query, you probably want to
rename the month field, so that would have to be modify as such:
SELECT
Field1, Field2, ..., TheMonthField AS MyMonth...
INTO...
You cannot use month as a field name bc it is a reserved word.
I am not sure I understand what you are saying in #3
Ben
On 2/19/2009 9:47 AM, Jeff wrote:
Ben-
From your post, I set this up as follows into a query name "badparts":
SELECT * INTO 1badparts_tbl
FROM badparts_Crosstab AS INTO1badparts_tbl;
two problems...
1. If I run this a 2nd time I get a "table already exists" error even if
I first run the following SQL:
DELETE *
FROM 1badparts_tbl;
2. Thquery "badparts", mentioned above will populate the table with fields
named from the "PIVOT Format([Date],"mm-yy")" statement in my
badparts_Crosstab query.
If I later attempt to load these on an excel page using automation (My
original intent) then I will not know the actual field name (monthx?) for
this when i use the following code:
Set rs = db.OpenRecordset("badpartsl")
intloop = 2
With objXLSheet
Do
.Cells(intLoop, 1) = rs!PartNum
.Cells(intLoop, 2) = rs!Desc
.Cells(intLoop, 3) = rs!month1????
.Cells(intLoop, 4) = rs!month2????
-------------------------------------
This prompts me to ask 3 questions:
1. How can I totally delete the table 1badparts_tbl so that I do not get the
error when I run the crosstable-make table query called "badparts"?
2. Is there a way to link to the actual field name in the created table
without knowing the name of it so that when I do the automation, it gets
picked up in the correct order?
3. Is there a way to also add to the table all of the months with zero and
only populate the one that have a quantity?
I have tried using "PIVOT Format([Date],"mmm") In
"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
but I would really like to have this display as a 12 month rolling average
where the current month is the first shown... Is there a way to do this?
thanks
Jeff
:
Jeff,
What you can do is write another query based on the cross tab query, in
the QBE. In query window, select new-> in the query tab, find your
cross tab query, select the * for everything, then on the view menu
select SQL view.
Your new query should like something like this:
SELECT * FROM MyCrossTabQuery
Add this:
SELECT *
INTO tblCrossTabQry
FROM MyCrossTabQuery
The above syntax will create a new table based on your cross tab query.
HTH,
Ben
On 2/18/2009 1:45 PM, Jeff wrote:
Thanks Ben-
That is what I thought and is why I need to come up with a way to dump my
crosstab query to excel with automoations (in order to include what I have
already loaded on the sheet 1 work sheet...
If there was a way to dump a crosstab query iunto a table, then I would know
how to do this but I have not figured out how to load excel from a crosstab
query using automation...
the only other option might be to do my tranferSpreadSheet on Worksheet 1&
2 and then use automation to load the third worksheet, but not sure how to do
that either...
:
Jeff,
Transfer spreadsheet, to my knowledge, creates a new file everytime. If
you look at its syntax from online help:
expression.TransferSpreadsheet(TransferType, SpreadsheetType,TableName,
FileName, HasFieldNames, Range, UseOA)
It only allows for FileName, not separate worksheet.
Ben
On 2/18/2009 1:24 PM, Jeff wrote:
Thanks Ben-
Not sure what you are suggesting as I am having a hard time reading what you
posted... for some reason my browser or this forum does not support HTML...
Can you repost your message in plain text please?
thanks
:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Jeff,<br>
<br>
Transfer spreadsheet, to my knowledge, creates a new file everytime.
If you look at its syntax from online help:<br>
<i><font size="3" face="Times New Roman"><span
style="font-size: 12pt; font-style: italic;"><br>
expression</span></font></i>.<b><span style="font-weight: bold;">TransferSpreadsheet</span></b>(<b><i><span
style="font-weight: bold; font-style: italic;">TransferType</span></i></b>,
<b><i><span style="font-weight: bold; font-style: italic;">SpreadsheetType</span></i></b>,
<b><i><span style="font-weight: bold; font-style: italic;">TableName</span></i></b>,
<b><i><span style="font-weight: bold; font-style: italic;">FileName</span></i></b>,
<b><i><span style="font-weight: bold; font-style: italic;">HasFieldNames</span></i></b>,
<b><i><span style="font-weight: bold; font-style: italic;">Range</span></i></b>,
<b><i><span style="font-weight: bold; font-style: italic;">UseOA</span></i></b>)<font
size="2" face="Arial"><span
style="font-size: 10pt; font-family: Arial;"><o
></o
></span></font><br>
<br>
It only allows for FileName.<br>
<br>
Ben<br>
<br>
<br>
On 2/18/2009 11:59 AM, Jeff wrote:
<blockquote
cite="mid:
[email protected]"
type="cite">
<pre wrap="">Thanks for the advice..
I have used the TransferSpreadSheet function before by using a table as an
input but I am not sure how to incorporate it with the spreadsheets that I
already have open which I am creating charts with automation...
An example of the function that I used:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTemp, "C:\"
& strFileName& ".xls", True, "MySheet2"
In this case would strTemp equal the cross tab query?
Also, I have expereienced the TransferSpreadSheet function to overwrite a
file and I would be OK using this if I can select page 2 of my spreadsheet
(as page1 is already populated)
Where can I find out how to do that?
thanks
:
</pre>
<blockquote type="cite">
<pre wrap="">-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
You don't have to use Automation; you can use the TransferSpreadsheet
method of the DoCmd object. You can export the cross-tab query's data
to the spreadsheet. Read the VBA Help article TransferSpreadsheet
Method for more info (hint: type DoCmd.TransferSpreadsheet in the Debug
window [Ctrl-G]; place the cursor on the word TransferSpreadsheet and
hit the F1 key - the Help article will appear).
--
MGFoster:::mgf00<at> earthlink<decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBSZsu4YechKqOuFEgEQJDpwCdEw3Q2anwAShIGczegVQxV9WaP6IAoPXb
BdoM+dR9DlE2gtLvfkNY7Xki
=J8mI
-----END PGP SIGNATURE-----
Jeff wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Is there an easy way of using automation to load a cross tab query to Excel?
I have a cross tab query with the following SQL:
PARAMETERS [forms]![MyForm]![Start] DateTime, [forms]![MyForm]![End]
DateTime, [Forms]![MyForm]![Nactteam] Text ( 255 );
TRANSFORM Count([Team_tbl].[PartNum]) AS [CountOfPartNum]
SELECT [FPY Table].[PartNum], ONEBPCSF_IIM.IDESC AS [Desc]
FROM ([FPY Table] LEFT JOIN [Team_tbl] ON [FPY Table].[AsmNum] =
[Team_tbl].[PartNum]) LEFT JOIN ONEBPCSF_IIM ON [Team_tbl].[PartNum] =
ONEBPCSF_IIM.IPROD
WHERE ((([FPY Table].[Fail Quantity])=1) AND (([FPY Table].Date) Between
[forms]![MyForm]![Start] And [forms]![MyForm]![End]) AND
(([Team_tbl].[Nact-team])=[Forms]![MyForm]![Nactteam]))
GROUP BY [FPY Table].[PartNum], ONEBPCSF_IIM.IDESC, [Team_tbl].[Nact-team]
ORDER BY [FPY Table].[PartNum]
PIVOT Format([Date],"mmmyy");
