present table in a different layout

[wazza]

Hi

I have a table : i.e.

product 1 / Date1 / sales Qty
product 1 / Date2 / sales Qty
product 1 / Date3 / sales Qty
product 1 / Date4 / sales Qty

i want to present this in a different style on the form, using a scroll bar to view records not on the screen: i.e:

Product1:
Date1 Date2 Date3 Date4
salesqty salesqty salesqty salesqty

scroll --------------> if more dates

How can i do this...?

Many thanks!

 

[smercer]

You can use a wizard to make a Pivit table. this will make the fields change to the order that you want the way you want

 

[maxmangion]

i think the first thing you should consider doing is to normalise that table. You should create a table such as

tblProducts
ProductID
Product

in this manner if you decide to change the name of a product you need to change it only once in the tblProducts table and not changing it several times in your current table.

 

[wazza]

what do you mean....?

are you suggesting having a sepreate table for every product..

 

[wazza]

i have given up with the pivot table....

1# because its ugly for the users
2# i want something in a datasheet type view - spreadsheet like

is there any other way to solve my original question - flipping the layout?

someone suggested:
extracting the time period with the sales data i need using a query and vb code to flip the table by pulling each value into a new temp table..

soundz messy..

thanks

 

[Rich]

you can get the display you want by using a crosstab query, although you won't be able to enter data on the form. Other than that try one of the flexigrids advertised here.

 

[smercer]

Another way if you want it to link to database is to link a excell document to database and in the spreedsheet use links from one sheet to another and use autofill (just make sure you use absolute referencing). eg:
In first top left cell type:

=Sheet1!A2

(I did not use A1 because of heading)
1) and then autofill across.

2) move the cells that have autofilled so that the cell with link formula in "column B" is now under "column A"

3) select all cells that you just moved and autofill these across the columns

If you want it in a form or report, just copy the linked cells (not the source) and paste special and link that into your db
Is this what you wanted?

Good luck

 

[wazza]

i assume the excel spreadsheet will have to be open to loop the data back onto the form.

i developing forms for a users and rather them not have to worrk about other programs being active.

the flex-grid looks promising... i dont want the users to input data.

 

[wazza]

mis-read that last bit....

dont need to input data - so i will look at a cross-tab query

i done a search for flexigrids, cannot not churn any results - do u have any links + info

 

[smercer]

the flex-grid looks promising

I think Rich was refering to what I suggested here before about Pivot table

 

[smercer]

i assume the excel spreadsheet will have to be open to loop the data back onto the form.

i developing forms for a users and rather them not have to worrk about other programs being active.

the link will work regardless of whether the excel spreadsheet is open or not. the only thing you may worry about is that the excel needs to be installed on the machine that you want displayed on

I suggest at least trying it. and keep an open mind about it, if it doesn't work there is a way around it

 

[wazza]

is there a way to remove the pivot table total / filters and windows standard grey boxes..

i want a plain table with a few grid lines...

rgrds

 

[smercer]

there is a button on the tool bar called subtotals or totals and it may be selected. just unselect this.

 

[Rich]

a crosstab query is not the same as a pivot table