Crosstab Behaviour required for Data Input

[vectorBS]

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?

[puppydogbuddy]

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

[vectorBS]

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?

[puppydogbuddy]

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.

[vectorBS]

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.

[puppydogbuddy]

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.

[vectorBS]

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.

[puppydogbuddy]

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.

[vectorBS]

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

[puppydogbuddy]

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

[vectorBS]

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.

TRANSFORM Sum(Stock.StockTotalQuantity) AS SumOfStockTotalQuantity
SELECT Stock.StockDate, Stock.StockNumber, Stock.GarmentNumber, Stock.StyleNumber, Stock.ColourCode, Stock.UnitPrice
FROM Stock
GROUP BY Stock.StockDate, Stock.StockNumber, Stock.GarmentNumber, Stock.StyleNumber, Stock.ColourCode, Stock.UnitPrice
PIVOT Format(Stock.SizeID) In ("S","M","L","XL","XXL","30","32","34","36","38");

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......

[puppydogbuddy]

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.

TRANSFORM Sum(Stock.StockTotalQuantity) AS SumOfStockTotalQuantity
SELECT Stock.StockDate, Stock.StockNumber, Stock.GarmentNumber, Stock.StyleNumber, Stock.ColourCode, Stock.UnitPrice
FROM Stock
GROUP BY Stock.StockDate, Stock.StockNumber, Stock.GarmentNumber, Stock.StyleNumber, Stock.ColourCode, Stock.UnitPrice
PIVOT Format(Stock.SizeID) In ("S","M","L","XL","XXL","30","32","34","36","38");

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.

[FishVal]

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.

TRANSFORM Sum(Stock.StockTotalQuantity) AS SumOfStockTotalQuantity
SELECT Stock.StockDate, Stock.StockNumber, Stock.GarmentNumber, Stock.StyleNumber, Stock.ColourCode, Stock.UnitPrice
FROM Stock
GROUP BY Stock.StockDate, Stock.StockNumber, Stock.GarmentNumber, Stock.StyleNumber, Stock.ColourCode, Stock.UnitPrice
PIVOT Format(Stock.SizeID) In ("S","M","L","XL","XXL","30","32","34","36","38");

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.

[vectorBS]

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,size2q ty,size3qty,siz e4qty,size5qty
Keeping in mind size1qty refers to S/30 and so on.

[puppydogbuddy]

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,size2q ty,size3qty,siz e4qty,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.