Query question

[Johan Myrberger]

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

 

[LeAnne]

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

 

[Johan Myrberger]

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]

Hi Johan,

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

 

[PC Datasheet]

A crosstab query is not updateable and therefore can not be used as the
basis of a form.

 

[Duane Hookom]

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.