How can I achieve this?

[Jim Franklin]

Hi,

I am using A2K and I have a table with four fields, say ProdCode, Size,
Colour, StockLevel. There is an index so that 2 records cannot have the same
ProdCode, Size and Colour, as follows:

Size Colour PCode StockLevel
S Blue 123 10
M Blue 123 20
L Blue 123 30
M Red 123 22
S Red 123 12
L Red 123 32
M Green 123 21
S Green 123 11
L Green 123 31


I would like to be able to display this data in a matrix as follows. When
each cell of the matrix is edited/updated, the original record is changed to
reflect the new value in the Stock field.

Blue Green Red
S 10 11 12
M 20 21 22
L 30 31 32

Can anyone give me some clues as to the best way to go about this? Or even
if it is possible to do?

Many thanks for reading / any help you can give,

Jim F.

 

[Ron Weiner]

Jim

Looks like you need a CrossTab Query. From the description you gave of the
data the Sql might look like this:

TRANSFORM Sum(StockLevel) AS SumStockLevel
SELECT Size
FROM YourTable
WHERE Pcode=123
GROUP BY Size
ORDER BY Size DESC
PIVOT Colour

I am just guessing that you have already gotten this far. Now the big
"Problem" with this is that Crosstab queries are NEVER updateable. So...
you will need devise your own way to allow users to edit the values. In the
past what I have done was to insert the results of the Crosstab into a temp
table. I then let the user mess with the temp table via a bound form.
Lastly write some VBA/ADO code to morph the values in the temp table back
into the normalized tables when the form is closed or a Save button is
pushed.

Ron W

 

[Jim Franklin]

Thanks Ron,

I had got this far, hoping there is an easier solution. I want to try and
avoid writing a temptable each time as it seems messy and also its going to
be happening alot, so 1) its slow and 2) makes a big file.

Is there an easier way? I have been looking at some 3rd party Grid controls
which seem to do this, but I have no idea which ones are any good. Is this
the right way to go, anyone?

Thanks again,
Jim F.

 

[Ron Weiner]

Jim

Don't know if you are still working on this as I haven't had the chance to
get back here since the 22nd, but consider the following before you rush out
and start buying a grid tool.

The tool is just one more thing that has to be installed with your
application.
The Grid tool is one extra failure point that you may need to deal with when
the user can't get it to register, or the reference to it breaks in your
Access app, or operates erratically.

I have had to do this kind of thing before and have successfully deployed a
non trivial enterprise level hybrid application using Access2K, Sql2K, and
ASP by using a couple of permanent temp tables in my Sql database.

I used one table to actually hold the data and the other table to describe
it. The table that holds that data looks like...

CREATE TABLE [tblTemp] (
[TempID] [int] IDENTITY (1, 1) NOT NULL ,
[Dirty] [bit] NOT NULL CONSTRAINT [DF_tblTemp_Dirty] DEFAULT (0),
[CurUserID] [int] NOT NULL ,
[StudentID] [int] NULL ,
[StudentName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp3] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp4] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp5] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp6] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp7] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp8] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp9] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp10] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
-- ad-nauseaum till done Be sure to add as many columns as is necessary to
hold
-- the maximum number of columns that the worst case crosstab might return.
-- my app stopen with 99 temp columns
CONSTRAINT [PK_tblTemp] PRIMARY KEY CLUSTERED
(
[TempID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
END

and the table that describes how the data should look / behave looks like...

CREATE TABLE [tblColumnDataStruct] (
[ColumnDataID] [int] IDENTITY (1, 1) NOT NULL ,
[CurUserID] [int] NULL ,
[FieldName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DisplayName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FieldType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DataType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CboRowSource] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DataMin] [float] NULL ,
[DataMax] [float] NULL ,
[Decimals] [int] NULL ,
[AsmtDefColID] [int] NULL ,
[AsmtID] [int] NULL ,
[SectionID] [int] NULL ,
[IndentLevel] [int] NULL ,
[MarkPd] [int] NULL ,
[EditOK] [bit] NOT NULL CONSTRAINT [DF_tblColumnDataStruct_EditOK] DEFAULT
(0),
CONSTRAINT [PK_tblColumnDataStruct] PRIMARY KEY CLUSTERED
(
[ColumnDataID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]

Using this structure, a few Stored Procs (that delete and add rows to the
temp tables), and a fair amount of VBA code (to morph the Access Datasheets
and ASP web Pages into the proper shape and save the changed data back to my
normalized structure), allowed me to build Access Subforms and ASP web pages
on the fly that the user could edit and save.

Depending on exactly what you need to do, a similar set up may work well for
you too. If your application is 100% Access you can expect the backend
database (the one with the temp tables) to do some bloating, but a once a
week maintenance compact and repair ought to take care of that problem.
This is NOT a problem with Sql Server 2000.

The biggest speed issue we have found for the app is actually not in the
data collection side, but in the reporting side where we are having to deal
with may more rows and columns being returned in the Crosstab query. Right
now our worst case scenario is printing a class full of report cards that
could take as long as 40 to 60 seconds to create a 60 to 90 page PDF file.

Typically creating the webpage to score a class of 25 to 30 Kids (Rows) for
5 to 10 Scores (Columns) takes just a couple of seconds depending on how
thick the pipe is between the client and the webserver. All in all we are
pretty happy with the performance.

Ron W

 

[Jim Franklin]

Ron,

Not sure if this is good ettiquette in Newsgroups, but I just wanted to say
"Many Thanks."

I am still working on this (I managed to escape the office for a few days)
and I am exploring the path that you suggest.

Thanks again,
Jim

Jim

Don't know if you are still working on this as I haven't had the chance to
get back here since the 22nd, but consider the following before you rush out
and start buying a grid tool.

The tool is just one more thing that has to be installed with your
application.
The Grid tool is one extra failure point that you may need to deal with when
the user can't get it to register, or the reference to it breaks in your
Access app, or operates erratically.

I have had to do this kind of thing before and have successfully deployed a
non trivial enterprise level hybrid application using Access2K, Sql2K, and
ASP by using a couple of permanent temp tables in my Sql database.

I used one table to actually hold the data and the other table to describe
it. The table that holds that data looks like...

CREATE TABLE [tblTemp] (
[TempID] [int] IDENTITY (1, 1) NOT NULL ,
[Dirty] [bit] NOT NULL CONSTRAINT [DF_tblTemp_Dirty] DEFAULT (0),
[CurUserID] [int] NOT NULL ,
[StudentID] [int] NULL ,
[StudentName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp3] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp4] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp5] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp6] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp7] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp8] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp9] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp10] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
-- ad-nauseaum till done Be sure to add as many columns as is necessary to
hold
-- the maximum number of columns that the worst case crosstab might return.
-- my app stopen with 99 temp columns
CONSTRAINT [PK_tblTemp] PRIMARY KEY CLUSTERED
(
[TempID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
END

and the table that describes how the data should look / behave looks like...

CREATE TABLE [tblColumnDataStruct] (
[ColumnDataID] [int] IDENTITY (1, 1) NOT NULL ,
[CurUserID] [int] NULL ,
[FieldName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DisplayName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FieldType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DataType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CboRowSource] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DataMin] [float] NULL ,
[DataMax] [float] NULL ,
[Decimals] [int] NULL ,
[AsmtDefColID] [int] NULL ,
[AsmtID] [int] NULL ,
[SectionID] [int] NULL ,
[IndentLevel] [int] NULL ,
[MarkPd] [int] NULL ,
[EditOK] [bit] NOT NULL CONSTRAINT [DF_tblColumnDataStruct_EditOK] DEFAULT
(0),
CONSTRAINT [PK_tblColumnDataStruct] PRIMARY KEY CLUSTERED
(
[ColumnDataID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]

Using this structure, a few Stored Procs (that delete and add rows to the
temp tables), and a fair amount of VBA code (to morph the Access Datasheets
and ASP web Pages into the proper shape and save the changed data back to my
normalized structure), allowed me to build Access Subforms and ASP web pages
on the fly that the user could edit and save.

Depending on exactly what you need to do, a similar set up may work well for
you too. If your application is 100% Access you can expect the backend
database (the one with the temp tables) to do some bloating, but a once a
week maintenance compact and repair ought to take care of that problem.
This is NOT a problem with Sql Server 2000.

The biggest speed issue we have found for the app is actually not in the
data collection side, but in the reporting side where we are having to deal
with may more rows and columns being returned in the Crosstab query. Right
now our worst case scenario is printing a class full of report cards that
could take as long as 40 to 60 seconds to create a 60 to 90 page PDF file.

Typically creating the webpage to score a class of 25 to 30 Kids (Rows) for
5 to 10 Scores (Columns) takes just a couple of seconds depending on how
thick the pipe is between the client and the webserver. All in all we are
pretty happy with the performance.

Ron W

Thanks Ron,

I had got this far, hoping there is an easier solution. I want to try and
avoid writing a temptable each time as it seems messy and also its going to
be happening alot, so 1) its slow and 2) makes a big file.

Is there an easier way? I have been looking at some 3rd party Grid controls
which seem to do this, but I have no idea which ones are any good. Is this
the right way to go, anyone?

Thanks again,
Jim F.


of
the In
the Or
even

 

[Jess Martyn]

Hello,

Recently, http://extremedesigners.5u.com - A website about web designing -
has been launched. I would like you to take a look at it and pass your
suggestions - complements - about it.

- Regards,
Extreme Designing Team.

Ron,

Not sure if this is good ettiquette in Newsgroups, but I just wanted to say
"Many Thanks."

I am still working on this (I managed to escape the office for a few days)
and I am exploring the path that you suggest.

Thanks again,
Jim

Jim

Don't know if you are still working on this as I haven't had the chance to
get back here since the 22nd, but consider the following before you rush out
and start buying a grid tool.

The tool is just one more thing that has to be installed with your
application.
The Grid tool is one extra failure point that you may need to deal with when
the user can't get it to register, or the reference to it breaks in your
Access app, or operates erratically.

I have had to do this kind of thing before and have successfully

deployed

a
non trivial enterprise level hybrid application using Access2K, Sql2K, and
ASP by using a couple of permanent temp tables in my Sql database.

I used one table to actually hold the data and the other table to describe
it. The table that holds that data looks like...

CREATE TABLE [tblTemp] (
[TempID] [int] IDENTITY (1, 1) NOT NULL ,
[Dirty] [bit] NOT NULL CONSTRAINT [DF_tblTemp_Dirty] DEFAULT (0),
[CurUserID] [int] NOT NULL ,
[StudentID] [int] NULL ,
[StudentName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL

,
[Temp1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp3] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp4] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp5] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp6] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp7] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp8] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp9] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp10] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
-- ad-nauseaum till done Be sure to add as many columns as is necessary to
hold
-- the maximum number of columns that the worst case crosstab might return.
-- my app stopen with 99 temp columns
CONSTRAINT [PK_tblTemp] PRIMARY KEY CLUSTERED
(
[TempID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
END

and the table that describes how the data should look / behave looks like...

CREATE TABLE [tblColumnDataStruct] (
[ColumnDataID] [int] IDENTITY (1, 1) NOT NULL ,
[CurUserID] [int] NULL ,
[FieldName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DisplayName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL

,
[FieldType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DataType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CboRowSource] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL

,
[DataMin] [float] NULL ,
[DataMax] [float] NULL ,
[Decimals] [int] NULL ,
[AsmtDefColID] [int] NULL ,
[AsmtID] [int] NULL ,
[SectionID] [int] NULL ,
[IndentLevel] [int] NULL ,
[MarkPd] [int] NULL ,
[EditOK] [bit] NOT NULL CONSTRAINT [DF_tblColumnDataStruct_EditOK] DEFAULT
(0),
CONSTRAINT [PK_tblColumnDataStruct] PRIMARY KEY CLUSTERED
(
[ColumnDataID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]

Using this structure, a few Stored Procs (that delete and add rows to the
temp tables), and a fair amount of VBA code (to morph the Access Datasheets
and ASP web Pages into the proper shape and save the changed data back

to

my
normalized structure), allowed me to build Access Subforms and ASP web pages
on the fly that the user could edit and save.

Depending on exactly what you need to do, a similar set up may work well for
you too. If your application is 100% Access you can expect the backend
database (the one with the temp tables) to do some bloating, but a once a
week maintenance compact and repair ought to take care of that problem.
This is NOT a problem with Sql Server 2000.

The biggest speed issue we have found for the app is actually not in the
data collection side, but in the reporting side where we are having to deal
with may more rows and columns being returned in the Crosstab query. Right
now our worst case scenario is printing a class full of report cards that
could take as long as 40 to 60 seconds to create a 60 to 90 page PDF file.

Typically creating the webpage to score a class of 25 to 30 Kids (Rows) for
5 to 10 Scores (Columns) takes just a couple of seconds depending on how
thick the pipe is between the client and the webserver. All in all we are
pretty happy with the performance.

Ron W

going
to have
the