473,320 Members | 1,691 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Crosstab Behaviour required for Data Input

I am facing a serious issue. The current data appears as follows and in entered in the same manner.



Garment Style Size Total Qty
UCPJ 1 32 12
UCPJ 1 30 55
UCT 1 S 25
UCT 1 L 100

User requirement is to change the data into the following format

Garment STYLE S/30 M/32 L/34 XL/36 XXL/38
UCPJ 1 55 12 0 0 0
UCT 1 25 0 100 0 0

Now this sort of thing is what crosstab query is designed for but the data entry needs to be in the same format. What options do it have to go about implementing it? My last resort will be to alter the DB Design. Do i create a form with everything coded which includes data retreival, manipulation and saving? or is there an alternative?
Jul 6 '07 #1
34 8331
puppydogbuddy
1,923 Expert 1GB
I am facing a serious issue. The current data appears as follows and in entered in the same manner.



Garment Style Size Total Qty
UCPJ 1 32 12
UCPJ 1 30 55
UCT 1 S 25
UCT 1 L 100

User requirement is to change the data into the following format

Garment STYLE S/30 M/32 L/34 XL/36 XXL/38
UCPJ 1 55 12 0 0 0
UCT 1 25 0 100 0 0

Now this sort of thing is what crosstab query is designed for but the data entry needs to be in the same format. What options do it have to go about implementing it? My last resort will be to alter the DB Design. Do i create a form with everything coded which includes data retreival, manipulation and saving? or is there an alternative?
I think all you need to do is create a crosstab query using the crosstab query wizard as discussed in the following link. You would use the detail of the query you currently have (with Qty instead of totalQty) as the "base" query or source query for the crosstab. In response to the wizard prompts, enter the following:

column header: Size
Value column: Qty
Row Headers: all other columns

http://www.databasedev.co.uk/crosstab_queries.html
Jul 6 '07 #2
Thanks for the reply but as i mentioned in my original post, i need the data to be entered in the same manner and crosstab queries are not editable. How do i go about doing that?
Jul 6 '07 #3
puppydogbuddy
1,923 Expert 1GB
Thanks for the reply but as i mentioned in my original post, i need the data to be entered in the same manner and crosstab queries are not editable. How do i go about doing that?
Don't you have a data entry form that you are presently using? Just place the form in design view, and position the controls as needed; then click view > change tab order. If you have one, you can use the wizard to create one. And you would continue to use the same data ssource the you used for the "base" query above. As discussed previously, the crosstab is built on top of the base query.
Jul 6 '07 #4
Don't you have a data entry form that you are presently using? Just place the form in design view, and position the controls as needed; then click view > change tab order. If you have one, you can use the wizard to create one. And you would continue to use the same data ssource the you used for the "base" query above. As discussed previously, the crosstab is built on top of the base query.
Sorry i dont understand. Are you saying that i will be able to create an editable form using corsstab query. I do have a data entry form which is not based on crosstab query and i have already tried creating one with crosstab but that doesnt allow editing as crosstab query in its nature is not editable.
Jul 6 '07 #5
puppydogbuddy
1,923 Expert 1GB
Sorry i dont understand. Are you saying that i will be able to create an editable form using corsstab query. I do have a data entry form which is not based on crosstab query and i have already tried creating one with crosstab but that doesnt allow editing as crosstab query in its nature is not editable.
You use your form for data input/inquiry, and use the editable query that you were using as the record source of the form. Just re-position the controls on your form to make it look like you want it.

You use a report for data output. Here you would use the crosstab query as the data source for the report. And you can make the report look exactly like you want. The query for a report doesn't need to be editable.
Jul 6 '07 #6
I dont think you understood my problem. The DB is designed to have records in the following format

Garment------Style----Size----------Total Qty
UCPJ----------1----------32-------------12
UCPJ----------1----------30-------------55
UCT------------1----------S--------------25
UCT------------1----------L--------------100

User requirement is to change the data input into the following format

Garment---STYLE-----S/30----M/32----L/34----XL/36----XXL/38
UCPJ-------1--------------55-------12--------0---------0----------0
UCT---------1--------------25--------0--------100------0----------0

This is because of the fact that the data input in the first instance is reduced by atleast 5 lines in the second instance.

I have pulled my hair out on this one. I have replicated corsstab behaviour through outer joins, unions, inner joins but all the queries become uneditable hence making the form uneditable.
Jul 9 '07 #7
puppydogbuddy
1,923 Expert 1GB
I dont think you understood my problem. The DB is designed to have records in the following format

Garment------Style----Size----------Total Qty
UCPJ----------1----------32-------------12
UCPJ----------1----------30-------------55
UCT------------1----------S--------------25
UCT------------1----------L--------------100

User requirement is to change the data input into the following format

Garment---STYLE-----S/30----M/32----L/34----XL/36----XXL/38
UCPJ-------1--------------55-------12--------0---------0----------0
UCT---------1--------------25--------0--------100------0----------0

This is because of the fact that the data input in the first instance is reduced by atleast 5 lines in the second instance.

I have pulled my hair out on this one. I have replicated corsstab behaviour through outer joins, unions, inner joins but all the queries become uneditable hence making the form uneditable.
You don't understand what I am trying to tell you. Crosstab queries are primarily used to facilitate a horizontal presentation (display) format for <<reports>>>, and do not involve a change to the internal structure of your tables. Even in your examples above, you get the same total quantities for each size whether you present them in traditional format vertically down the page or in crosstab format across the page.

That being the case, you should continue to use your original query for your form if you want your form to be editable, and use the crosstab for reports. Even though you use your original query for your form, you can still simulate the look and feel of a crosstab query by repositioning and eliminating some of the controls on the form to facilitate input in a horizontal fashion. Repositioning and changing the tab order of controls on the form can be done independant of the type of query used.
Jul 9 '07 #8
Ok if that is the case could you please facilitate me by giving me a small example. I am really stuck on this one. I have all the reports having crosstab queries as their source but forms are a pain and i dont blame the users for complaining about entering 5 rows of information for one record.

A sample form on a db will do. Cheers
Jul 9 '07 #9
puppydogbuddy
1,923 Expert 1GB
Ok if that is the case could you please facilitate me by giving me a small example. I am really stuck on this one. I have all the reports having crosstab queries as their source but forms are a pain and i dont blame the users for complaining about entering 5 rows of information for one record.

A sample form on a db will do. Cheers

you have already presented the new layout as shown below. To implement it, make a copy of your original form, then move the controls and change/delete the headings/labels, change tab order, etc until you achieve new layout as shown below:

Original Input Format
Garment------Style----Size----------Total Qty
UCPJ----------1----------32-------------12
UCPJ----------1----------30-------------55

New Horizontal Format For Form (Uses Same Query)
Garment---STYLE-----S/30----M/32----L/34----XL/36----XXL/38
UCPJ-------1--------------55-------12--------0---------0----------0
Jul 9 '07 #10
You have lost me there. I really cannot grasp your concept. I have tried as well and its simply not possible. Here is the crosstab query.

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum(Stock.StockTotalQuantity) AS SumOfStockTotalQuantity
  2. SELECT Stock.StockDate, Stock.StockNumber, Stock.GarmentNumber, Stock.StyleNumber, Stock.ColourCode, Stock.UnitPrice
  3. FROM Stock
  4. GROUP BY Stock.StockDate, Stock.StockNumber, Stock.GarmentNumber, Stock.StyleNumber, Stock.ColourCode, Stock.UnitPrice
  5. PIVOT Format(Stock.SizeID) In ("S","M","L","XL","XXL","30","32","34","36","38");
  6.  
Now how can you replicate by rearranging? I did give it a try. I removed the size column and replaced by S,M, etc and then performed Dlookup on their quantities. This slows the form considerably and makes the quantity un editable again......
Jul 9 '07 #11
puppydogbuddy
1,923 Expert 1GB
You have lost me there. I really cannot grasp your concept. I have tried as well and its simply not possible. Here is the crosstab query.

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum(Stock.StockTotalQuantity) AS SumOfStockTotalQuantity
  2. SELECT Stock.StockDate, Stock.StockNumber, Stock.GarmentNumber, Stock.StyleNumber, Stock.ColourCode, Stock.UnitPrice
  3. FROM Stock
  4. GROUP BY Stock.StockDate, Stock.StockNumber, Stock.GarmentNumber, Stock.StyleNumber, Stock.ColourCode, Stock.UnitPrice
  5. PIVOT Format(Stock.SizeID) In ("S","M","L","XL","XXL","30","32","34","36","38");
  6.  
Now how can you replicate by rearranging? I did give it a try. I removed the size column and replaced by S,M, etc and then performed Dlookup on their quantities. This slows the form considerably and makes the quantity un editable again......
You are still using the crosstab query above, even though I instructed you not to use it for your form. Also, do you know the difference between labels and controls (e.g. textboxes) and fields in a table? You should only be deleting and re-adding labels....and then just repositioning the controls consistent with the labels. The last step is to change the tab order of the controls consistent with the new layout. That will not make your query uneditable.
Jul 9 '07 #12
FishVal
2,653 Expert 2GB
You have lost me there. I really cannot grasp your concept. I have tried as well and its simply not possible. Here is the crosstab query.

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum(Stock.StockTotalQuantity) AS SumOfStockTotalQuantity
  2. SELECT Stock.StockDate, Stock.StockNumber, Stock.GarmentNumber, Stock.StyleNumber, Stock.ColourCode, Stock.UnitPrice
  3. FROM Stock
  4. GROUP BY Stock.StockDate, Stock.StockNumber, Stock.GarmentNumber, Stock.StyleNumber, Stock.ColourCode, Stock.UnitPrice
  5. PIVOT Format(Stock.SizeID) In ("S","M","L","XL","XXL","30","32","34","36","38");
  6.  
Now how can you replicate by rearranging? I did give it a try. I removed the size column and replaced by S,M, etc and then performed Dlookup on their quantities. This slows the form considerably and makes the quantity un editable again......
Hi, There.

The question here, to my mind, is what control is suitable to represent data the way you need. Access form is not suited for this task at all. This means the cost of implementation this via Access form will be big, I don't mention it is not possible at all but it will take too much and result may not be satisfactory in the terms of performance and reliability.
Particulary me would prefer using smthng like spreadsheet control, it will take a bit of coding as well but spreadsheet control by itself is better suited for task like this.
Let me know if this looks promissing for you.

BTW a simple temporary solution maybe using r/o form you have and updateble popup form or subform.
Jul 9 '07 #13
Hi FishVal,
Thanks for your comments. I will look at spreadsheet control and see what it can do. I agree with the cost bit as i have already tried it and i can see there will be a lot of coding and intense queries.
Popup seems a good short term solution but there are three different sections of the system which need data input in this way and i dont want to have them all like that and this popup approach will not work with editing data.

Hi puppydogbuddy
Thanks for your comments on my understanding of access controls. If i understood what you said till now is to have unbound text boxes with all the sizes and rest bound. Then you are saying what i mentioned to start with i will have to do all the data retreival, manipulation, saving myself. I wouldnt mind doing that in Java or VC but not VBA for access.

By the way i was reading through some similar cases on the web. What has struck me is that it is found by experts of any given application or dev area a very tedious job. They have always adviced to redesign DB rather than actually mess with creating this milestone form.

So in your opinion shall i de normalize my DB design from

Garment, style, colour, size, qty
to
Garment, style, colour, size1qty,size2qty,size3qty,size4qty,size5qty
Keeping in mind size1qty refers to S/30 and so on.
Jul 10 '07 #14
puppydogbuddy
1,923 Expert 1GB
Hi FishVal,
Thanks for your comments. I will look at spreadsheet control and see what it can do. I agree with the cost bit as i have already tried it and i can see there will be a lot of coding and intense queries.
Popup seems a good short term solution but there are three different sections of the system which need data input in this way and i dont want to have them all like that and this popup approach will not work with editing data.

Hi puppydogbuddy
Thanks for your comments on my understanding of access controls. If i understood what you said till now is to have unbound text boxes with all the sizes and rest bound. Then you are saying what i mentioned to start with i will have to do all the data retreival, manipulation, saving myself. I wouldnt mind doing that in Java or VC but not VBA for access.

By the way i was reading through some similar cases on the web. What has struck me is that it is found by experts of any given application or dev area a very tedious job. They have always adviced to redesign DB rather than actually mess with creating this milestone form.

So in your opinion shall i de normalize my DB design from

Garment, style, colour, size, qty
to
Garment, style, colour, size1qty,size2qty,size3qty,size4qty,size5qty
Keeping in mind size1qty refers to S/30 and so on.
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.
Jul 10 '07 #15
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.
Jul 10 '07 #16
puppydogbuddy
1,923 Expert 1GB
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.
Jul 10 '07 #17
I have sent it again.
Jul 10 '07 #18
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?
Jul 10 '07 #19
puppydogbuddy
1,923 Expert 1GB
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.
Jul 10 '07 #20
FishVal
2,653 Expert 2GB
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.
Jul 10 '07 #21
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
Jul 11 '07 #22
FishVal
2,653 Expert 2GB
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.Spreadsheet.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.
Jul 11 '07 #23
Hi, vectorBS

I've finally implemented this via OWC11.Spreadsheet.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.
Jul 12 '07 #24
FishVal
2,653 Expert 2GB
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.
Jul 12 '07 #25
Sure. My vCard is open to download.
Have sent the DB via email
Jul 12 '07 #26
Hi, vectorBS

I've finally implemented this via OWC11.Spreadsheet.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.
Jul 18 '07 #27
Hi, vectorBS

I've finally implemented this via OWC11.Spreadsheet.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
Aug 1 '07 #28
FishVal
2,653 Expert 2GB
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).
Aug 1 '07 #29
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.
Aug 2 '07 #30
FishVal
2,653 Expert 2GB
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.

Well. Below is described how I've implemented this in my db. This is a part of Lab inventory db where results of ICP testing (test for metal traces) are stored.

Tables.

tblICPElementNames
keyICPElementNameID Long, Autonumber, PK
txtElementName Text

tblICPResults
keyICPResultID Long, Autonumber, PK
keyICPSampleID FK(tblICPSamples)
keyICPElementNameID PK(tblICPElementNames)
txtContent Text

tblICPSamples
keyICPSampleID Long, Autonumber, PK
keyOrderedItemID FK(....)
lngSampleID Long
keyICPSmpSentSetsID FK(...)
keyICPSmpRcvdSetID FK(...)

Queries.

qryICPElementsNameAsc
SELECT tblICPElementNames.*
FROM tblICPElementNames
ORDER BY tblICPElementNames.txtElementName;

qryICPSampleDesc (join with multiple tables to obtain full material description)
SELECT "#" & tblICPSamples.lngSampleID & ", " & tblItemNames.txtItemPrimaryName & ", B: " & tblItemOrders.txtBatch & " (" & tblManufacturerNames.txtManufacturerName & ")" AS txtICPSampleDesc
FROM tblItemNames INNER JOIN (tblItemPQs INNER JOIN (tblManufacturerNames INNER JOIN (tblItemOrders INNER JOIN tblICPSamples ON tblItemOrders.keyOrderedItemID = tblICPSamples.keyOrderedItemID) ON tblManufacturerNames.keyManufacturerNameID = tblItemOrders.keyManufacturerNameID) ON tblItemPQs.keyItemPQID = tblItemOrders.keyItemPQID) ON tblItemNames.keyItemNameID = tblItemPQs.keyItemNameID
ORDER BY tblICPSamples.keyICPSampleID;

qryICPSampleIDs
SELECT tblICPSamples.keyICPSampleID
FROM tblICPSamples
ORDER BY tblICPSamples.keyICPSampleID;

Forms

frmICPResults

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Dim blnSaveToTable As Boolean
  3.  
  4. Private Sub Form_Load()
  5.  
  6.     Dim sprSS As OWC11.Spreadsheet
  7.     Dim RS As New ADODB.Recordset, rsICPElements As New ADODB.Recordset
  8.     Dim strSQL As String
  9.     Dim rngCursorX As OWC11.Range
  10.  
  11.     Set sprSS = Me.sprSpreadsheet.Object
  12.     Set rngCursorX = sprSS.Worksheets("Sheet1").Range("A1")
  13.  
  14.     blnSaveToTable = False
  15.     rsICPElements.Open "qryICPElementsNameAsc", CurrentProject.Connection, _
  16.         adOpenForwardOnly, adLockReadOnly
  17.  
  18.     With RS
  19.         .Open "qryICPSampleDesc", CurrentProject.Connection, _
  20.             adOpenForwardOnly, adLockReadOnly
  21.         PutRecordsetToSheet rngCursorX, RS, ""
  22.         .Close
  23.         .Open "qryICPSampleIDs", CurrentProject.Connection, _
  24.             adOpenForwardOnly, adLockReadOnly
  25.         PutRecordsetToSheet sprSS.Worksheets("Sheet2").Range(rngCursorX.Address), RS, ""
  26.         .Close
  27.     End With
  28.  
  29.     Set rngCursorX = rngCursorX.Offset(ColumnOffset:=1)
  30.  
  31.     While Not rsICPElements.EOF
  32.  
  33.         strSQL = "SELECT tblICPResults.txtContent FROM tblICPSamples" & _
  34.             " LEFT JOIN tblICPResults ON (tblICPSamples.keyICPSampleID" & _
  35.             " = tblICPResults.keyICPSampleID AND tblICPResults.keyICPElementNameID=" & _
  36.             rsICPElements![keyICPElementNameID] & _
  37.             ") ORDER BY tblICPSamples.keyICPSampleID;"
  38.         RS.Open strSQL, CurrentProject.Connection, _
  39.             adOpenForwardOnly, adLockReadOnly
  40.         PutRecordsetToSheet rngCursorX, RS, rsICPElements![txtElementName]
  41.         RS.Close
  42.  
  43.         strSQL = "SELECT tblICPResults.keyICPResultID FROM tblICPSamples" & _
  44.             " LEFT JOIN tblICPResults ON (tblICPSamples.keyICPSampleID" & _
  45.             " = tblICPResults.keyICPSampleID AND tblICPResults.keyICPElementNameID=" & _
  46.             rsICPElements![keyICPElementNameID] & _
  47.             ") ORDER BY tblICPSamples.keyICPSampleID;"
  48.         RS.Open strSQL, CurrentProject.Connection, _
  49.             adOpenForwardOnly, adLockReadOnly
  50.         PutRecordsetToSheet sprSS.Worksheets("Sheet2").Range(rngCursorX.Address), _
  51.             RS, rsICPElements![keyICPElementNameID]
  52.         RS.Close
  53.  
  54.         Set rngCursorX = rngCursorX.Offset(ColumnOffset:=1)
  55.         rsICPElements.MoveNext
  56.  
  57.     Wend
  58.  
  59.     With sprSS.ActiveWorkbook
  60.         With .Worksheets("Sheet1")
  61.             .Range("$A:$A").Locked = True
  62.             .Range("$1:$1").Locked = True
  63.         End With
  64.         .Protect
  65.     End With
  66.  
  67.     rsICPElements.Close
  68.     blnSaveToTable = True
  69.  
  70.     Set RS = Nothing
  71.     Set sprSS = Nothing
  72.  
  73. End Sub
  74.  
  75. Private Sub sprSpreadsheet_SheetChange(ByVal sh As Object, ByVal Target As Object)
  76.  
  77.     Dim wksPKs As OWC11.Worksheet
  78.     Dim RS As New ADODB.Recordset
  79.     Dim rngPK As OWC11.Range
  80.     Dim sprSS As OWC11.Spreadsheet
  81.  
  82.     If Not blnSaveToTable Then Exit Sub
  83.  
  84.     Set sprSS = Me.sprSpreadsheet.Object
  85.     Set wksPKs = sprSS.ActiveWorkbook.Worksheets("Sheet2")
  86.     Debug.Print wksPKs.Name, wksPKs.Range(Target.Address)
  87.     Set rngPK = wksPKs.Range(Target.Address)
  88.  
  89.     If rngPK <> "" Then
  90.         With DoCmd
  91.             .SetWarnings False
  92.             .RunSQL "UPDATE tblICPResults SET tblICPResults.txtContent='" & _
  93.                 Target & "' WHERE tblICPResults.keyICPResultID=" & rngPK & ";"
  94.             .SetWarnings True
  95.         End With
  96.     Else
  97.         If Target <> "" Then
  98.             With RS
  99.                 .Open "tblICPResults", CurrentProject.Connection, _
  100.                     adOpenDynamic, adLockOptimistic
  101.                 .AddNew
  102.                 ![keyICPSampleID] = sprSS.RectIntersect _
  103.                     (rngPK.Rows.EntireRow, wksPKs.Range("$A:$A"))
  104.                 ![keyICPElementNameID] = sprSS.RectIntersect _
  105.                     (rngPK.Columns.EntireColumn, wksPKs.Range("$1:$1"))
  106.                 ![txtContent] = Target
  107.                 .Update
  108.                 rngPK = ![keyICPResultID]
  109.                 .Close
  110.             End With
  111.         End If
  112.     End If
  113.  
  114.     Set rngPK = Nothing
  115.     Set RS = Nothing
  116.     Set wksPKs = Nothing
  117.     Set sprSS = Nothing
  118.  
  119. End Sub
  120.  
  121. Private Sub PutRecordsetToSheet(ByVal rngCursor As OWC11.Range, _
  122.                                 ByRef RS As ADODB.Recordset, _
  123.                                 ByVal varHeader As Variant)
  124.  
  125.     rngCursor.Worksheet.Unprotect
  126.     rngCursor = varHeader
  127.     With RS
  128.         While Not .EOF
  129.             Set rngCursor = rngCursor.Offset(RowOffset:=1)
  130.             rngCursor = .Fields(0)
  131.             .MoveNext
  132.         Wend
  133.     End With
  134.     rngCursor.Worksheet.Protect
  135.  
  136. End Sub
  137.  
  138.  
Aug 2 '07 #31
FishVal
2,653 Expert 2GB
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.

This solution is for db of VectorBS.

Tables

Stock
StockNumber Text, PK
GarmentNumber Text, PK
StyleNumber Text, PK
SizeID Text, PK
ColourCode Text, PK
StockTotalQuantity Integer
UnitPrice Currency
StockDate Date

Size
SizeID Text, PK
GarmentType Text
SizeDescription Text

Queries

qryColumnHeaders
SELECT Size.SizeID
FROM [Size]
ORDER BY Size.SizeID;

qryRowHeaders
SELECT DISTINCT Stock.GarmentNumber, Stock.StyleNumber, Stock.StockNumber, Stock.ColourCode
FROM Stock;

Forms

frmCrosstabView
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub Form_Load()
  4.  
  5.     Dim sprSS As OWC11.Spreadsheet
  6.     Dim RS As New ADODB.Recordset, rsHeaders As New ADODB.Recordset
  7.     Dim strSQL As String
  8.     Dim rngCursorX As OWC11.Range
  9.  
  10.     Set sprSS = Me.sprSpreadsheet.Object
  11.     Set rngCursorX = sprSS.Worksheets("Sheet1").Range("A1")
  12.  
  13.     sprSS.EnableEvents = False
  14.  
  15.     rsHeaders.Open "qryColumnHeaders", CurrentProject.Connection, _
  16.         adOpenForwardOnly, adLockReadOnly
  17.  
  18.     With RS
  19.         .Open "qryRowHeaders", CurrentProject.Connection, _
  20.             adOpenForwardOnly, adLockReadOnly
  21.         PutRSToSheet rngCursorX, RS, True
  22.         Set rngCursorX = rngCursorX.Offset(ColumnOffset:=.Fields.Count)
  23.         .Close
  24.     End With
  25.  
  26.  
  27.     While Not rsHeaders.EOF
  28.  
  29.         strSQL = "SELECT Stock.StockTotalQuantity FROM Stock RIGHT JOIN qryRowHeaders ON" & _
  30.             " (Stock.StockNumber = qryRowHeaders.StockNumber AND" & _
  31.             " Stock.GarmentNumber = qryRowHeaders.GarmentNumber AND" & _
  32.             " Stock.StyleNumber = qryRowHeaders.StyleNumber AND" & _
  33.             " Stock.ColourCode = qryRowHeaders.ColourCode AND Stock.SizeID='" & _
  34.             rsHeaders![SizeID] & "');"
  35.         RS.Open strSQL, CurrentProject.Connection, _
  36.             adOpenForwardOnly, adLockReadOnly
  37.         PutRSColumnToSheet rngCursorX, RS, rsHeaders![SizeID]
  38.         RS.Close
  39.  
  40.         Set rngCursorX = rngCursorX.Offset(ColumnOffset:=1)
  41.         rsHeaders.MoveNext
  42.  
  43.     Wend
  44.  
  45.     sprSS.ActiveWorkbook.Protect
  46.     rsHeaders.Close
  47.     sprSS.EnableEvents = True
  48.  
  49.     Set RS = Nothing
  50.     Set sprSS = Nothing
  51.  
  52. End Sub
  53.  
  54. Private Sub sprSpreadsheet_SheetChange(ByVal sh As Object, ByVal Target As Object)
  55.  
  56.     Dim RS As New ADODB.Recordset
  57.  
  58.     With RS
  59.  
  60.         .Open "Stock", CurrentProject.Connection, _
  61.             adOpenDynamic, adLockOptimistic
  62.         .MoveFirst
  63.         .Filter = BuildFilter(sh, Target)
  64.  
  65.         If Not .EOF Then
  66.             ![StockTotalQuantity] = Target
  67.         Else
  68.             If Target <> "" Then
  69.                 .AddNew
  70.                 UpdateRecord sh, Target, RS
  71.             End If
  72.         End If
  73.  
  74.         .Update
  75.         .Close
  76.  
  77.     End With
  78.  
  79.     Set RS = Nothing
  80.  
  81. End Sub
  82.  
  83. Private Function PutRSColumnToSheet(ByVal rngCursor As OWC11.Range, _
  84.                                     ByRef RS As ADODB.Recordset, _
  85.                                     ByVal varHeader As Variant) As OWC11.Range
  86.  
  87.     Dim rngUpperCell As OWC11.Range
  88.  
  89.     rngCursor.Worksheet.Unprotect
  90.     rngCursor = varHeader
  91.     Set rngUpperCell = rngCursor
  92.  
  93.     With RS
  94.         While Not .EOF
  95.             Set rngCursor = rngCursor.Offset(RowOffset:=1)
  96.             rngCursor = .Fields(0)
  97.             .MoveNext
  98.         Wend
  99.     End With
  100.     rngCursor.Worksheet.Parent.Parent.RectUnion(rngUpperCell, rngCursor).Locked = False
  101.     rngCursor.Worksheet.Protect
  102.  
  103. End Function
  104.  
  105. Private Sub PutRSToSheet(ByVal rngCursor As OWC11.Range, _
  106.                          ByRef RS As ADODB.Recordset, _
  107.                          ByVal blnHeaders As Boolean)
  108.  
  109.     Dim intX As Integer
  110.  
  111.     rngCursor.Worksheet.Unprotect
  112.  
  113.     With RS
  114.         If blnHeaders Then
  115.             intX = 0
  116.             For Each fld In .Fields
  117.                 rngCursor.Offset(ColumnOffset:=intX) = fld.Name
  118.                 intX = intX + 1
  119.             Next
  120.         End If
  121.         While Not .EOF
  122.             Set rngCursor = rngCursor.Offset(RowOffset:=1)
  123.             intX = 0
  124.             For Each fld In .Fields
  125.                 rngCursor.Offset(ColumnOffset:=intX) = fld.Value
  126.                 intX = intX + 1
  127.             Next
  128.             .MoveNext
  129.         Wend
  130.     End With
  131.     rngCursor.Worksheet.Protect
  132.  
  133. End Sub
  134.  
  135. Private Function BuildFilter(ByRef shSheet As OWC11.Worksheet, _
  136.                              ByRef rngTarget As OWC11.Range) As String
  137.  
  138.     Dim sprSS As OWC11.Spreadsheet
  139.     Dim i As Integer
  140.  
  141.     Set sprSS = shSheet.Parent.Parent
  142.  
  143.     For i = 0 To 3
  144.         BuildFilter = BuildFilter & _
  145.             shSheet.Range("A1").Offset(ColumnOffset:=i) & "='" & _
  146.             sprSS.RectIntersect(shSheet.Range("$A:$A").Offset(ColumnOffset:=i), _
  147.             rngTarget.EntireRow) & "' AND "
  148.     Next i
  149.     BuildFilter = BuildFilter & "SizeID='" & _
  150.         sprSS.RectIntersect(shSheet.Range("$1:$1"), rngTarget.EntireColumn) & "'"
  151.  
  152.     Set sprSS = Nothing
  153.  
  154. End Function
  155.  
  156. Private Sub UpdateRecord(ByRef shSheet As OWC11.Worksheet, _
  157.                          ByRef rngTarget As OWC11.Range, _
  158.                          ByRef RS As ADODB.Recordset)
  159.  
  160.     Dim sprSS As OWC11.Spreadsheet
  161.     Dim i As Integer
  162.  
  163.     Set sprSS = shSheet.Parent.Parent
  164.  
  165.     With RS
  166.         For i = 0 To 3
  167.             .Fields(shSheet.Range("A1").Offset(ColumnOffset:=i).Value) = _
  168.                 sprSS.RectIntersect(shSheet.Range("$A:$A").Offset(ColumnOffset:=i), _
  169.                 rngTarget.EntireRow)
  170.         Next i
  171.         ![SizeID] = sprSS.RectIntersect(shSheet.Range("$1:$1"), rngTarget.EntireColumn)
  172.         ![StockTotalQuantity] = rngTarget
  173.     End With
  174.  
  175.     Set sprSS = Nothing
  176.  
  177. End Sub
  178.  
  179.  
Aug 2 '07 #32
This solution is for db of VectorBS.

<big snip>

[/code]

Thanks for the help! I've copied all of this to my project, and I'll be playing with it this week.
Chuck
Aug 6 '07 #33
I'm close to done, but I see an unusual effect at times.

I have a combobox on the form with the OWC9 spreadsheet object.
When I pull down the combobox, my spreadsheet starts flickering until I click in one of the cells. After that, I can use the combobox without any flickering. I've tried selecting a different cell before using the combobox, but I get the same results.

To eliminate the effect, I have to comment out my line
sst.autofit = true


Have any of you who've worked with the spreadsheet component run into anything like this?
Chuck
Aug 7 '07 #34
can I have some examples too? I am trying to recreate the ICP testing project and chuckbo's project as well.
Sep 2 '07 #35

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Dave | last post by:
I came across an article in SQL Mag about Crosstab Queries. It works great in Query Analyzer, but I'm stuck on how to use it in an Access ADP. I need to use it as a Recordsource in a form and...
1
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to...
3
by: Bryan | last post by:
I've been looking over this newsgroup, but I can't find an answer to my problem. I see that a few others have posted this issue over the years, but none of them got a response. I'll give it...
2
by: carl.barrett | last post by:
Hi, I'm back with the same question as I still can't get it to display my data the way I want it to. The table lists information about a perpetrator involved with an anti social behaviour...
8
by: Penny | last post by:
(Access 2003 Multiuser Split DB, Windows XP Pro) Hi All, I would really appreciate just some basic tips on how to make a Crosstab Form based on a Crosstab Query. The query always has the same...
27
by: Bob | last post by:
running access 2k; mdb w/ linked tables to another mdb (front/back-end); trying to run a query that updates a table FROM information from a crosstab query. I AM NOTT trying to update the...
13
by: salad | last post by:
Operating in A97. I didn't receive much of a response conserning Pivot tables in Access. Pivot tables are nice, but a CrossTab will work for me too. Using a Pivot table, one is actually...
7
by: Gerhard | last post by:
Hi there, I need a little help please. I need to create the following report for a medical training school: Course...
2
by: Jim Devenish | last post by:
I wish to create a crosstab query as the record source for a report. It needs to count data between selected dates which are entered by the user in a popup window. The following Select query...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.