Show records as columns (not rows) in form

[Michael]

I have a one-to-many relationship (tblCompany) to
(tblFinancialData) with fields such as Year, Revenue, etc.

This data should be viewed in a columnar format rather
than a row format in a datasheet form. The goal is for a
user to input the data by column, not year.

Anyone know how to switch a datasheet form to show data
in columns instead of rows, with the field names as the
first column in the datasheet? I've searched and
searched, with no luck. See representation below, and
thanks in advance.

Field Name
Year 2000 2001 2002
Revenue $33.1 $40.1 $45.6
Net Income $0.5 $1.8 $6.4

 

[Jeff Boyce]

Michael

From your description, it sounds like you are using a spreadsheet, rather
than MS Access.

If you are using MS Access, the data structure sounds decidedly
not-well-normalized. This will cause you headaches in setting up forms and
data entry, as you've already found.

Finally, I believe Access treats "Year" as a reserved word -- what you mean
may not match how Access is interpreting that if you're using it as a field
name.

 

[Thomas]

Hello if you just want to change to
view of the form from DataSheet view
to Single Form ?

Open the Form in Design View
Upper left corner of the form
you will see a small black square
DblClick on it
Under the (Tab) Format you will see
Default View (Select) Single Form
That will change the form from DataSheet to a Single Form

Hope that helps
Thomas

 

[Michael]

Jeff, thanks for your post. What I'm doing is VERY easy
in Excel, showing data in columnar format:

2000 2001 2002 2003 ...
Revenue 56 56 65 68 ...
Gross Profit 23 23 26 32 ...
CapEx 2 1 3 0 ...
Op. Expenses 12 8 20 5 ...

You will note that if I were to store this data in
Access, I would have the following data fields: Year,
Revenue, GrossProfit, CapEx, OpExpenses. However, to
input this information in an Access table
(tblFinancials), I have to input each record in a row, as
follows:

2000, 56, 23,2, 12
2001, 56, 23, 1, 8
.....

I'm comfortable having our research team input data this
way and so I think my question has now shifted to one of
a Report output, whereby the output would look similar to
the original example shown above, where data is shown in
columnar format. The relationship is that a Company
would have many tblFinancials records.

Since this database will be used for several years, as we
look at Companies in 2006, the years will adjust to be
2003 - 2009 vs. today's 2000 - 2006. Thus, the
field 'Year' has to be inputted so that I can perform
financial analysis per year and across years. I would
like to avoid having to use Excel.

In summary, my question is: How can I show this data in
columnar format in a report rather than in a row format?

Thanks again,

Michael

 

[Douglas J. Steele]

Actually, the proper way to store that in Access would use only 3 fields
(something like AcctYear, ValueType and Value), and your database would be
something like

2000, "Revenue", 56
2000, "GrossProfit", 23
2000, "CapEx", 2
2000, "Op. Expenses", 12
2001, "Revenue", 56
2001, "GrossProfit", 23
2001, "CapEx", 1
2001, "Op. Expenses", 8

(Of course, you'd probably have a ValueType table, so that you'd be storing
a number rather than the literal Value Types)

You can use a Cross-tab query to turn that data into the format you're
looking for for reporting purposes.

And Jeff's point was that you shouldn't name a field Year, since Year is a
reserved word.