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

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

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

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

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

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