Re: Need help creating a report in access
- From: "BruceM" <bamoob@xxxxxxxxxxxxxxxx>
- Date: Tue, 19 Feb 2008 07:33:07 -0500
"Olduke" <Olduke@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:8D301014-D775-4CE1-B621-0E43FB3B6751@xxxxxxxxxxxxxxxx
Sums and counts can also be done in a text box in the report or group header or footer, or through VBA. For instance, the control source of a text box in the report footer could be:
"Deevusone" wrote:
Hi,
I'm a newb when it comes to access, so please bare with me. I'm trying
to generate a report for work and require the following information to
be displayed. I know i can use excel to create it but it's too manual
to do on a weekly basis.
I start off with importing the data from excel. The data from the
excel spreadsheet has several columns of information, ie: customer
account#, name, address, etc.
After importing the data from excel into a new table, i need to do a
count of all the data from the table. The tricky part (for me that is)
to this is that i need to group the data from one of the columns and
perform a count there. It's a little hard to explain.
for example, in the column that i'm trying to count has the following
information
dataset #1
invalid account number
account number not found
account number incorrect
dataset #2
rate invalid
tariff invalid
price not setup
i want to group dataset#1 and put them into a group called "Account
number issues" and produce the total number that falls under that
category.
then i want to group dataset#2 and put them into a group called
"Rate/Price issues" and produce the total number that falls under that
category.
But there's another issue to this. some of the data has the same info
which might make the record look unique, ie:
rate invalid; account number not found
rate invalid; price not setup
will the above data give me inaccurate number account?
the end result of the data should look something like this
AAA
account number issues: 10
rate price issues: 4
BBB
account number issues: 2
rate price issues: 23
CCC
account number issues: 56
rate price issues: 1
and how can i create this type of report?
Please help this newb!
--
Deevusone
In Access the place to do all of your counting adding summing etc. is in a
query.
Create a query using the wizard. Use the first table to create it (dataset
1).
Use the fields that will give you the counts you need (which I assume from
your post is account number).
When you're finished, with the query in design view starting typing in the
first blank field on the right. Enter:
AccountNoInvalid: Count([accountnumber])
In the Criteria line underneath enter the conditions that would make the
account invalid.
When you run the query in the number of invalid accounts should be listed.
Do the same with your other Account Issues.
Then create a report from the query that uses the data you want in the report.
=Count([AccountNumber])
Also, a report has sorting and grouping capabilities (View > Sorting and Grouping) that will let you group records according to criteria you choose. Within a group, Count or DCount can be used in an expression in a text box in the group footer.
A query can be used to establish criteria, and do perform counts, sums, and so forth, but it should be pointed out that a query is just one of the options, depending on the requirements. Records in a form cannot be sorted and grouped as they are in a report, so a query may be the only option, but a report offers a lot of built-in flexibility.
.
- References:
- Need help creating a report in access
- From: Deevusone
- RE: Need help creating a report in access
- From: Olduke
- Need help creating a report in access
- Prev by Date: Re: A really really simple report
- Next by Date: problems with microsoft office -- power point! Please help.
- Previous by thread: RE: Need help creating a report in access
- Next by thread: Re: using macros to open specific record in a form
- Index(es):
Relevant Pages
|
