Cross Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to make a query or a form that let me input data in this form:

City_1 City_2 City_3 City_4
Product_1 $ $ $ $
Product_2 $ $ $ $
Product_3 $ $ $ $

Hope any body can help me!
 
What specific problems are you having? We can't see your tables or forms or
whatever.
 
I have 2 tables, one for Cities, One for Products and prices.
What I need is to have a way to fill in the prices by product and by city in
the format I outline before. If I have to change the design of the tables, I
can do that, because I am just starting.
What I wish is to fill in the price of each product in each city, all in the
same form.
Am I being clear?
 
This helps but it wouldn't have taken too long to provide some actual field
names and sample data. I assume you have done a good job of normalizing your
tables but want to do your data entry in a "spreadsheet" type view.
Crosstabs are not updateable so you will have to either enter in a different
format or use code to or other method to de-normalize and then re-normalize.
 
Let's say my tables are like this:

Table Cities:
ID_City
City_Name

Table Products
ID_Product
ID_City
Product_Name
Price

Any way, I just get a result like this:

City_name Product_Name Price
MED Prod_1 $
MAS Prod_2 $
BOS Prod_3 $

And what I want to get is:
MED BOS MIA
Product_1 $ $ $
Product_2 $ $ $
Product_3 $ $ $

wher $ can be an input by the user.

Thanks for all your help and your time.
 
Crosstabs are not updateable so you will have to either enter in a different
format or use code to or other method to de-normalize and then re-normalize.

Are you looking for code help? How many cities do you have? Does the number
of cities vary? Can you group your cities if you have too many? Would you be
satisfied with the spreadsheet appearance but needing to double-click an
entry to edit an individual value on a separate form?
 
Yes!! You understand me perfectly!
I will be satisfied with the spreadsheet appearance.
The number of cities vary. I have a table to group the cities.

I gave you an example with cities, but what I need is a form of working
things this way, because it will be easier for an user to fill in an
application.

Thanks a lot!
 
I only asked if you were "looking for code help" and didn't promise to do
this for you. This isn't simple code since the number of columns would vary.
You would need a flexible for as well as code the loops through records to
populate the form. You need additional code to write the values back to the
table after updating.

I don't have time to do all of this and I'm not optimistic that others will
take the do it as volunteers. Hopefully someone has a link to a similar
solution.
 
Dear Lina:

We have produced such forms several times. To give you some idea, the
variable number of City columns will have to be handled using indexing
of the column collection. You should use a horizontal scroll bar
control, and program it to move through the set of variable columns
while keeping the Product column static.

There will need to be a temporary table behind the form and complex
update/insert queries to update back to the Products table. You must
also have a separate Products table, with ID_Product and Product_Name
in it. You shouldn't have Product_Name in the junction table.

Finally, if there is any chance this will be used in a multi-user
system, you will need to keep the previous values of all intersections
so you can tell which were changed. Then you will need to check to
see if any other user may have changed what you have changed, given
the previous value, the current value, and the new value. A conflict
resolution must be built.

This can be anywhere from 8 hours work to build (if no multi-user
features are implemented) to about 20 hours with full conflict
resolution. At going rates, it could easily exceed $1000. It would
take much more time and money than that to teach how this is done.
Probably you can tell why no one is likely to volunteer this much.
However, for an expert programmer, the above outline would be enough
to get started.

I guess someone should write a couple of chapers in a book on how to
do this. I haven't seen that done yet.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Thanks a lot!

Tom Ellison said:
Dear Lina:

We have produced such forms several times. To give you some idea, the
variable number of City columns will have to be handled using indexing
of the column collection. You should use a horizontal scroll bar
control, and program it to move through the set of variable columns
while keeping the Product column static.

There will need to be a temporary table behind the form and complex
update/insert queries to update back to the Products table. You must
also have a separate Products table, with ID_Product and Product_Name
in it. You shouldn't have Product_Name in the junction table.

Finally, if there is any chance this will be used in a multi-user
system, you will need to keep the previous values of all intersections
so you can tell which were changed. Then you will need to check to
see if any other user may have changed what you have changed, given
the previous value, the current value, and the new value. A conflict
resolution must be built.

This can be anywhere from 8 hours work to build (if no multi-user
features are implemented) to about 20 hours with full conflict
resolution. At going rates, it could easily exceed $1000. It would
take much more time and money than that to teach how this is done.
Probably you can tell why no one is likely to volunteer this much.
However, for an expert programmer, the above outline would be enough
to get started.

I guess someone should write a couple of chapers in a book on how to
do this. I haven't seen that done yet.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Click to expand...
 
Thanks a lot!

Duane Hookom said:
I only asked if you were "looking for code help" and didn't promise to do
this for you. This isn't simple code since the number of columns would vary.
You would need a flexible for as well as code the loops through records to
populate the form. You need additional code to write the values back to the
table after updating.

I don't have time to do all of this and I'm not optimistic that others will
take the do it as volunteers. Hopefully someone has a link to a similar
solution.
Click to expand...
 
Back
Top