really really stuck

  • Thread starter Thread starter Stephanie
  • Start date Start date
S

Stephanie

I've tried to understand the online help but I just don't get it. I need a
report that filters out and summarizes data. Nothing really fancy. If
someone would look at the attached and do a couple small filters and puts it
in a report I'm sure I could copy and manipulate it to do what I really need
to do. I just need an example much simpler than the one that comes with
Access.

Could somebody please look at a file I email them and do a simple query?

I'll personally come shine the shoes of whoever can give me this example.
Thank you
 
Stephanie, If you use the report wizard in Access, it will ask the relevant
questions, especially about where do you wish to break the information.
Normally, you would want to add totals for each customer, or each invoice.
Then also get grand totals at the end of the report. The wizard can do that
for you. Just make sure you based the report on either a table or a query
that contains all the information you need. For example, make a query
including the details, the table with company or person's names, and other
relevant data that may be spread out in several tables. Once you get the
query producing more or less what you need (it will be many duplicate
information, etc. but consistent with the basic fields of information, then
run the report wizard based on that query. Add the break points to get
subtotals, and header/footer for the section and that should get the full
report.
By the way, there was nothing attached to your posting.

--
Victor Delgadillo [MVP Access]
Miami, Florida

Consultas al grupo, asi todos nos beneficiamos.

_
 
I tried the report wizard. It lists all records. How do I filter them based
on a character within the string?

Victor Delgadillo said:
Stephanie, If you use the report wizard in Access, it will ask the relevant
questions, especially about where do you wish to break the information.
Normally, you would want to add totals for each customer, or each invoice.
Then also get grand totals at the end of the report. The wizard can do that
for you. Just make sure you based the report on either a table or a query
that contains all the information you need. For example, make a query
including the details, the table with company or person's names, and other
relevant data that may be spread out in several tables. Once you get the
query producing more or less what you need (it will be many duplicate
information, etc. but consistent with the basic fields of information, then
run the report wizard based on that query. Add the break points to get
subtotals, and header/footer for the section and that should get the full
report.
By the way, there was nothing attached to your posting.

--
Victor Delgadillo [MVP Access]
Miami, Florida

Consultas al grupo, asi todos nos beneficiamos.

_
Stephanie said:
I've tried to understand the online help but I just don't get it. I need a
report that filters out and summarizes data. Nothing really fancy. If
someone would look at the attached and do a couple small filters and
Click to expand...
puts
it
in a report I'm sure I could copy and manipulate it to do what I really need
to do. I just need an example much simpler than the one that comes with
Access.

Could somebody please look at a file I email them and do a simple query?

I'll personally come shine the shoes of whoever can give me this example.
Thank you
Click to expand...
Click to expand...
 
I think I could do this if someone would walk me through filtering the data
so it only showed records with a certain character in position 6. I think
that's done in a query, right.

I admit it, I'm stupid. But if someone would let me email a file to them or
give me step-by-step instructions here I could do it.

If you have records in this format:
aaaaaaa
bybbbGb
cccccGc

How do you make a query show only those records with no G in position 6?
How do you make a query that shows no records with a Y anywhere?



Stephanie said:
I tried the report wizard. It lists all records. How do I filter them based
on a character within the string?

Victor Delgadillo said:
Stephanie, If you use the report wizard in Access, it will ask the relevant
questions, especially about where do you wish to break the information.
Normally, you would want to add totals for each customer, or each invoice.
Then also get grand totals at the end of the report. The wizard can do that
for you. Just make sure you based the report on either a table or a query
that contains all the information you need. For example, make a query
including the details, the table with company or person's names, and other
relevant data that may be spread out in several tables. Once you get the
query producing more or less what you need (it will be many duplicate
information, etc. but consistent with the basic fields of information, then
run the report wizard based on that query. Add the break points to get
subtotals, and header/footer for the section and that should get the full
report.
By the way, there was nothing attached to your posting.

--
Victor Delgadillo [MVP Access]
Miami, Florida

Consultas al grupo, asi todos nos beneficiamos.

_
Stephanie said:
I've tried to understand the online help but I just don't get it. I
Click to expand...
Click to expand...
need
a puts really
need
Click to expand...
Click to expand...
 
I think I could do this if someone would walk me through filtering the data
so it only showed records with a certain character in position 6. I think
that's done in a query, right.

I admit it, I'm stupid. But if someone would let me email a file to them or
give me step-by-step instructions here I could do it.

If you have records in this format:
aaaaaaa
bybbbGb
cccccGc

How do you make a query show only those records with no G in position 6?
Click to expand...

Use a criterion of

NOT LIKE "?????G?"
How do you make a query that shows no records with a Y anywhere?
Click to expand...

Use a criterion of

NOT LIKE "*Y*"

Best of all - STOP trying to pack seven different pieces of
information into one field! In a relational database, each field
should be "atomic" - have one and only one value. If you redesign your
tables so that they are properly normalized, your queries will be much
simpler!
 
Ureka!
Thanks

John Vinson said:
Use a criterion of

NOT LIKE "?????G?"


Use a criterion of

NOT LIKE "*Y*"

Best of all - STOP trying to pack seven different pieces of
information into one field! In a relational database, each field
should be "atomic" - have one and only one value. If you redesign your
tables so that they are properly normalized, your queries will be much
simpler!
Click to expand...
 
Still stupid. The Not Like thing works great, but I don't know how to get
Access to tell me the total number of records that meet the criteria. If the
result of the query is 50 of the 100 records what do I need to do to get my
report to show 50? What it does now is list all of the records found. I can
count to 50 but the report is going to end up with several dozen counts from
several dozen queries and I need to run it several times a day. I thought I
could click the Sum button and change the Total to Count. It gives me a type
mismatch error. The field I'm trying to count is formated as text but I
thought Count would count the total number of records. What am I doing
wrong????
 
I'm trying to get this to work on a report. I looked at the Northwind
database and what I'm trying to do now is add a Textbox that has
=Count([MyQueryName]) in the Control Source line of Properties. That's what
it looks like Northwind is doing. What I get, though is, #Name?

What the simple trick that I'm missing here? One thing I don't see is how
Access would know to use my Query of the name MyQueryName for the input. I
could just as easily have a table with that name - how would Access know
which one to use? Seems to me I'm not pointing to the right place. My erport
has nothing in it but the text box where I want to see the total number of
records found in the query. I really need help.
 
Stephanie said:
I'm trying to get this to work on a report. I looked at the Northwind
database and what I'm trying to do now is add a Textbox that has
=Count([MyQueryName]) in the Control Source line of Properties. That's what
it looks like Northwind is doing. What I get, though is, #Name?

What the simple trick that I'm missing here? One thing I don't see is how
Access would know to use my Query of the name MyQueryName for the input. I
could just as easily have a table with that name - how would Access know
which one to use? Seems to me I'm not pointing to the right place. My erport
has nothing in it but the text box where I want to see the total number of
records found in the query. I really need help.
Click to expand...

Bind the report to the query and then use =Count(*) in the Report Header or
Footer.
 
How do you Bind the report to the query and what does it mean??
Thanks

Rick Brandt said:
Stephanie said:
I'm trying to get this to work on a report. I looked at the Northwind
database and what I'm trying to do now is add a Textbox that has
=Count([MyQueryName]) in the Control Source line of Properties. That's what
it looks like Northwind is doing. What I get, though is, #Name?

What the simple trick that I'm missing here? One thing I don't see is how
Access would know to use my Query of the name MyQueryName for the input. I
could just as easily have a table with that name - how would Access know
which one to use? Seems to me I'm not pointing to the right place. My erport
has nothing in it but the text box where I want to see the total number of
records found in the query. I really need help.
Click to expand...

Bind the report to the query and then use =Count(*) in the Report Header or
Footer.
Click to expand...
 
SueB said:
How do you Bind the report to the query and what does it mean??
Thanks
Click to expand...

The Report has a RecordSource property. Set that to the query's name.
That is what tells the Report where to get its data.
 
RecordSource? Do you mean Control Source? When I click on my textbox it has
a Control Source line. I entered =Count(MyQueryName]) What I get is a line
for every record found by the query and each line includes a number that
displays the total number of records. How do I get it to show up just once?
And I've seen a number of web pages and info here that says this kind of sum
goes in the footer. I want this in the body of the report. I also want to be
able to show the results of many other queries. How? How? How? I'm so much
better than yesterday but I'm still no good at this. I need a small example
in my face to dissect. Then I'll know it.
 
SueB said:
RecordSource? Do you mean Control Source? When I click on my textbox it has
a Control Source line. I entered =Count(MyQueryName]) What I get is a line
for every record found by the query and each line includes a number that
displays the total number of records. How do I get it to show up just once?
And I've seen a number of web pages and info here that says this kind of sum
goes in the footer. I want this in the body of the report. I also want to be
able to show the results of many other queries. How? How? How? I'm so much
better than yesterday but I'm still no good at this. I need a small example
in my face to dissect. Then I'll know it.
Click to expand...

Ok...

The controls on the report have a ControlSource. The report itself has a
RecordSource. If the RecordSource of the report is your query then a control
using =Count(*) will give a count of records from that query. What value you
get from Count() depends on where you put it. In a GroupHeader or Footer it
will give the count for that group. In the ReportHeader or Footer it will give
the count of the entire query.

To use Count(), Sum(), Max(), or any other aggregate function to aggregate all
of the records in the report it must be used in the ReportHeader or Footer. In
addition these only work against the query that is the RecordSource of your
report and your report can have only one RecordSource so you would only get the
count of a single query using this method.

What you can use instead is the Domain Aggregate function DCount(). DCount("*",
"YourQueryName") will give you the total count of your query no matter where you
place it on the Report and it does not require that your query be the
RecordSource of the report. In fact for what you want you would not want your
report to have any RecordSource at all. Just an unbound report with a bunch of
controls each one using DCount() against a different query. If you have lots of
these it might not perform too well though.
 
Still stupid. The Not Like thing works great, but I don't know how to get
Access to tell me the total number of records that meet the criteria. If the
result of the query is 50 of the 100 records what do I need to do to get my
report to show 50?
Click to expand...

Could you please post the SQL view of the entire query? You should be
able to change this query into a Totals query counting records, or
base a Totals query on this query.
 
SELECT [pname_report 1_14].ID, [pname_report 1_14].Customer, [pname_report
1_14].SR1LD_prod_id, [pname_report 1_14].Suppl_Ckt_id, [pname_report
1_14].Wms_Pname_Id, [pname_report 1_14].dlci, [pname_report 1_14].vpi,
[pname_report 1_14].vci, [pname_report 1_14].orig_hourly_data, [pname_report
1_14].orig_weekly_data, [pname_report 1_14].dest_SR1LD_prod_id,
[pname_report 1_14].dest_Suppl_Ckt_id, [pname_report 1_14].dest_dlci,
[pname_report 1_14].dest_vpi, [pname_report 1_14].dest_vci, [pname_report
1_14].dest_hourly_data, [pname_report 1_14].dest_weekly_data
FROM [pname_report 1_14]
WHERE ((([pname_report 1_14].dest_SR1LD_prod_id) Like "0*" And
([pname_report 1_14].dest_SR1LD_prod_id) Not Like "*AV*" And ([pname_report
1_14].dest_SR1LD_prod_id) Not Like "??????G*") AND (([pname_report
1_14].dest_Suppl_Ckt_id) Not Like "TOP*" And ([pname_report
1_14].dest_Suppl_Ckt_id) Not Like "*Y*") AND (([pname_report
1_14].dest_dlci) Not Like "*") AND (([pname_report 1_14].dest_hourly_data)
Like "PNAME_HRLY_INCOME_NOT_FOUND"));



I hope you understand the text above - I sure don't.


This is one of several queries.
 
I hope you understand the text above - I sure don't.
Click to expand...

<g> Oh, you will one of these days.

IF - and I'm not sure that this is the case - you don't want to see
the details but only want to see the count, change this to

SELECT Count(*)
FROM [pname_report 1_14]
WHERE ((([pname_report 1_14].dest_SR1LD_prod_id) Like "0*" And
([pname_report 1_14].dest_SR1LD_prod_id) Not Like "*AV*" And
([pname_report
1_14].dest_SR1LD_prod_id) Not Like "??????G*") AND (([pname_report
1_14].dest_Suppl_Ckt_id) Not Like "TOP*" And ([pname_report
1_14].dest_Suppl_Ckt_id) Not Like "*Y*") AND (([pname_report
1_14].dest_dlci) Not Like "*") AND (([pname_report
1_14].dest_hourly_data)
Like "PNAME_HRLY_INCOME_NOT_FOUND"));

Just copy and paste this back into the SQL view of your query (MAKE A
BACKUP of your database first!!!)

If you want to see the details and you ALSO want to see the count on
your Report, open your Report in design view; use the View menu option
to view the Report Footer. On the footer put a textbox; look at that
textbox's properties and set its Control Source to

=Count(*)

to cout all the records on the report.

You say you have many queries to report - how do they differ? There
may be a way to do this without running multiple reports or building
multiple queries, using the Report's Sorting and Grouping facility.
 
Back
Top