?
Solved

Convert Values in Field into Field Headings

Posted on 2012-08-28
7
Medium Priority
?
411 Views
Last Modified: 2012-09-14
Attached file shows data as-is vs. the desired outcome. The "data as-is" (top table) is in read-only Access tables that I can't change.

I'd like to build a query that uses the dates in the "Order#_Date" columns as column names. Those columns would then be populated with the order quantities corresponding to the name/date of each column.

If necessary I can use VBA to do this but it's been awhile since I've done anything in VBA so please include plenty of details if the solution requires VBA.

Thank you!
0
Comment
Question by:ltdanp22
7 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38340854
Attachment missing.

>I'd like to build a query that uses the dates in the "Order#_Date"
Word of advice:  Lose the # in your query column, as this forces you to use square brackets [ ] when referring to it in queries, which adds to the overhead of working with the query.
0
 

Author Comment

by:ltdanp22
ID: 38340979
Sorry. Could have sworn I attached it.
Future-Volumes-Tool---Example-fo.xlsx
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38341605
>If necessary I can use VBA to do this

What's the preferred option then? VB.NET? Would you be downloading a datatable of this data and then transforming it?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:ltdanp22
ID: 38341792
What's the preferred option then? VB.NET?

I've only ever used VBA. Not sure how similar VB.NET is. I included the VB.NET category because I couldn't find a VBA category.

Would you be downloading a datatable of this data and then transforming it?

The original table is a linked table...a very big linked table. I was planning to use a query to pull a subset of data and then "transform" the query results.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38351766
You can use a crosstab query to bring in the data in the format you desire or can bring in all the (raw) data and use Excel pivot table.  I would recommend the crosstab query unless you need to do something fancy.  Also, a "very large" amount of data might exceed your worksheet rows.
0
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 38394827
Attached file shows data as-is vs. the desired outcome. The "data as-is" (top table) is in read-only Access tables that I can't change.

Too bad, because the table design is really bad.

Anyhow, try this VBA code

Dim db as DAO.Database 
Dim rs as DAO.Recordset 
dim strSQL as string


Set db = CurrentDB 
Set rs = DB.OpenRecordset("yourtablehere") 

strSQL = "Select PartNumber, "

strSQL = strSQL & "Order1_Qty as [" & rs.Fields("Order1_date") & "], "
strSQL = strSQL & "Order2_Qty as [" & rs.Fields("Order2_date") & "], "
strSQL = strSQL & "Order3_Qty as [" & rs.Fields("Order3_date") & "], "
strSQL = strSQL & "Order4_Qty as [" & rs.Fields("Order4_date") & "] "

strSQL = " from yourtable"
rs.Close
Set rs = Nothing

DoCmd.RunSQL strSQL

Set db = Nothing

Open in new window

0
 

Author Closing Comment

by:ltdanp22
ID: 38399864
Thanks, I made a version of this work. Completely agree about the table design.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question