table manipulation

Hi all

my db has a single table, Table1. Table1 is as follows:


AUTHOR;TITLE;PUBLISHER

rows are not unique; i.e. I can have more than 1 row with the same author.

What would be the SQL code to generate a table2 with the following 
structure:

AUTHOR;TITLE-PUBLISHER;...;TITLE-PUBLISHER

where every author now has only 1 row and titles and publishers for 
author are appended to the right as as may fields as necessary?

thanks!




0
hugh
2/16/2008 11:25:55 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
498 Views

Similar Articles

[PageSpeed] 24

hugh wrote:
> Hi all
>
> my db has a single table, Table1. Table1 is as follows:
>
>
> AUTHOR;TITLE;PUBLISHER
>
> rows are not unique; i.e. I can have more than 1 row with the same
> author.
> What would be the SQL code to generate a table2 with the following
> structure:
>
> AUTHOR;TITLE-PUBLISHER;...;TITLE-PUBLISHER
>
> where every author now has only 1 row and titles and publishers for
> author are appended to the right as as may fields as necessary?
>
> thanks!

A crosstab query might be able to give you that.  Making such a table would be a 
horrible idea.  The result would be a spreadsheet, not a database table.

-- 
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com



0
Rick
2/16/2008 11:48:20 PM
Reply:

Similar Artilces:

Table Linking
I'll start out the question with what I'm trying to accomplish. Basically, I've created a form that populates data into a table (lets call it table 1). I want to be able to reference this table against a master table (call it table 2) and return the matching results. The form has a command button that runs a query that links these two tables and returns the resulting matches. The problem I am having is that if a user wanted to leave one of the fields blank on the populating form the query will not return anything. So lets say the form asks for State, City, County and Zi...

pivot table 2007 formatting does not stay when refreshing
Hi, In the file, I have multiple tabs each having its own pivot table. When I have to refresh one of the tabs, the formatting changes on all the other tabs changes as well, most importantly the cells that have zero, I have formatted this to hypens. These cells always goes back to zero's. Very frustrating! I checked the box that says preserves formatting but it seems that the formatting is not preserved. What is included in formatting? Can someone explain why this is happening. Thanks ...

hide 0 values in pivot table (2003)
Hi I can not find how to hide 0 fields in pivot table in office 2003. thanks tim You can hide blank values but not zero values (at least not built in the pivot table options), you can hide all zero values under tools>options>view and uncheck zero values. Debra Dalgleish has posted code for hiding zero fields http://tinyurl.com/ca7gr -- Regards, Peo Sjoblom "Tim" <adslk4at@tpg.com.au> wrote in message news:428801de$1@dnews.tpgi.com.au... > Hi I can not find how to hide 0 fields in pivot table in office 2003. > > thanks tim > ...

Table Changes in GP 9.0 SP1
We can't seem to find any documentation whether or not there are any table changes in SP1. Does anyone know if there are any? If so where can we find the information? Thanks, Sharon Standard policy is not to make table changes in Service Packs. David Musgrave [MSFT] Senior Development Consultant Escalation Engineer - Great Plains Microsoft Dynamics Support - Asia Pacific Micorosoft Dynamics (formerly Microsoft Business Solutions) http://www.microsoft.com/Dynamics mailto:dmusgrav@online.microsoft.com Any views contained within are my personal views and not necessarily Microsof...

Pull Unique Values From a List/Table
How can I pull unique values from a table? I track vendors who might sell multiple items, but I only need to show the vendor once so I can pull other information. There are also may be blanks in the rows if the vendor is inactive. Thanks! Let me add one more twist to this problem: The unique values also need to filter out where it may be 'VendorA - West' and 'VendorA - East' and combine them into one record. I only need it to show VendorA and not both records East and West (same company, just different drop ship location). Thanks! "Karl Burrows" <kfb1@spa...

Delayed Response Time Forms/Reports 2003 Linked Table
I've been trying to create forms and reports (queries also) in Access 2003. I linked an Excel file located on my server and the process is incredibly slow. I get a significant lag when I try to adjust properties of any type for a data field or label or any other object. When I import the excel file it works fine. Is there anything I could possibly do to fix this issue. It seemed to be working fine last week, but this week I'm having trouble. Thanks. ...

Pivot tables to count unique records only
Hi All, I have some data which is sometimes repeated. I want this data to feed directly into a pivot table and only count the unique records as theres records in there which appear about 8 times. When you do count on a reference number it counts it each time, show its showing about 131 records when its truly 22 ! Can anyone help please? -- Adam ----------- Windows 98 + Office Pro 97 A pivot table won't calculate a unique count. However, you could add a column to the database, then add that field to the pivottable. For example, if you want to count the customers in column A, use...

table lines making dashed
I was trying to make a verticle line in a table dashed, is there a way to do this. I didn' t find one in, format table. thanks bruce Using basic shapes and a bit of tweaking you can. -- JoAnn Paules MVP Microsoft [Publisher] "Bruce" <notreally@idontwantoo.com> wrote in message news:eyfYBn7mGHA.2056@TK2MSFTNGP05.phx.gbl... >I was trying to make a verticle line in a table dashed, > is there a way to do this. I didn' t find one in, format table. > > thanks bruce > Oops - wrong reply to this question Try creating the table in Word then pastin...

Why size of email with pivot table is so big?
I only send one sheet of pivot table to users, and i found every email is about 1MB, why the size is so big? Did it send with raw data hiding in somewhere? ...

Erwin diagrams for HR Tables?
I am helping some of our HR people write Crystal Reports again the HR tables in GP 9. I am aware of the various diagrams available in the SDK, but there don't appear to be any for the HR tables. Does anyone know a source for Database Diagrams for HR? Beyond the information provided with the standard GP SDK, I am not sure you will find anything else. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com "DavidM" wrote: > I am helping some of our HR people write Crystal Reports again the HR tables > in G...

Read only table from replicated database
Our replicated database recently became corrupted (for reason or reasons undetermined) and data was lost. Indeed some fields from one table were deleted! As I was unable to restore the data to the Master Copy, I imported the tables (back end) into a new database and this was successfully completed. However, one particular form is served by an underlying query which brings together three tables. Now here's the strange bit: when the query is run in the front end database using the corrupted database, the query works fine and records can be added to the various tables through the q...

Creating a Report from one column of a table
I am not sure if this is possible to do but am hoping it is. I've created a database that gives information on clients and jobs. People fill out a form of a person details and tick different jobs - theres about 50 to choose, but no more than 10 would be picked for one person. Once they are ticked, it is shown in the table all correctly. What i am hoping to do is produce a report for each job - over 50 reports. But the report only shows one of the column/ticked boxes. For example, if theres 20 people and out of them 5 have the job 'retail' ticked, the report for Retail will only...

How do I get a web query to auto-refresh before a pivot table aut.
I have html data stored on an intranet site that I want to auto-refresh when the user opens the Excel file. I then want a pivot table in the file to also auto-refresh. Excel is updating the pivot table before it's refreshing the data which is the reverse order of what I need. I can't figure out how I change the order of the auto tasks. Thanks. ...

How can I select pivot table row totals to fill color them
I have a pivot table with total rows. Other people in my team at work can hover over the left edge of one of the total rows and all total rows get selected so that a fill color can be applied. It does not work for me From the PivotTable tool bar, Click on the PivotTable dropdown, select - Enable selection. -- Best Regards, Luke M "Ruth5155" <Ruth5155@discussions.microsoft.com> wrote in message news:47441F7C-709C-4AC6-987A-5806C9F6E7DD@microsoft.com... >I have a pivot table with total rows. Other people in my team at work can > hover over the left e...

Displaying Total Amount Of Records In A Table
I would like to display the amount of records that are in a table on the form - do I use a text box and assign the control source somehow...? Thank you in advance You could use something like below in the control source on an unbound text field in the form. I would work best if you put in the primary key field name in FieldName. DCount("[FieldName]", "TableName") -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "dmills" wrote: > I would like to display the amount of records that...

What table contains the data displayed in 'My Tasks' view?
MS Project Server 2007 Does anyone know what table contains the data displayed in My Tasks? -- JenGoSto Not sure which table it is, but when utilising the reporting db you can get this info of the EPM task user view. Hope this helps -- Marc Soester [MVP] http://www.i-pmo.com.au "JenStover" wrote: > MS Project Server 2007 > > Does anyone know what table contains the data displayed in My Tasks? > > > -- > JenGoSto I am trying to pull the My Task information for one or more resources for a specific period. I've used...

Alter table Add column to merge publication
We are currently on SQL Server 2008 and I would like to add a column to a table that exists in our merge replication. Below is what I ran in our test environment Alter Table TableName Add ColumnNameTypeID uniqueidentifier Null The column was added, but the Default value or Binding was set to NewID() and the RowGuid property was set to Yes. I would like the Default value or Binding not set to anything and the RowGuid set to No. Is there a way to do this using the Alter Table command? The new column added is a FK to another table. I am thinking after the column is adde...

Mathematical Calculations in a form linked back to a table
Hello I palnning on implementing calculation between two fields, and the result within another field. Suppose I have three fields called A B and C respectively. In the form I plan on Inputing a value for[A] and Inputing a value for [B]. Now, Field [C] will calculate [A]-[B]. When filed [C] is calculated, it is not in the table, like A and B are. I would like to know how to solve this problem. It is rarely necessary to store a calculated value in a table in Access. Much more commonly, a form (as you are using) or a query is used to do the calculation "on the fly", as it were. ...

Pivot table ranking
Is there a way to 'rank' the rows in a pivot table (form)? For example, I have a set of data: Ice Cream Total Sold Flavor1 500 Flavor2 1000 Flavor3 200 etc... I can see my best seller by sorting (descending) on the "Total Sold" field. Is there any way (calculated field?) to put an actual ranking next to the Flavor field? But when the Flavors change during the sort, the Ranking field should remain static, so the ranking is new with new/updated data. Can this be done within the Pivot Table or is there another (better?) way of doing this (Cros...

Formatting Totals on Pivot Table
I have a pivot table with customers then product for rows, Month for column header, and total dollars for the details. Is there a way I can change the font automatically for all Customer subtotals to bold. The plain border around the subtotals just is not enough. ...

insert into when not exist in table
Hi using sql 2005. I want to insert into table A values(x,y) from table b. However only when values x,y do not exist in table A? I'm sure that this is really simply... I'm timing out when alternating between <ahhh> brain freeze </ahhh> and <ahhh> max complicated </ahhh> Many thanks, Jonathan INSERT INTO A (x, y) SELECT x, y FROM B EXCEPT SELECT x, y FROM A; or INSERT INTO A (x, y) SELECT x, y FROM B WHERE NOT EXISTS(SELECT * FROM A WHERE A.x = B.x AND A.y = B.y); -- ...

data in pivot table
My data is automatically being counted when I don't want it to be. I just want the data displayed in the data section, but the heading keeps on coming up as Count of... How do I just get the data to be displayed? You can select the cell containing the "Count of" and overtype it with something else. On Wed, 6 Oct 2004 14:57:01 -0700, xausaj <xausaj@discussions.microsoft.com> wrote: >My data is automatically being counted when I don't want it to be. I just >want the data displayed in the data section, but the heading keeps on coming >up as > >...

Mail Merging a Table
I work for a church and I am trying to create a donation report for 2004. The data I have is in a CSV in the following format: Name, Date of Donation, Amount of Donation, Various Address Fields Does anyone have any suggestions on how I might perform a mail merge on this data so that all donations by one particular person will be listed in the same document? For example, if a particular person donated 5 times during the year I would like to create a document with the following list: Date1 Amount1 Date2 Amount2 Date3 Amount3 Thanks in advance for any guidance you can give me in how I can d...

View Pivot Table Source data as a Data Table
Hello kind & helpful people, I am using Excel 2007 I have a pivot table and I want to be able to look at all of the data that it represents at once. That is, I want to see the original data table that was used to create the Pivot Table. I have located the source data specified in the "PivotTable Tools » Options » Data » Change Data Source" area of the ribbon. That value is: '\Documents and Settings\xgraham\My Documents\This Data\Pivots for Review\Markets\[Updated 2009 Data.xls]Raw Data'!$A$25:$H$11263 However, the user referred to th...

Relink to tables with password on BE
I'm using Dev Ashish's code to relink tables and it works great on a non-protected db. The issue I am having is that when I add a Password to the BE, I can't get it to work. Where do I need to put the password in Dev's code to get it to relink? I'm thinking it needs to be in the connection and in the table link but I can't get it to work. I added the password to the following code: .Connect = ";Database=" & strDBPath & ";PWD=xxx10" but it is breaking on this which is before the code above ...