If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query question
I currently have two tables:
* CustomerData, with fields CustID, Country, and some other misc fields * CountryData, with fields Country, Year and Population Based on this I'm trying to make a query from Excel that would provide the following type of output: CustID Country 2001 2002 2003 2004 (header line) CustID1 Countr1 pop-01 pop-02 .... (data..) Instead I am only able to create output like: CustID Country Year Population CustID1 Countr1 2001 pop-01 CustID1 Countr1 2002 pop-02 ... CustID2 Countr2 2001 pop-01 CustID2 Countr2 2002 pop-02 So: - How can I create the wished output? - Or is this not possible with the current table design, should I redesign the CountyData table to have a field for each year? Regards /Johan Myrberger |
#2
|
|||
|
|||
Johan Myrberger wrote:
I currently have two tables: * CustomerData, with fields CustID, Country, and some other misc fields * CountryData, with fields Country, Year and Population Based on this I'm trying to make a query from Excel that would provide the following type of output: CustID Country 2001 2002 2003 2004 (header line) CustID1 Countr1 pop-01 pop-02 .... (data..) Instead I am only able to create output like: CustID Country Year Population CustID1 Countr1 2001 pop-01 CustID1 Countr1 2002 pop-02 .. CustID2 Countr2 2001 pop-01 CustID2 Countr2 2002 pop-02 So: - How can I create the wished output? - Or is this not possible with the current table design, should I redesign the CountyData table to have a field for each year? Hi Johan, NonononoNO. Your design for tblCountryData is fine. What you want to do can easily be achieved using a Crosstab query. For example: TRANSFORM First(Countries.Pop) AS FirstOfPop SELECT Customers.CustomerID, Countries.CountryID FROM Customers INNER JOIN Countries ON Customers.CountryID = Countries.CountryID GROUP BY Customers.CustomerID, Countries.CountryID PIVOT Countries.CensusYear; This will create a recordset with CustomerID and CountryID as row headings, one column for each CensusYear (note the fieldname change..."Year" is a reserved word in Access, meaning it refers to a specific function), and population values within the matrix. Don't worry about my use of the FIRST() function; it's just a sneaky way of getting Access to pivot on the values you want. hth, LeAnne |
#3
|
|||
|
|||
Thank you! The pointer to crosstab queries made things clear to me!
When you have a crosstab view (in this case of a single table, the CountryData table below) - is it possible to use this type of view for data entry? I have tried to use the crosstab query in a form, and found the properties "Data Entry" and "Allow additions" and set them to Yes. However I am not able to modify or add data... regards /Johan Myrberger LeAnne wrote: Johan Myrberger wrote: I currently have two tables: * CustomerData, with fields CustID, Country, and some other misc fields * CountryData, with fields Country, Year and Population Based on this I'm trying to make a query from Excel that would provide the following type of output: CustID Country 2001 2002 2003 2004 (header line) CustID1 Countr1 pop-01 pop-02 .... (data..) Instead I am only able to create output like: CustID Country Year Population CustID1 Countr1 2001 pop-01 CustID1 Countr1 2002 pop-02 .. CustID2 Countr2 2001 pop-01 CustID2 Countr2 2002 pop-02 So: - How can I create the wished output? - Or is this not possible with the current table design, should I redesign the CountyData table to have a field for each year? Hi Johan, NonononoNO. Your design for tblCountryData is fine. What you want to do can easily be achieved using a Crosstab query. For example: TRANSFORM First(Countries.Pop) AS FirstOfPop SELECT Customers.CustomerID, Countries.CountryID FROM Customers INNER JOIN Countries ON Customers.CountryID = Countries.CountryID GROUP BY Customers.CustomerID, Countries.CountryID PIVOT Countries.CensusYear; This will create a recordset with CustomerID and CountryID as row headings, one column for each CensusYear (note the fieldname change..."Year" is a reserved word in Access, meaning it refers to a specific function), and population values within the matrix. Don't worry about my use of the FIRST() function; it's just a sneaky way of getting Access to pivot on the values you want. hth, LeAnne |
#4
|
|||
|
|||
Hi Johan,
Johan Myrberger wrote: Thank you! The pointer to crosstab queries made things clear to me! You're welcome. When you have a crosstab view (in this case of a single table, the CountryData table below) - is it possible to use this type of view for data entry? I have tried to use the crosstab query in a form, and found the properties "Data Entry" and "Allow additions" and set them to Yes. However I am not able to modify or add data... Data in a crosstab query are not updatable. It sounds like what you are trying to do is create a spreadsheet-like data entry form, and want users to enter their data using this pseudo-table. If I am correct, this is not a good approach. I suggest basing your data entry form based on the table (or if more than 1 table, on a simple select query joining the tables) in which the data will reside. To *display* data in a wide-flat configuration, create a crosstab query to pivot data in the table(s), and then create a *report* bound to the xtab. Search the Help Index for "crosstab queries, reports" for more information. hth, LeAnne |
#5
|
|||
|
|||
A crosstab query is not updateable and therefore can not be used as the
basis of a form. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com "Johan Myrberger" wrote in message ... Thank you! The pointer to crosstab queries made things clear to me! When you have a crosstab view (in this case of a single table, the CountryData table below) - is it possible to use this type of view for data entry? I have tried to use the crosstab query in a form, and found the properties "Data Entry" and "Allow additions" and set them to Yes. However I am not able to modify or add data... regards /Johan Myrberger LeAnne wrote: Johan Myrberger wrote: I currently have two tables: * CustomerData, with fields CustID, Country, and some other misc fields * CountryData, with fields Country, Year and Population Based on this I'm trying to make a query from Excel that would provide the following type of output: CustID Country 2001 2002 2003 2004 (header line) CustID1 Countr1 pop-01 pop-02 .... (data..) Instead I am only able to create output like: CustID Country Year Population CustID1 Countr1 2001 pop-01 CustID1 Countr1 2002 pop-02 .. CustID2 Countr2 2001 pop-01 CustID2 Countr2 2002 pop-02 So: - How can I create the wished output? - Or is this not possible with the current table design, should I redesign the CountyData table to have a field for each year? Hi Johan, NonononoNO. Your design for tblCountryData is fine. What you want to do can easily be achieved using a Crosstab query. For example: TRANSFORM First(Countries.Pop) AS FirstOfPop SELECT Customers.CustomerID, Countries.CountryID FROM Customers INNER JOIN Countries ON Customers.CountryID = Countries.CountryID GROUP BY Customers.CustomerID, Countries.CountryID PIVOT Countries.CensusYear; This will create a recordset with CustomerID and CountryID as row headings, one column for each CensusYear (note the fieldname change..."Year" is a reserved word in Access, meaning it refers to a specific function), and population values within the matrix. Don't worry about my use of the FIRST() function; it's just a sneaky way of getting Access to pivot on the values you want. hth, LeAnne |
#6
|
|||
|
|||
You could create an unbound form for user input. You would need code to pull
the values from your table and place in the controls. Following a "Save" by the user, the values would be update or appended to the table. -- Duane Hookom MS Access MVP "Johan Myrberger" wrote in message ... Thank you! The pointer to crosstab queries made things clear to me! When you have a crosstab view (in this case of a single table, the CountryData table below) - is it possible to use this type of view for data entry? I have tried to use the crosstab query in a form, and found the properties "Data Entry" and "Allow additions" and set them to Yes. However I am not able to modify or add data... regards /Johan Myrberger LeAnne wrote: Johan Myrberger wrote: I currently have two tables: * CustomerData, with fields CustID, Country, and some other misc fields * CountryData, with fields Country, Year and Population Based on this I'm trying to make a query from Excel that would provide the following type of output: CustID Country 2001 2002 2003 2004 (header line) CustID1 Countr1 pop-01 pop-02 .... (data..) Instead I am only able to create output like: CustID Country Year Population CustID1 Countr1 2001 pop-01 CustID1 Countr1 2002 pop-02 .. CustID2 Countr2 2001 pop-01 CustID2 Countr2 2002 pop-02 So: - How can I create the wished output? - Or is this not possible with the current table design, should I redesign the CountyData table to have a field for each year? Hi Johan, NonononoNO. Your design for tblCountryData is fine. What you want to do can easily be achieved using a Crosstab query. For example: TRANSFORM First(Countries.Pop) AS FirstOfPop SELECT Customers.CustomerID, Countries.CountryID FROM Customers INNER JOIN Countries ON Customers.CountryID = Countries.CountryID GROUP BY Customers.CustomerID, Countries.CountryID PIVOT Countries.CensusYear; This will create a recordset with CustomerID and CountryID as row headings, one column for each CensusYear (note the fieldname change..."Year" is a reserved word in Access, meaning it refers to a specific function), and population values within the matrix. Don't worry about my use of the FIRST() function; it's just a sneaky way of getting Access to pivot on the values you want. hth, LeAnne |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Pass Through Query Question | Joe Williams | Running & Setting Up Queries | 4 | December 2nd, 2004 06:39 PM |
Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |
Access 97 - Multiple Table Query Question | Jeff Homan | Running & Setting Up Queries | 6 | October 14th, 2004 07:04 PM |
Query Question | nick | Running & Setting Up Queries | 2 | October 5th, 2004 06:02 PM |
Query management question | Dana809904 | Running & Setting Up Queries | 2 | September 28th, 2004 07:18 AM |