Formatting a New Table query

[ellford]

Okay, I'm not sure if this is possible, so I'll describe the situation.

I have data in a text file (comma delimited) that I import into an access table. The data from the spreadsheet consists of numbers (a different number for each field). Together they form a unique number for the client.

A perfect example could be a telephone number where the data would be 999,888,7777 (as an example). The data then imports perfect to my first table into 3 seperate fields.

I then run a query to combine the numbers into a new table. This also works perfectly. The only problem is the data is missing key formatting (e.g. with the telephone number the formatting would be (999) 888-7777.

Now does anyone know how to make this happen at the query that creates the table (perhaps in a SQL statment)? I'd hate to have to force the user to run another query to format this appropriately if it can be helped.

My maketable SQL is as follows:

SELECT ac & ' ' & bc & ' ' & cc & ' ' & dc AS COMBINED INTO tbl_Authenticated_Numbers FROM tbl_Numbers;

...the ' ' is required to add in the appropriate spacing of the numbers. My big problem is the number identified at 'dc' needs to have a decimal place 3 points to the right (I can format the initial table to do this with a @@@@@\.@@@ in the format section but it doesn't migrate to the new table with this formatting).

Any help would be appreciated.

 

[lespaul]

why are you storing formatting info instead of just displaying it at runtime?


Leslie

 

[PHV]

Have you tried to play with the Format function in the SELECT clause of your make table query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ellford]

why are you storing formatting info instead of just displaying it at runtime"

I don't get you?

I have 4 seperate numbers that alone mean nothing, but when combined form the unique identifier number. Thus the need to combine them.

"Have you tried to play with the Format function in the SELECT clause of your make table query ? "

This is where I'm kinda lost. Not really sure how to insert the formatting into the SQL statement.

:-(

 

[PHV]

Either:
SELECT Format(Val(ac & bc & cc & dc), 'your format here')
Or:
SELECT Format(ac, 'format1') & Format(bc, ....

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ellford]

So how exactly would this look as a SQL statement? I've tried it, but the Query bails on me each time, so I must have something incorrect.

I tried the following:

SELECT Format(Val(ac & bc & cc & dc), '@@ @@@ @@@@@.@@@ @')
AS COMBINED INTO tbl_Authenticated_Numbers
FROM tbl_Numbers

The final result needs to be a table with this unique number looking exactly like 11 222 33333.333 4

Currently all the fields in tbl_Numbers are defined as 'text' fields. Perhaps that's my issue here? I'm not really sure.

 

[PHV]

What are the ac,bc,cc and dc values giving this result (11 222 33333.333 4) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

My guess (ac="11", bc="222", cc="33333333" and dc="4")
SELECT Format(ac & bc & cc & dc, '@@ @@@ @@@@@.@@@ @') AS COMBINED
INTO tbl_Authenticated_Numbers
FROM tbl_Numbers

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ellford]

AWESOME, this is exactly the way it's supposed to look!!

Thanks,

 

[ellford]

Okay, 1 last thing with this. I've set up the relationship appropriately so this works great. The only way it works is for me to define the field COMBINED in the table tbl_Authenticated_Numbers as a primary key.

As I want to make all this seamless to the user, is there a way to define this as the primary key when this query is run?

I've done some digging and found 'some' stuff, but I can't get it work (I probably have it out of syntax). This is what I've been toying with:

SELECT Format(ac & bc & cc & dc, '@@ @@@ @@@@@.@@@ @') AS COMBINED INTO tbl_Authenticated_Numbers (COMBINED CHAR(18) NOT NULL PRIMARY KEY)
FROM tbl_Numbers

Unfortunately it's not happy. Any ideas?

 

[ellford]

I think I've once again solved my own issue. I ran into a mess of problems, as this make table query would die on me due to the relationship I established with another table.

As such, I just kept the table and relationships and primary key that I had initially created. The user can delete the previous years records and run an append query that dumps in the information I needed, thus maintaining the relationships and primary keys, etc.

Here's the code I used:

INSERT INTO tbl_Authenticated_Numbers
SELECT Format(ac & bc & cc & dc,'@@ @@@ @@@@@.@@@ @') AS COMBINED
FROM tbl_Numbers;