Export a Query in Spreadsheet Format

[jcbhydro]

Good Morning,

I have a Query derived from a single Table in Access 2010 which I routinely export in spreadsheet format.
I have now built a report from the Query in which has labels have been added to suit an external agency. I wish to export this Report in Excel format, but the resulting file does not include the additional labels and is merely an xls version of the underlying query.

Is there an alternative method of achieving what I need?

Any suggestions would be gratefully received.

Regards,

jcbhydro

 

[Gasman]

Create an update table query to a table and export that. The table already having the correct labels/Excel headers?

Specify the labels in the query.... Date() as "Test Date"

I'd probably use the second option.

HTH

 

[jcbhydro]

Many thanks for the suggestions.

I would certainly not be very keen to build yet another table for this purpose.
I could of course include the 6 labels as dummy fields in the existing table, but that would be a rather inelegant solution.

I confess that I don't understand your 2nd option. My understanding of Query structure is that the columns in Design View are derived from Tables & Fields.
Where/How does one incorporate your example; Date() = "Test Date".

jcbhydro

 

[Gasman]

In design view you would prefix the field with <name:> without the <>.
Or in SQL view add AS <name> to each field.

This is what is constructed by prefixing the field On_Date with my date: in design view

SELECT Dates.On_Date AS [my date]
FROM Dates;

In SQL view I would just add the AS <name>. You only need the [] if the name has spaces.

HTH

 

[jcbhydro]

Dear HtH,

Many thanks for your further suggestions.

With my limited experience of SQL coding I am still rather confused as to how to turn an <Expr> into a label.

SELECT [Name]="Title" AS Expr1, [Name]="Initial" AS Expr2, [Mail List].[Member ID], [Mail List].[First Name], [Mail List].[Address 1], [Mail List].[Address 2], [Mail List].Town, [Mail List].PostCode, [Mail List].Surname
FROM [Mail List]
WHERE ((([Mail List].[Third Age Matters])=True));


This is the SQL code I have tried for my extremely ssimple query, but this produces column headings of <Expr1> & <Expre2> not 'title; & 'Initial' as required. I am obviously misinterpreting your suggestions.

regards,

jcbhydro

 

[Gasman]

Firstly HTH is an acronym for Hope This Helps My name is actually Paul.

You appear to be using the same field for different headings?

I've guessed what you are trying to achieve, so see if you can work from this.

You can paste this to the SQL window and then amend there or in the design view.

SELECT [Mail List].[Name] AS Surname, [Mail List].[Initial] AS Initial, [Mail List].[Member ID] AS Member, [Mail List].[First Name], [Mail List].[Address 1], [Mail List].[Address 2], [Mail List].Town, [Mail List].PostCode, [Mail List].Surname
FROM [Mail List]
WHERE ((([Mail List].[Third Age Matters])=True));

Effectively in design view prefix the field name with your name and a colon :

So in design view you would see
Surname:Name

Here is a screen dump of me changing the field On_Date to My Date

 

[jcbhydro]

Thank you Paul,

With your guidance I believe that I have at last got some valid coding in place to achieve what I require in the way of new & modified column titles.

The SQL Code now reads;
SELECT [Name1] AS Title, [Name2] AS Initial, [Mail List].[First Name] AS Forename, [Mail List].Surname, [Mail List].[Address 1], [Mail List].[Address 2], [Mail List].Town AS Address3, [Name4] AS Address4, [:Name5] AS Address5, [Name6] AS Address6, [Mail List].PostCode, [Name7] AS [U3A Name]
FROM [Mail List]
WHERE ((([Mail List].[Third Age Matters])=True));


My final problem is to nominate parameter values to prevent the dreaded 'enter parameter' message appearing for each of the 'column names' which I have generated, when I call up a datasheet. I wish to leave all except the final parameter blank. The final parameter needs to include some text common to all records.

Your further assisstance would be much appreciated.

jcbhydro

 

[Gasman]

AFAIK you only get the parameter dialogue form when the field does not exist in the source of the data, so unsure as to why you are getting the parameter dialogue, unless it is for that last parameter, which will then be repeated for all records.

Can you post a cut down version of your db with the components in question?. I only have 2003, so it would have to be in mdb format unless someone else can help.

 

[jcbhydro]

Paul,

You have been extremely patient in your assistance to me.

I'm afraid that an attempt to 'Save as' an mdb fail produces a not possible message, due to 2010 features.

However, I have decided that i can live with the 'Parameter dialogue' boxes. Pressing <OK> a few times isn't an elegant solution but it works. I need the last parameter anyway as that is where i need to input some text which is common to all records.

Thank you again for your kind assistance.

jcbhydro

 

[Gasman]

Don't give up, someone else who has 2007 will be able to help. You could also try exporting only what is required and select a mdb file type?

Post the 2007 version anyway. That way someone else has the chance to help.