Need help creating a report in access

[Deevusone]

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!

 

[Olduke]

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!

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.

 

[BruceM]

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!

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.

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:
=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.