one record 10 fields to ten rcords 1 field

[New Ripper]

I have a table with one record containing 10 fields.
I want to copy this information to a table containing 10 records and one
field.
I need to do this every time I change a field value in the single record.
The reason is that a combo box is linked to the 10 record/one field table
but a subform is linked to the one record/10 field table and I can't think of
a way of making both links to the same table and achieving the same results.

 

[New Ripper]

Hi Jeff
You're probably right!
It all started because I needed to have 10 text boxes to describe 10 prices.
The prices and their description (text) are both variable and are used in a
billing program. So customer had so many "big boxes" @ 25p and so many
"little boxes" at 15p. etc. This is a warehouse storage system. Each customer
could have a different price for the same type of box! That's no problem from
the database viewpoint and I've dealt with this. The box descriptions/type
are global over all customers in the database.
When a customer sends in a new box for storing, the database is updated with
the box information, including identity,etc, and the type of box, either "big
box" or "little box"etc. Ideally a dropdown combo box would be the method to
select from a choice of 10 descriptions. This is the problem. How do I
populate the combobox? I need a table.
At this stage we don't know the description/type of all 10 boxes and so far
only 4 are named but the user is supposed to be able to add new types as they
arise.
Obviously I could do this at the programming level but that is a bit too
naive, although this might be my only answer!!! But if I could populate the
10 record/1 field table from the 1 record/10 field table I'd be a happy bunny!
Roger

 

[Jeff Boyce]

There's a chance you are working with a data structure/design that better
fits a spreadsheet than a relational database. If you'll provide a more
specific description of the actual data, folks here may be able to offer
more specific suggestions.

It all starts with the data!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I have a table with one record containing 10 fields.
I want to copy this information to a table containing 10 records and one
field.
I need to do this every time I change a field value in the single record.
The reason is that a combo box is linked to the 10 record/one field table
but a subform is linked to the one record/10 field table and I can't think of
a way of making both links to the same table and achieving the same

results.

 

[Pete D.]

You need to, as Jeff says look at the data. I'm sure there is some
standard, by inch, foot, pound, each or whatever. Yes it can get
complicated but you must have a standard you use for billing. Now what is
it? You can have a % for pieces, that might be one table, a % for weight
and one for size. If you provide details I'm sure someone here can come up
with tables and relations.
Pete D.

 

[John W. Vinson]

Hi Jeff
You're probably right!
It all started because I needed to have 10 text boxes to describe 10 prices.

Sure. But there's nothing that says that those ten textboxes must be bound to
ten fields in one record - and there are many good reasons why they should NOT
be!

"Fields are expensive, records are cheap". Suppose you need an *eleventh and
twelfth* value someday? What do you do - redesign your table, rebuild all your
queries and all your forms? Ouch!

The prices and their description (text) are both variable and are used in a
billing program. So customer had so many "big boxes" @ 25p and so many
"little boxes" at 15p. etc. This is a warehouse storage system. Each customer
could have a different price for the same type of box!

One table with three (or probably more) fields: BoxID; CustomerID; Price. Each
customer can have as many different kinds of boxes as they need, and each box
can have a price that's specific to that customer; with no limit whatsoever to
the number of boxes or the number of customers.

That's no problem from
the database viewpoint and I've dealt with this. The box descriptions/type
are global over all customers in the database.

Of course - a Box table, primary key BoxID, related one to many to the prices
table.

When a customer sends in a new box for storing, the database is updated with
the box information, including identity,etc, and the type of box, either "big
box" or "little box"etc. Ideally a dropdown combo box would be the method to
select from a choice of 10 descriptions. This is the problem. How do I
populate the combobox? I need a table.

Ummm... sure. The Boxes table.

At this stage we don't know the description/type of all 10 boxes and so far
only 4 are named but the user is supposed to be able to add new types as they
arise.

Sure; use a Form based on the boxes table, allowing any number of new boxes to
be built.

Obviously I could do this at the programming level but that is a bit too
naive, although this might be my only answer!!! But if I could populate the
10 record/1 field table from the 1 record/10 field table I'd be a happy bunny!
Roger

The one record/ten field table *SHOULD SIMPLY NOT EXIST*.

 

[New Ripper]

Sure. But there's nothing that says that those ten textboxes must be bound to
ten fields in one record - and there are many good reasons why they should NOT
be!

"Fields are expensive, records are cheap". Suppose you need an *eleventh and
twelfth* value someday? What do you do - redesign your table, rebuild all your
queries and all your forms? Ouch!


One table with three (or probably more) fields: BoxID; CustomerID; Price. Each
customer can have as many different kinds of boxes as they need, and each box
can have a price that's specific to that customer; with no limit whatsoever to
the number of boxes or the number of customers.


Of course - a Box table, primary key BoxID, related one to many to the prices
table.


Ummm... sure. The Boxes table.


Sure; use a Form based on the boxes table, allowing any number of new boxes to
be built.


The one record/ten field table *SHOULD SIMPLY NOT EXIST*.

I'm OK on the prices as each customer has his own price table so it was only
the global descriptions to link to those tables but I will follow the general
concensus.
Thanks for your input, I'm sure I'll be back for more as new demands uncover
bad programming-

 

[John W. Vinson]

I'm OK on the prices as each customer has his own price table

Sorry. Wrong again!

Storing data (a customer's identity) in a tablename is EVEN WORSE than storing
it in a fieldname.

You're using a relational database, not a spreadsheet! Use it relationally! If
the Price depends jointly on the identity of a box and the identity of a
customer, you need *one* price table with three fields - BoxID, CustID, Price.

so it was only
the global descriptions to link to those tables but I will follow the general
concensus.

I'm really not sure what you're saying here.

Thanks for your input, I'm sure I'll be back for more as new demands uncover
bad programming-

Do read up about normalization. "When you find yourself in a deep hole, the
first thing is to stop digging"...