strange query question

[Guest]

Hi,
I have a table that has a list of ten accounts. Field 1 has the account name
as well as all the client account numbers. e.g
Field 1 Field 2
Account Name: Flexible accounts
Client account # 1 $122
Client account # 2 $2
Client account # 3 $199
Account Name: Floating accounts
Client account # 1 $46
Client account # 2 $36
Client account # 4 $1
Client account # 5 $77
I would like to separate all the account names with the immediately below
client account numbers. Is this possible?
Is there a query that can be written that looks for the account name and
then grabs all the client numbers below until it reaches the next account
name?
Thanks
George

 

[Joseph Meehan]

Hi,
I have a table that has a list of ten accounts. Field 1 has the
account name as well as all the client account numbers. e.g
Field 1 Field 2
Account Name: Flexible accounts
Client account # 1 $122
Client account # 2 $2
Client account # 3 $199
Account Name: Floating accounts
Client account # 1 $46
Client account # 2 $36
Client account # 4 $1
Client account # 5 $77
I would like to separate all the account names with the immediately
below client account numbers. Is this possible?
Is there a query that can be written that looks for the account name
and then grabs all the client numbers below until it reaches the next
account name?
Thanks
George

So do you want:

Flexible accounts Client account # 1 $122
Flexible accounts Client account # 2 $2
Flexible accounts Client account # 3 $199
Floating accounts Client account # 1 $46
Floating accounts Client account # 2 $36
Floating accounts Client account # 4 $1
Floating accounts Client account # 5 $77

What is your table design?

 

[Guest]

Hi Joseph,
That is exactly what i want. Account name in field1, client number in field2
and value in field3.
Thanks
George

 

[Joseph Meehan]

Hi Joseph,
That is exactly what i want. Account name in field1, client number in
field2 and value in field3.
Thanks
George

OK, first step is table design. What is your current table design?

I might also ask, how many records do you anticipate having total?

 

[Guest]

Hi Joseph,
Fileds 1 through to 17 are text type.
Total records approx 300.
Thanks
George

 

[Joseph Meehan]

Hi Joseph,
Fileds 1 through to 17 are text type.
Total records approx 300.
Thanks
George

I have thought about this one for a while and maybe someone else might
have an automated answer, but I think I would just do a lot of cut and paste
to get the data into the proper table structure. Actually if I was not
retired, I would have my data entry operators copy and paste.

You need an Account table listing just the accounts.

I am going to guess that each account may have floating and or flexible
accounts.

I would then have a ClientAccount table and either ID each account type
flex or float by name or more likely by a separate field (making then easier
to filter) maybe using a simple yes-no field assuming you are absolutely
sure someone will not come up with a semi floating field next month.

You would then have a one to many (Client to Account) relationship and
you will be able to display them as you wish and work with them many other
ways as well.

 

[Guest]

Thanks Joseph,
I was afraid I would have to get manually involved with this. Maybe there is
some code that could pull this off but i am not that advanced. Your help is
much appreciated.
Take care
George