How do i transfer rows into columns? - Microsoft Access / VBA

How do i transfer rows into columns using query?

Please help me to solve this situation

I have a table that stores records like

Table “x”

Expand|Select|Wrap|Line Numbers

 ID WeekEnd %

1 10/12/07 70

1 10/13/07 80

1 10/14/07 40

1 10/15/07 45

2 10/12/07 20

2 10/13/07 30

2 10/15/07 30

I want to display the above Table in the following format using query.

Expand|Select|Wrap|Line Numbers

 
ID 10/12/07 10/13/07 10/14/07 10/15/07

1     70       80       40       45

2     20       30       NA       30

I had tried crosstab query but it display only the sum or avg.. functions value not exactly value in the table.

1) Want to display the WeekEnd as Table head and display values accordingly
2) Fill NA for empty fields

Note: Please provide me better solution other than creating a table.

Thanks,
Karthik

Sep 7 '08 #1

Subscribe Post Reply

1713

FishVal

2,653

Expert 2GB

Helli, Karthik.

Sum() and Avg() are not the only aggregate functions.
E.g. First() will work well in your case.
BTW on table having only unique combinations of [ID] and [WeekEnd] values Sum() and Avg() functions will return exactly what you need. ;)

Kind regards,
Fish

Sep 7 '08 #2

NeoPa

32,556

Expert Mod 16PB

From the fact that you don't mention how you want multiples to be handled, I assume that you have only one record for each position.

If this is true then a CrossTab query with (almost) any of the aggregate functions will work.

The First of 1 == The Avg of 1 == Max of 1 == etc etc.

Sep 7 '08 #3

smrkeyan316

Helli, Karthik.

Sum() and Avg() are not the only aggregate functions.
E.g. First() will work well in your case.
BTW on table having only unique combinations of [ID] and [WeekEnd] values Sum() and Avg() functions will return exactly what you need. ;)

Kind regards,
Fish

--------------------------------------
Hello,

Thanks for giving me the first step to solve the problem.

And there are few more issues in the database. I will try if not i will get back to you with more questions.

Thanks for your support.
Karthik

Sep 9 '08 #4

Similar topics

Relational Data Transfer in DTS

by: kamalkumar | last post by:

Hi Please help for this simple problem DTS Transfer or any other method?

Microsoft SQL Server

ADODB OleDbDataAdapter different count of rows from Active Directory

by: Ireneus Broncel | last post by:

I have a class which reads Groups and Users from ActiveDirectory. The Problem is, that i have about 10000 rows as product. When I am trying to read the "memberOf" Objects out of this field i get...

C# / C Sharp

Any advice about large size database transfer by c#?

by: Dancefire | last post by:

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, What i am tring to do is to transfer a database tabe SRC to another database DEST. Actually it's not only simple transfer, but the row in...

C# / C Sharp

How to transfer a value from an Invisible Grid to an visible Grid

by: AssanKhan Ismail | last post by:

I'd like to know ,in our case we have two grids with equal number of rows and columns ,actually in runtime we want to transfer one cell's value to another grid when the first grid's visible...

ASP.NET

transfer SQL data

by: shank | last post by:

I need to make a table's data available to users. The table is about 150,000 rows x 20 columns. So Excel is out of the question. I think the next common file types would be MDB or DBF. Using ASP,...

ASP / Active Server Pages

how to count rows and columns of integers/doubles in a file?

by: Martin Joergensen | last post by:

Hi, I have some files which has the following content: 0 0 0 0 0 0 0 1 1 1 1 0 0 1 1 1 1 0 0 1 1 1 1 0 0 1 1 1 1 0 0 0 0 0 0 0

C / C++

Transfer Table to Existing Excel Worksheet

by: hmiller | last post by:

Hey there folks: I have been trying to get this work for about a week now. I'm new to VBA... I am trying to transfer a populated table in Access to an existing, but blank, Excel worksheet. I...

Microsoft Access / VBA

Help Summing Rows and Columns In A Matrix

by: lethek39 | last post by:

Hey I have been trying to figure out how to sum rows and columns in a matrix square. I also have been trying to get the program to list the numbers of the diagonal in the matrix. So far this is the...

Python

How do i transfer different rows into (a) columns heads and (b) rows using SQL?

by: smrkeyan316 | last post by:

How do i transfer rows into columns using SQL? Please help me to solve this situation using MS ACCESS Query I have a table that stores records like Table “x” ID WeekEnd % 1 10/12/07 70

Microsoft Access / VBA

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

General