Query to Redistribute a Table

Hi
I am struggling with how to query a table with the current structure:
Field1 Field2 ...Field14 Jan Feb Mar Apr...
x         y       ...z           10  15   13  16...
etc,
It represents sales per month to customers per delivery adress,
product and so forth.The numbers under each month respresents the
sales for that month.
I need to be able to query so I can see all detalis (ie fileds 1-14)
per row per month, ie redistribute that original table so I can use
month as a field and still use all the original fields. My new table
needs to be something link
Month  Field 1  Field2  ... Field14  New Field=Sales
Jan       X         Y        .... Z           10
Feb       X2       Y2     .....Z2           15
etc
For simplicity I have only illustrated one value per month but there
are sales per month as Reveniue, as kgs, as margin, etc

Any help to bring me in the right direction to query this would be
appreciated,
thanks
Lars
0
plgerh
1/16/2008 10:27:22 AM
access 16762 articles. 3 followers. Follow

2 Replies
731 Views

Similar Articles

[PageSpeed] 8

Lars,
here is an answer to a similar problem from the discussion group.
You can see the fields in the other person's wide flat table.
Hope this makes sense.
start of answer
-----------------------
Well... actually you don't. It's not too hard to move data from a wide-flat
table into a tall-thin one. YOu can use a "Normalizing Union Query" such as

SELECT <primary key field>  "Preposition_I" AS WordType, Preposition_I AS
WordValue
FROM Declension
WHERE Preposition_I IS NOT NULL
UNION ALL
SELECT <primary key field>  "Instrumental_I" AS WordType, Instrumental_I AS
WordValue
FROM Declension
WHERE Instrumental_I IS NOT NULL
UNION ALL
<etc. etc. through all the fields>

             John W. Vinson [MVP]
--------------------
end of answer

Jeanette Cunningham

<plgerh@gmail.com> wrote in message 
news:2f52ff6e-67da-429d-8027-9b7a7dcd17cd@c4g2000hsg.googlegroups.com...
> Hi
> I am struggling with how to query a table with the current structure:
> Field1 Field2 ...Field14 Jan Feb Mar Apr...
> x         y       ...z           10  15   13  16...
> etc,
> It represents sales per month to customers per delivery adress,
> product and so forth.The numbers under each month respresents the
> sales for that month.
> I need to be able to query so I can see all detalis (ie fileds 1-14)
> per row per month, ie redistribute that original table so I can use
> month as a field and still use all the original fields. My new table
> needs to be something link
> Month  Field 1  Field2  ... Field14  New Field=Sales
> Jan       X         Y        .... Z           10
> Feb       X2       Y2     .....Z2           15
> etc
> For simplicity I have only illustrated one value per month but there
> are sales per month as Reveniue, as kgs, as margin, etc
>
> Any help to bring me in the right direction to query this would be
> appreciated,
> thanks
> Lars 


0
Jeanette
1/16/2008 10:47:48 AM
Try by "grouping" yours results by months. (ie SELECT ... FROM  ...  GROUP 
BY ... )
I 'm not it's run in this syntax but it's possible to have what you want.

"Jeanette Cunningham" <nnn@discussions.microsoft.com> a �crit dans le 
message de news:%23G9580CWIHA.5208@TK2MSFTNGP04.phx.gbl...
> Lars,
> here is an answer to a similar problem from the discussion group.
> You can see the fields in the other person's wide flat table.
> Hope this makes sense.
> start of answer
> -----------------------
> Well... actually you don't. It's not too hard to move data from a 
> wide-flat
> table into a tall-thin one. YOu can use a "Normalizing Union Query" such 
> as
>
> SELECT <primary key field>  "Preposition_I" AS WordType, Preposition_I AS
> WordValue
> FROM Declension
> WHERE Preposition_I IS NOT NULL
> UNION ALL
> SELECT <primary key field>  "Instrumental_I" AS WordType, Instrumental_I 
> AS
> WordValue
> FROM Declension
> WHERE Instrumental_I IS NOT NULL
> UNION ALL
> <etc. etc. through all the fields>
>
>             John W. Vinson [MVP]
> --------------------
> end of answer
>
> Jeanette Cunningham
>
> <plgerh@gmail.com> wrote in message 
> news:2f52ff6e-67da-429d-8027-9b7a7dcd17cd@c4g2000hsg.googlegroups.com...
>> Hi
>> I am struggling with how to query a table with the current structure:
>> Field1 Field2 ...Field14 Jan Feb Mar Apr...
>> x         y       ...z           10  15   13  16...
>> etc,
>> It represents sales per month to customers per delivery adress,
>> product and so forth.The numbers under each month respresents the
>> sales for that month.
>> I need to be able to query so I can see all detalis (ie fileds 1-14)
>> per row per month, ie redistribute that original table so I can use
>> month as a field and still use all the original fields. My new table
>> needs to be something link
>> Month  Field 1  Field2  ... Field14  New Field=Sales
>> Jan       X         Y        .... Z           10
>> Feb       X2       Y2     .....Z2           15
>> etc
>> For simplicity I have only illustrated one value per month but there
>> are sales per month as Reveniue, as kgs, as margin, etc
>>
>> Any help to bring me in the right direction to query this would be
>> appreciated,
>> thanks
>> Lars
>
> 

0
Vincent
1/16/2008 10:56:19 AM
Reply:

Similar Artilces:

Query Problems
I have a table of ecards that is populated from a website and includes receiver, sender, and Team leader. The problem I am having is names can be entered into the table like this "Tom Thumb", but Team leader is a dropdown menu that is constant and lists name like this: "Tom S. Thumb". When I make a query to find all the members of Tom S. Thumb's team, I get everyone except Tom Thumb, who is member too. Tom Thumb has his own higher team leader, so I can't change that. How do I get Tom Thumb to be included when I enter "Tom S. Thumb" as Team Leader? ...

Excel web query returns no data
When I enter a web addreess into IE it shows the page When I set up a web query in Excel it allows me to select the relevant tables, but then returns no data The same Excel query worked on previuous versions of Excel Web address for query http://www.bmreports.com/servlet/com.logica.neta.bwp_PanBM DataServlet?param1=T_CNQPS- 4&param2=&param3=&param4=&param5=2001-04-01&param6=* Any help appreciated ...

Query to Mail Merge issue
I am running Access2003 on WinXP SP2. I send out dues letters for members of a retiree group. When I try to do a mail merge to my Dues Query all goes fine except the telephone number in the query which is (555)555-1234 comes out 5555551234. If I copy the query into an excel spreadsheet and use that as the datasource for the mail merge it works fine. Is there something I need to do to format the query so it merges with the right format. Thanks Billa In the query format the field to include the parentheses and dash. In query design view, instead of the field name put an expression alo...

Export query to Excel with formated heading
When export from query to Excel, I'd like to turn some columns into heading row on top of Excel sheets how to turn this: LastName FirstName SID Section Instructor Doe John 1234 0545 Su Doe Jane 2345 0545 Su into this? Class Roster Instructor: Su Section: 0545 LastName FirstName SID Doe John 1234 Doe Jane 2345 I think one way of achieving this would be to create a report in Access where you would group under instructor with the students in the detail section. From the report you can then use the analyze with Ex...

Pivot Table with Quarter Subtotals across the top
Hello, I'm constantly creating pivot tables that show sales information by date across the top from left to right. I would love to insert a subtotal at the end of each calender quarter to subtotal the last three months. As it stands right now I just drop in the "Month" field from the data into the pivot table and the months flow through the pivot table. Is it possible to insert a quarter subtotal, say "1Q07" that will sum Jan-07, Feb-07 & Mar-07? Thanks in advance, Dza In 2007- Select any date heading, on the options ribbon click Group Field in the Group g...

Query Criteria issue
Access 2003: Trying to create a demographics report that will allow the following. Gender example On the form "frmStudentDemographics" is a drop down field named "cboGender" When the user makes a selection "Male" a report is generated for only Males. But in some cases the user will want to run a report for both (all) genders. So I added a check box next to the drop down named "ckAllGender" My idea is that if that box is checked then the report will show information on Both (all) genders. So in the query that generates the report, in the c...

Suggest a solution
Hi all. I have a table named Articles that has the fields: ArticleID, ArticleName, Price and so on.... What I want to do is to make a new table that would hold some related Property records for each article. For example... Color, Weight, Length,.. This is not a problem, but what I want is to be able to change fields names for the second table (like PropertyID, PropertyName) and be able to search among those values from query. This is the most important part: ---------------------------------------------------------------- I would like to have them as a field in query from ...

auto filter data within pivot table
Hello, Does anyone know how can we filter data within a pivot table? or sort the data from ascending to decending order withing a pivot table??? Can we do that? It doesnt seems to work?! Thanks. Elaine. You can add fields to the page area, and use them to filter the pivot table's data. To sort a field, select a cell in a column, and click the A-Z button on the Excel toolbar. elaine wrote: > Does anyone know how can we filter data within a pivot table? or sort > the data from ascending to decending order withing a pivot table??? > Can we do that? > > It doesnt se...

Spontaneous 'Compile Error' In Queries
I've got a half-dozen queries that contain this expression: IssuerName: IIf(IsNull([tblIssuer].[IssuerID]),"[Issuer Unknown]",[tblIssuer].[IssuerName]) Haven't touched any of them, but suddenly they were all throwing "Compile error. in query expression 'IssuerName: IIf(IsNull([tblIssuer].[IssuerID]),"[Issuer Unknown]",[tblIssuer].[IssuerName])'." tblIssuer.IssuerID is still there in a link. My kneejerk was to somehow force recompiles of all the queries, so I did a Compact/Repair. After the compact/repair, all was well: the errors went away. C...

look way to map form at CRM frontend to tables at CRM database
How can I find out what tables which new record has been added on when I enter data through a form in CRM front end? I try to find way to map form in the CRM frontend to the tables at the CRM database at backend? It is CRM 3.0 Thank in advances for help! ...

Editing more than 1 table at a time with SQL.
Say I have two tables in a DB called TABLE1, and TABLE2. TABLE1 [IDNUMBER] [FIRSTNAME] 1 Ryan 2 Jennifer 3 Monica TABLE2 [IDNUMBER] [LASTNAME] 1 Johnson 2 Snipes 3 Smith My question is would I be able to use SQL in code to Edit, Delete or Insert data to both tables in one string rather than one table at a time? For instance what if I want to change both tables with a [IDNUMBER] = 2 to a [IDNUMBER] = 4, OR change the ...

Table headers in a list box (Custom Report)
I have a big table with many fields (more than fifty). They are not in one table but related. Now user's come with a requirement of a separate type of report everyday which has different fields. because of which i have to design a report for them with the required query. I was therefore wondering can i make a form which has two list boxes the one on left side will have all the fields of the table (only headers needed, not data). Users can select multiple no. of fields from that list box which will appear in another list box. Users should be able to then select the order of fi...

Do you need MS Access to query on an Access table?
I'm asking these questions because I have looked at a lot of stuff in the discussion groups and still confused. I am fairly good at Excel programming but haven't been able to get Excel and Access to talk. First, I have Office 2003 Professional at work and Office 2003 without Access at home. I want to work on developing Excel programming at home which will get data from Access to Excel with either MS Query or with programming. Is it possible to just have the .mdb files on the home computer for Excel to work with, or do I need the Access program too? The info I want ...

I am having trouble keeping numbers formatted in a Pivot Table
I have a pivot table that won't stay formatted. The data is formatted as currency in the worksheet that the Pivot Table is drawing off of. It shows up as a general number in the Pivot. I've tried formatting it in the Pivot but it doesn't stay when I refresh the data. "Preserve formatting" is checked on the Pivot table, so it SHOULD be preserving any formatting I apply to the pivot table, but that doesn't seem to be happening. I know I could record a macro to format this, but it is happening on multiple Pivot tables in the same workbook and that would be a pain ...

Pivot table field dropdown list reset #2
I would like to know if there is a way to reset a field dropdown list in a pivot table. In some field sometimes I delete data from the source and some items of the field will no longer be there. However, when I refresh the pivot table the dropdown list will still show the old items even if they're not in the source data anymore. I really need your help with this. Thanks a lot in advance for your help, Francisco Molina ...

How do I compare output data from 2 pivot tables in a graph forma.
I have a pivot table created for 2004 that shows the number of inspections we received on a particular day of the week (ie. 5 on saturdays, 2 on wednesdays, etc.) I am creating the same pivot table for 2005 and am looking for a way to graph the data (bar graph) of the pivot table from 2004 to compare with my 2005 data (ie. last year we received 5 inspections on saturdays, this year we received xx amount on saturdays). Is this even possible to do? I am using Office Pro 2003 and any suggestions would be greatly accepted. Doobi, One option is to combine the pivot tables for 2004 and...

How to purge all the Integration Manager log tables?
I went through and purged all the log files from Integration Manager using the procedure outlined in the IM User's Guide. However, after I was finished I looked at the tables in the im.mdb Access database and noticed that the following tables still have quite a few records in them: LogDocumentActivity LogActivity LogDocuments The Log table was empty, which makes sense since I purged all the logs. However, these other tables seem to have retained their records. Is there a way to purge these tables, too? -- Bud Cool, Accounting System Manager HDA, Inc. Hazelwood, MO GP 9.0, SP2 B...

Combining two types of functions. Select Query and DLookup
I would like to write a code that performs the following action: 1. If the value in a comboBox Project Number is 19912 2. Perform a select query on comboBox Task Number and comboBox National Site ID 3. If the value in a comboBox Project Number is not 19912 4. Perform a select query on comboBox Task Number and a DLookup () on comboBox National Site ID I tried the code below but it's not working. Any ideas on how to do this? If Me.cmbProjectNumber.Value="19912" then cmbNationalSiteID.RowSource = "SELECT DISTINCT [National Site ID] FROM [InScope Table] WHERE [Proje...

Extract from a table a value by interpolating
Hi, I'm a new with fancy things in excell and would very much appreciate the help. I have a table from which I want to extract values, but I want those values to be interpolated from what is in the table. How can I do that? Thanks, Maria hi, this is how i do it. Lets say your table is at A12 to B20 make another column at column C with this equaition =(B14-B13)/(A14-A13) A B C 12 2.000 1.000 0.5 13 4.000 2.000 1.0 14 6.000 4.000 1.0 15 8.000 6.000 1.0 16 10.000 8.000 1.0 17 12.000 10.000 1.0 18 14.000 12.000 1.0 19 16.000 14.00...

Pivot table seems to round my figures up!
I have a pivot table that works very nicely except for one thing - th data has come through all rounded up e.g. in the data source sheet one column has the following figures: 0.69 0.94 1 but when I create the pivot table it shows all the data as 1 I have formatted all the source cells as number wit 2 decimals and als the numbers cells in the pivot table - now they look like 1.00 I really need to be able to show the 0.69 and 0.94 as well as the 1.00 Is this possible please? Many thanks in advanc -- Message posted from http://www.ExcelForum.com RaeHippyChick, Make sure that you are...

Alternate grey/red shading with subtitles getting shaded differently (Word table)
Dear Experts: I got a telephone list/table with the following make-up: - Each name and corresponding phone number has got its own row. - The letters A-Z also have their own rows and are formatted bold - First name, last name is entered in one table cell - Phone numbers are entered in the table cells next to the "name" cell With any number rows of the table SELECTED would like the table to acquire the following FORMATTING: Rows A, B,C to Z (formatted bold) should have a grey shaded fill (RGB value 133, 133, 133) Rows immediately following these subtitles (A-Z) should...

Pivot Table into Report
I'm trying to generate a weekly report for management that summarizes Help Desk activity. I'm using Access DB that links to a CSV, generated from the support system (SQL not accessible). My problem is getting the figures I can generate in the Pivot Table into a form I can just pull up without further editing. I've already separated out by type of request (Break/Fix and Service Request), generating separate queries. What is requested are stats for various service queues(rows), by status (columns), grouped in specific ways; some queues are not grouped, some need ...

hierarchical data
Now that Jet is being developed in-house by the Access dev team can we expect to see some movement on its querying capabilities. I for one would love to see some functionality to better aid in representing hierarchical data. SQL Server 2005 introduced "Recursive CTE's" will Access follow suit with a similar concept? If I could request only one future feature this would be it! ...

Getting query data for a report
If I have the query "UniqueCounts" and following query results: Count ServicesType 2 Food Basket 99 Men's Dorm 84 Personal Needs 5 PN Family Pack 14 Program Dorm 7 Temporary ID What would I put in a text field in a report to get the query results from Count for the ServicesType "Men's Dorm". (Which should be the value 99.) Thanks! you would want to put in 'Men's Dorm' as the criteria in the query....on in a new query that is applied to this query ...

problem in a form from crosstab query
Hi, I have a form from a crosstab query. On that form, I've added some fields to make "on the fly" calculations but they remain blank if one of the values returned from the query and used for calculation is = 0. What I can do? Thks in advance Use Nz() around each field, to replace null with zero. For example, instead of: =[A] + [B] + [C] use: =Nz([A],0) + Nz([B],0) + Nz([C],0) Here's another suggestion for getting a row total in a crosstab: http://allenbrowne.com/ser-67.html#RowTotal -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Acces...