How to dynamiccally change table structure in code

[Dou]

I want to dynamiccally change table structure in Access 2000 code.
that is dynamiccally add table structure field.
when a table's records have changed, the another table structure will be change,
How to do it in code? thanks

 

[BruceM]

You don't change a table's structure with code, you do it in table design
view. If you need to arrange the information you could use a query. What do
you need to do?

 

[John Nurick]

You can if you want to, but if you're intending to alter the structure
of tables as part of the ordinary operation of your database there's
something badly wrong with the design.

 

[Dou]

My table structure:

OrderNo Text 20
Color Text 20
Size Text 20
OrderQty Integer
ShipVia Text 10
DelDate Date 'Delivery date


Table Content:

OrderNo Color Size OrderQty ShipBy DelDate

--------------------------------------------------------------------------------

L60092 04Marine 4A 46 Sea 30-Jun-05
L60092 04Marine 5A 32 Sea 30-Jun-05
L60092 04Marine 6A 69 Sea 30-Jun-05
L60092 04Marine 8A 106 Sea 30-Jun-05
L60092 04Marine 10A 133 Sea 30-Jun-05
L60092 04Marine 12A 134 Sea 30-Jun-05
L60092 04Marine 14A 115 Sea 30-Jun-05
L60092 04Marine 16A 65 Sea 30-Jun-05

L60230 17Beige Clair 4A 8 Air 30-Jun-05
L60230 17Beige Clair 5A 6 Air 30-Jun-05
L60230 17Beige Clair 6A 11 Air 30-Jun-05
L60230 17Beige Clair 8A 17 Air 30-Jun-05
L60230 17Beige Clair 10A 24 Air 30-Jun-05
L60230 17Beige Clair 12A 17 Air 30-Jun-05
L60230 17Beige Clair 14A 22 Air 30-Jun-05
L60230 17Beige Clair 16A 4 Air 30-Jun-05

I want to create the foolwing report format:


OrderNo Color\Size 4A 5A 6A 8A 10A 12A 14A 16A Total ShipBy DelDate

--------------------------------------------------------------------------------

L60092 04Marine 46 32 69 106 133 134 115 65 700 Sea 30-Jun-05
L60230 17Beige Clair 8 6 11 17 24 17 22 4 109 Air 30-Jun-05

--------------------------------------------------------------------------------

Total 54 38 80 123 157 151 137 69 809



How to create this report? thanks

 

[Duane Hookom]

Consider creating a crosstab query with OrderNo, Color & Size, Sum(OrderQty)
ShipVia, and DelDate as Row Headings. Size would be the Column Heading and
Sum(OrderQty) is the value.

--
Duane Hookom
MS Access MVP


My table structure:

OrderNo Text 20
Color Text 20
Size Text 20
OrderQty Integer
ShipVia Text 10
DelDate Date 'Delivery date


Table Content:

OrderNo Color Size OrderQty ShipBy DelDate



L60092 04Marine 4A 46 Sea 30-Jun-05
L60092 04Marine 5A 32 Sea 30-Jun-05
L60092 04Marine 6A 69 Sea 30-Jun-05
L60092 04Marine 8A 106 Sea 30-Jun-05
L60092 04Marine 10A 133 Sea 30-Jun-05
L60092 04Marine 12A 134 Sea 30-Jun-05
L60092 04Marine 14A 115 Sea 30-Jun-05
L60092 04Marine 16A 65 Sea 30-Jun-05

L60230 17Beige Clair 4A 8 Air 30-Jun-05
L60230 17Beige Clair 5A 6 Air 30-Jun-05
L60230 17Beige Clair 6A 11 Air 30-Jun-05
L60230 17Beige Clair 8A 17 Air 30-Jun-05
L60230 17Beige Clair 10A 24 Air 30-Jun-05
L60230 17Beige Clair 12A 17 Air 30-Jun-05
L60230 17Beige Clair 14A 22 Air 30-Jun-05
L60230 17Beige Clair 16A 4 Air 30-Jun-05

I want to create the foolwing report format:


OrderNo Color\Size 4A 5A 6A 8A 10A 12A 14A 16A Total
ShipBy DelDate



L60092 04Marine 46 32 69 106 133 134 115 65 700 Sea
30-Jun-05
L60230 17Beige Clair 8 6 11 17 24 17 22 4 109 Air
30-Jun-05



Total 54 38 80 123 157 151 137 69 809



How to create this report? thanks

 

[Dou]

I create the following Crosstab:

TRANSFORM Sum(PIDetails.OrderQty) AS OrderQtyOfSum
SELECT PIDetails.PINo, PIDetails.OrderNo, PIDetails.Style, PIDetails.Color,
PIDetails.ShipVia, PIDetails.DelDate, Sum(PIDetails.OrderQty) AS Total
FROM PIDetails
GROUP BY PIDetails.PINo, PIDetails.OrderNo, PIDetails.Style,
PIDetails.Color, PIDetails.ShipVia, PIDetails.DelDate
ORDER BY PIDetails.Size
PIVOT PIDetails.Size;

the ORDER BY PIDetails.Size doesn't work, I hope it is 4A,5A,6A,8A,10A,12A.
How to do it? thanks

 

[Duane Hookom]

I'm not sure what difference the order of the columns makes? If you display
the results in a report, you can set the display any way you want.

To specify the order, you can set the column headings property to:
4A,5A,6A,8A,10A,12A

 

[Dou]

Thanks. my report headings is not fixed, sometime is
4A,5A,6A,8A,10A,12A, but soemtime is only 4A,5A,6A,8A or 6A,8A .

How to create a changeable Crosstab, the report can automatically change
heading columns number(8 sizes, 6 sizes or 4 sizes...)?

 

[Duane Hookom]

You would need to have a table that states what order the sizes should be
reported in. You can use this table joined to your existing table to grab
your column headings in the proper order.

Use DAO code to change the SQL property of your saved crosstab query.