Re: Crosstab data entry? (Was: Re: Query question)

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: PC Data*** (nospam_at_nospam.spam)
Date: 12/17/04 

Date: Fri, 17 Dec 2004 16:03:19 GMT

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

--
                                        PC Data***
Your Resource For Help With Access, Excel And Word Applications
                              resource@pcdata***.com
                                 www.pcdata***.com
"Johan Myrberger" <johan.myrberger@removeeverythingbuthome.se> wrote in
message news:41C2BD90.28C1@removeeverythingbuthome.se...
> 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
Quantcast