Polishing table for export

[Scaffold]

Greetings!



I need some ideas because I am new to Access. I am currently prepairing a table which I intend to export to Excel format and use in the engineering software. With some pain I was able to finally organize a query which extracts data in the way I need. Only two problem remain.

1) After values in some of the columns I need to add "mm", becase it is the way it will be consumed later.

2) I need to rename columns to match the requirements of the engineering software. I cannot rename them in the quiery, or maybe do not know how.

Of course, all of that can be made in Excel after the export. But I don't think it is the best way, because if I make some changes (fix some bugs, for instance), I will have to repeat formatting over and over again. I'd like to have the table 100% ready in Access.

Can you please advice me?

[DaveT]

#2. Using the Access query design grid, you can alias (rename) field names. First build the query normally. Then go into the field grid and type new name (alias) colon and real field name.

For example if your query has a field name FIELD1, you can enter My New Field Name: FIELD1

When the query runs, the column name will show as My New Field Name

#1 Using the same procedure as #2 above, you can alais a field name to add "mm", for eample:

Field1mm : Field1

[Scaffold]

Hello DaveT!

Many thanks for the solution on the #2.

About #1 I am not sure. I need mm added after values, rather than field names. Basically, I need 1, 2, 3 etc. to turn to 1 mm, 2 mm, 3 mm etc. Can that be done?

[DaveT]

Gotcha. To append mm to each field value then I would do something like:

Alias field name: CStr([Data Field Name]) & "mm"

Do not make Alias the same as Data Field Name 'cause that will give you a circular reference.

If you want a space before mm, then use ... & " mm"

If the data field can be null (has no value), then you have to decide what you want outputted because you can end with stuff like 1 mm, 2 mm, mm, 4 mm, ...

Then you might use something like:

Alias: IIF( Len(Nz(Data Field Name])) > 0, CStr([Data Field Name]) & "mm", "")

or you can use

Alias: IIF( Len(Nz(Data Field Name])) > 0, CStr([Data Field Name]) & "mm", Null)

[Scaffold]

As I understood, I have to create extra field for this trick to work? It works that way for me. Thank you so much!

One more question. I got it that in the design grid of a query the order in which the columns are located controls the order of sorting, with the left one taking precedence over right one, and so on. Right? But I need columns to lie in the different order in the resulting table. I was able to drag columns in the table view to the desired order, but after some of the manipulations in the design grid I had them reset to the default locations. Also, when I export table to Excel, the columns are also reset to the default (SQL) locations, unless I push option to export layout as well. Is it the way it works really, or can the columns be assigned their locations in the table view explicitly?

Again, thank you very much!

[DaveT]

Followup note on the alias stuff: If your query has more than one table, sometimes you have to use the table name and field name such as:

Alias : [table1].[field1]

instead of just

Alias : [field1]

=====================
When designing a query, put the fields into the grid in the order (left to right) that you want for output.

After that, you set the sort order (left to right). Sorts must be explicitly set (the sort row in the query design).

Note: For a single table with no sorts set, the query will order itself to the primarykey, but as a matter of practice it is best to explicitly set the sort order in every query.

Now, if the output fields (left to right) do not match the multi-field sort order you need, no problem. Go to the far right of your query design and pull down the fields you want to sort on, in the order you need (such as LastName, FirstName, etc.).

Uncheck the show box for these fields. Go ahead and set the Sort for each of these fields.

Run and save the query.

Note that the next time you open the query, Access may have altered the design slightly. Not to worry, the query will run the way you designed it.

[Scaffold]

Hello again Dave! Thanks for your outstanding coaching I am almost there!
Only one problem remains. I have a table which has two entries 'wide' and 'narrow'. The key field is counter column though (I am yet lame at relations and can't sometimes establish a proper link between tables). In the query table I see the values in the column as 'wide' and 'narrow', which is what it supposed to be like. But when I export it to Excel, they are replaced with '1' and '2', which is actually values of the key counter in the source table. All other coulmns generate content propely. How can that be fixed?