Same field multiple times

[Robertaccessnewb]

I am really stuck on this one:

I need to display and EDIT the data from this query:

FROM THIS:

NAME	ITEM	VALUE	VALUE_ID
TOM	1025	9,3	47
TOM	1025	44,9	40
TOM	1025	3,1	46
SUE	1025	8,9	47
SUE	1025	45,0	40
SUE	1025	2,8	46

TO THIS:

NAME	ITEM	VALUE_ID=47	VALUE_ID=40	VALUE_ID=46
TOM	1025	9,3	44,9	3,1
SUE	1025	8,9	45	2,8

Snapshot of my real rwa Data:

This is my Crosstab query, which i cant edit of course:

I know when I do a crosstable query i cant change the values.
How can I set up a query or a form to show the valuefields in different columns?

Sorry but i am a newb.

 

[plog]

I need to display and EDIT the data from this query:

Even for someone with a few years experience with Access this would be a very difficult task. Picking one of those 2 things is achievable for you, but not both in 1. Just getting the data to display in that manner on a form is a huge undertaking, then adding the ability to edit it doubles its complexity.

Instead, I would just build a form based on the table to edit the data, with each VALUE_ID in its own row. Then if you want to display it as you have shown use the cross-tab query or build a report on it (which is difficult as well).

 

[ebs17]

The view of the target representation can be created using a crosstab query. But the order 47, 40, 46 is quite strange because the pivot columns are sorted.

You should update in the original table (via form).

Or you go to Excel. You can do everything in one table. There are plenty of functionalities to work criss-cross.

 

[561414]

To get the desired output in Access, you can use a combination of a query and a crosstab query.

First, create a query that groups the data by Name, Item, and Value_ID, and calculates the sum of the Value column for each group. This will give you a table with the following columns: Name, Item, Value_ID, and SumOfValue. Now Name and Value are reserved, so be wary of that.

Then just save this query:

SELECT
    Name,
    Item,
    Value_ID,
    SUM(Value) AS SumOfValue
FROM yourTable
GROUP BY Name, Item, Value_ID;

Next, create a crosstab query based on the previous query. In the crosstab query, use Name and Item as the row headings, and Value_ID as the column heading. This will pivot the data so that the Value_ID values become the column headers, and the corresponding SumOfValue values will be displayed in the cells under each Value_ID column.

Here is an example of what the crosstab query might look like:

TRANSFORM
    SUM(SumOfValue) AS SumOfValue
SELECT
    Name,
    Item
FROM yourQuery
GROUP BY Name, Item
PIVOT Value_ID;

You'll get the desired output, here's a sample attached as well.

 

[Pat Hartman]

Here's a sample I made years ago when a client wanted to create a forecasting application and have the data entry look like it did when he was working with Excel. My solution is what I call a "bound denormalized form". It looks like a crosstab but it is updateable. I've included two samples. One that pivots expense types and one that pivots months. The months pivot is easy to justify and since it isn't likely we will change the number of months in a year any time soon, pretty safe to implement. In theory you could do the same thing for days in a month. Although the expences works fine, you could run into trouble if you nad more than about 20 or so expense types. So, always know your dataset before attempting to use this technique.

Bound Denormalized Form

I've left the old versions here but ALWAYS go to the last post to get the current version. This database contains two examples (using the same technique) of how to take data that is properly normalized and display it so that it looks like a spreadsheet (rows displayed as columns) but is...

www.access-programmers.co.uk

 

[arnelgp]

you will need to Save the crosstab to some Dummy table (on my case dummyT).
then you can edit the record from dummyT and on the BeforeUpdate of the form
update the Base table (yourTable).

see Form1 (and dsData) form and the code in it.

 

[Robertaccessnewb]

Here's a sample I made years ago when a client wanted to create a forecasting application and have the data entry look like it did when he was working with Excel. My solution is what I call a "bound denormalized form". It looks like a crosstab but it is updateable. I've included two samples. One that pivots expense types and one that pivots months. The months pivot is easy to justify and since it isn't likely we will change the number of months in a year any time soon, pretty safe to implement. In theory you could do the same thing for days in a month. Although the expences works fine, you could run into trouble if you nad more than about 20 or so expense types. So, always know your dataset before attempting to use this technique.

THANK YOU! Your sample Database guided me to the solution!

The trick is to make a query of a Table and join the other queries.

I should have asked much earlier, I always try to figure out on my own and lose weeks of my life :-(
Thank you again.

 

[Pat Hartman]

I'm glad you understood the solution. Not everyone does Good luck. Just keep in mind that this is probably a limited use solution so don't go promising anything bigger than an annual forecast or maybe 10 year history until you see where the solution bogs down.

One of my students is using the report solution to show history. He has 10, 15, 20 year options but because he didn't want to write the code to spread out the columns, or to just use the 20 year version, he made three versions of the report so the columns would be nicely spaced.