Crosstab Behaviour required for Data Input

[vectorBS]

vectorBS,
Could you email me a sanitized (remove any sensitive data) version of your db? You can obtain my email address by downloading a vCard from my profile.

I have done as requested.

[puppydogbuddy]

I have done as requested.

Took a quick look at your db....there are no forms. Where is the form that you had using the old layout? I need it so that I can use it to reformat the layout.

[vectorBS]

I have sent it again.

[vectorBS]

Hi FishVal,

You mentioned spreadsheet control. Do you think that flexi control will be just as good? Could you please run me through how i would implement these controls for satisfying my purpose?

[puppydogbuddy]

I have sent it again.

Ok, after looking at your db with the form (nicely done), you have the options I have listed below.

I see now why you were having difficulty with reworking the form layout. what I did not realize was that you were using a datasheet view, and did not fully understand the structure of your db until I had a chance to look at it myself. Nonetheless, I think I came up with a good solution for you (see #1 below) if your main concern is with the display format for printing/viewing of the customer order.

1. Continue with the layout as you have it for input/editing purposes, but overlay it over a crosstab view which will remain invisible until it is toggled on via checkbox. Whenever crosstab is on, the input view is off (not visible). You would then be able to display crosstab view as the order detail format in the final order. This will enable you to keep the database design intact.

2. Change database table design to have a separate sizing table that will enable you to have many sizes for each OrderDetail line....and rework the layout of your form accordingly.

3. FishVal's suggestion (maybe, but you will have to work with him on that idea)

Let me know what you think.

[FishVal]

Hi FishVal,

You mentioned spreadsheet control. Do you think that flexi control will be just as good? Could you please run me through how i would implement these controls for satisfying my purpose?

Hi.

Let me clarify what I've mentioned.

Each row in crosstab query corresponds to several records in actual table. An idea to use spreadsheet (and I think flexi would be satisfactory enough) is to require crosstab values to be viewed/edited together with corresponding PKs storing them in hidden columns. Then on each cell changed update back table. This just an idea but it seems worth implementation.

But, I state it once more just bcz I think it was not enough clear (nevermind if it was), it seems to me acceptable to implement the kind of interface I've suggested in the previous post. I mean adding updateble subform containing records from crosstab query row being in focus or use the same subform popping up when, for example, crosstab row doubleclicked. The main idea here is to separate view and edit to different controls.

P.S. Anyway I'll take a try with flexi and spreadsheet controls. Maybe I'll use this in my dbs.

[vectorBS]

Hi puppydogbuddy,
Thanks for the comments and having a look. I am glad you agree with me now. Regarding your points

1. No the data input needs to be in a horizontal manner that was the whole point of me creating this thread. I have got reports for viewing the data which are via crosstab query.
2. I am really tempted. But this will de normalize my DB and its un professional :o( I am keeping this as my last option.

Hi FishVal,
Can you forward me some resources on this and in your opinion how long will it take you to create a form like that from scratch. I need to estimate the time and cost because the deadline is very near. Cheers

[FishVal]

Hi puppydogbuddy,
Thanks for the comments and having a look. I am glad you agree with me now. Regarding your points

1. No the data input needs to be in a horizontal manner that was the whole point of me creating this thread. I have got reports for viewing the data which are via crosstab query.
2. I am really tempted. But this will de normalize my DB and its un professional :o( I am keeping this as my last option.

Hi FishVal,
Can you forward me some resources on this and in your opinion how long will it take you to create a form like that from scratch. I need to estimate the time and cost because the deadline is very near. Cheers

Hi, vectorBS

I've finally implemented this via OWC11.Spreadshe et.11. The code is not very nice so far, but it works. It performs nice with 30x5 records, but with large number of records the control may be populating slowly.
If you want to give this a try, then let me know and post your table(s) full metadata to let me see how it can be implemented in your case.

P.S. And finally, concerning your crosstab query. I hope Sum function there is not for summing several records but to represent one.

[vectorBS]

Hi, vectorBS

I've finally implemented this via OWC11.Spreadshe et.11. The code is not very nice so far, but it works. It performs nice with 30x5 records, but with large number of records the control may be populating slowly.
If you want to give this a try, then let me know and post your table(s) full metadata to let me see how it can be implemented in your case.

P.S. And finally, concerning your crosstab query. I hope Sum function there is not for summing several records but to represent one.

Nice one!! Hopefully you shall set a precedent. Can i send you the db I think it will be more convineant for you.

[FishVal]

Nice one!! Hopefully you shall set a precedent. Can i send you the db I think it will be more convineant for you.

Sure. My vCard is open to download.

[vectorBS]

Sure. My vCard is open to download.

Have sent the DB via email

[vectorBS]

Hi, vectorBS

I've finally implemented this via OWC11.Spreadshe et.11. The code is not very nice so far, but it works. It performs nice with 30x5 records, but with large number of records the control may be populating slowly.
If you want to give this a try, then let me know and post your table(s) full metadata to let me see how it can be implemented in your case.

P.S. And finally, concerning your crosstab query. I hope Sum function there is not for summing several records but to represent one.

Hi FishVal,
You must be wondering where have i disappeared. Actually i had to de normalize the db and make it to the hand off. I saw your form and i must say its a very good effort. It works nicely but the flaw is that i cant add rows which means that i am not able to add more stock or a new line of order.

[chuckbo]

Hi, vectorBS

I've finally implemented this via OWC11.Spreadshe et.11. The code is not very nice so far, but it works. It performs nice with 30x5 records, but with large number of records the control may be populating slowly.
If you want to give this a try, then let me know and post your table(s) full metadata to let me see how it can be implemented in your case.

P.S. And finally, concerning your crosstab query. I hope Sum function there is not for summing several records but to represent one.

FishVal, VectorBS --

can I please get a copy of the code you developed? I'm about to start a similar project except that my users need to input forecasted prices, 1 per year, that are stored in different records. I'm also using the OWC spreadsheet control, except that we're still on Office 2000 and I'll have to use OWC.Spreadsheet .9. That means I need a code dump and not a copy of the database. :(
Can either of you help, please?
Chuck

[FishVal]

FishVal, VectorBS --

can I please get a copy of the code you developed? I'm about to start a similar project except that my users need to input forecasted prices, 1 per year, that are stored in different records. I'm also using the OWC spreadsheet control, except that we're still on Office 2000 and I'll have to use OWC.Spreadsheet .9. That means I need a code dump and not a copy of the database. :(
Can either of you help, please?
Chuck

Hi, there.

Particular solution depends on tables structure. The code I've wrote for Vector differs greatly from that I'm using in my db. So I'd like to ask you to post your tables metadata (field names, field types, table relationships).

[chuckbo]

Hi, there.

Particular solution depends on tables structure. The code I've wrote for Vector differs greatly from that I'm using in my db. So I'd like to ask you to post your tables metadata (field names, field types, table relationships).

The data model is very simple -- it's just dealing with the Spreadsheet control that I think needs to be worked out. I started playing with it yesterday and made some progress. There are several requirements I can see how I would do them, but I'd have to experiment to find the appropriate commands and syntax to use. I appreciate being able to see what you've developed to help me cut out some development time!

I'm dealing with a Forecast_Price table.
PriceID is the primary key (Autonumber format).
Country and Scenario are text.
Year is an integer
Country/Scenario/Year is the unique, composite (logical) key.
Oil Price, Gas price, and Inflation adjustment are Single fields.
There's also a Prospect table that includes Country as a text field. Prices can only be entered for countries that appear in the Prospect table.

I envision users being able to edit one country's data at a time. Each scenario/Price combination will appear on its own row. I'd expect the first column to be scenario and the 2nd column to be one of the 3 prices. Columns 3 through n will represent a series of years. When I first create the table, I'm thinking of these steps:
1) determine the largest year of any record
2) build the header row
3) get the records for a specific country
4) populate the grid.
The headers can't be edited. I'm thinking that I'll need to have a button that adds a year to the existing table (which would, in effect, add that year to every country/scenario as it's edited).
All records are created/updated when the user presses the Save button.
Users may be pasting in their forecasts from another spreadsheet.