Concatenate a field name 
Author Message
 Concatenate a field name

I have a table of clients

fields are  "month" "client 1" "client 2"....."client 20"

the values are sales figures for each client in a given
month

In a query I want to have a field which will instead of
having  [Clients]![Client 1]

would be a concatenate function of [Clients]![Client X]
whith "X" being the number of a client pulled out of a
form or record in a table.

How can I concatenate the field name ....

not sure in access, but in excel it would be

Field =concatenate("[Clients]!,"X") where X would be a
reference to another field or value.



Mon, 24 Oct 2005 06:17:02 GMT  
 Concatenate a field name


Quote:
> I have a table of clients

> fields are  "month" "client 1" "client 2"....."client 20"

> the values are sales figures for each client in a given
> month

This is a bad design and your table sholud have fields Month, Client ID, and
SalesFigures or something of meaning.
If you don't do this you will continue to run into problems that will
require hand coding. If you do do it most of your problems will be solved
with simple queries and report designs.

Look up Fields Collection in help:

me.Fields("client" & chrClientNum) where chrclientNum = 0, 1,2 etc works.



Mon, 24 Oct 2005 06:59:43 GMT  
 Concatenate a field name


Quote:
>I have a table of clients

>fields are  "month" "client 1" "client 2"....."client 20"

Then you have an INCORRECTLY DESIGNED TABLE. This is "spreadsheet
thinking"!

What if you were to get a 21st client? What if Client 1 in one record
is the same person as Client 6 in a different record?

You should model a one to many relationship *as a one to many
relationship* - the table should be tall-skinny, with *three fields*:

MonthNo (Month is a reserved word, don't use it as a field name)
ClientID (link to the Client table)
Sales  (sales during this month for this client)

This would let you easily use a Query with two criteria, one for the
client and one for the month.

Quote:
>the values are sales figures for each client in a given
>month

>In a query I want to have a field which will instead of
>having  [Clients]![Client 1]

>would be a concatenate function of [Clients]![Client X]
>whith "X" being the number of a client pulled out of a
>form or record in a table.

>How can I concatenate the field name ....

>not sure in access, but in excel it would be

>Field =concatenate("[Clients]!,"X") where X would be a
>reference to another field or value.

Excel is a spreadsheet, a good one. Access is a relational database.
THEY ARE DIFFERENT. A database is *NOT* a big spreadsheet; it is a
different kind of program with different logic and different
conventions.

There are *some* areas where the programs overlap, but don't be misled
by them: you can drive nails with a crescent wrench, but that doesn't
make it a hammer!

                  John W. Vinson[MVP]    
    Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public



Mon, 24 Oct 2005 08:59:55 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Concatenating field name

2. Having trouble accessing single field key with two concatenated fields

3. Concatenating a Text Field and a Number Field

4. Concatenated Variable Names

5. Concatenating variable names

6. applying ital to selected fields within a concatenated textbox

7. Concatenating Fields in sql-string in vba

8. Trouble concatenating multiple rows from one field

9. Bolding text in concatenated fields

10. Concatenate two fields to one usable variable

11. Custom fields: concatenating project & task strings

12. Problem Opening Recordset with two fields concatenated As

 

 
Powered by phpBB® Forum Software