I am running WinXP and MS office.
I have a problem with Access - When I try to do a mailing list for instance,
I get messages that the Wizards are not installed even though I have
installed them again and again. I have tried to take out and reinstall the
program, have renamed the accesswiz files and repaired the program etc.

Any ideas?????

There isn't quite enough information to give a clear answer to the
question/problem. But we can start here: the Report is going to be
based either on a table or a query. If you go to the Report Design view
- In the upper left corner of the Report right underneath the ReportName
is a small square with a square shaped dot. Right-click on that square
dot and select Properties at the bottome of the menu list that appears.
At the top of the propertysheet that appears it will say Recordsource.
The Recordsource will either be a table name, or a query name, or it may
be straight SQL code. If there is nothing in the Recordsource box, then
programming code is being used to populate the report (highly unlikely -
but it could be the case).

Find out what the recordsource is. Is it a TableName - a query Name, or
straight SQL code (if is starts with Select ... then it is Sql Code).
Post what you find, and then we can take it from there.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Thank you so much for your help, Rich!

Okay, I found what you are talking about and it looks like the report
is based on a querry because when I clicked on the name of it (Yearly
Donations Received), it took me to a Query Builder page. I took a
screenshot of what it looks like and can email if that helps.

Nichole

Calvary Chapel wrote:

The query, if summing the data, might be a Totals query. If you go to
the menu in the query builder, you can click on View/SQL from the menu
and if you see a "Group By" towards the end of the statement, you know
it is a Totals query. (Click back to View/Design). With a Totals
query one can Group on a column (which would remove your "duplicate" if
same date and amount), or use Last, or First. You then would not see
both amounts in First or Last is used.

If this is a Totals query, in the Totals row of the query designer you
should see the word SUM under the column that contains your amounts.

Also, I created a table called Table1 with 2 fields; ID and Amt. I
added 3 records and put 1 in the value for ID and Amt. If I use the SQL
statement
SELECT Table1.ID, Table1.amt FROM Table1;
I will see all 3 records. However, if the statement is
SELECT DISTINCT Table1.ID, Table1.amt FROM Table1;
I will see only 1 record because all 3 records have the same ID and Amt
value. So perhaps DISTICT could be the culprint

To post you SQL statement, as mentioned by Rich, you can open your query
in design mode, select View/SQL, and copy the SQL statement to the
clipboard (CTRL+C) and paste it into your message reply (CTRL+V).

From: Calvary Chapel
Date Posted: 7/29/2010 5:03:00 PM

Thank you so much for your help, Rich!

Okay, I found what you are talking about and it looks like the reportis
based on a querry because when I clicked on the name of it (Yearly
Donations Received), it took me to a Query Builder page. I took a
screenshot of what it looks like and can email if that helps.

Nichole
<

Now that you have located the query -- what you can do is this -- which
would be a little bit easier than emailing. Go To the query tab in the
Access window. If you don't see the Access window (the window with
option listings for Tables, Queries, Reports, Modules, ...) Press the
F11 key on your keyboard. This will bring up the Access window. Now
click on the Query option and select the query name (from your report).
Open this query in design view. Now go to the file menu at the top of
the page and go to the View menu and click on Sql View. This displays
the sql code for the query. Highlight this code and copy it. Then in
your reply to this message paste the code there, and we can analyze it
for you.


Rich

*** Sent via Developersdex http://www.developersdex.com ***

You guys are such great help! Thank you!

When I went to my list of queries, as you said to do, I noticed some
interesting things. The first is that there are two queries with
similar names. The first is the one that the report is drawing from
and is called "Yearly Donations Received Querry Query," created &
modified in 2005 and another called "Yearly Donations Received
Querry," created in 2004 and modified in 2006. (The last secretary
left here in 2004...) WHY? I don't know...but both included all the
data I had been entering for each week's tithe. When in datasheet
view, neither one contained the duplicate entries. The only
difference between the two (besides the bad spelling of their names)
is the SQL code.

This is what the code looks like for the query the report actually
draws from, "Yearly Donations Received Querry Query":
SELECT [Yearly Donations Received Querry].[Name], [Yearly Donations
Received Querry].[Donation Type], [Yearly Donations Received Querry].
[Donation Amount], [Yearly Donations Received Querry].[Donation Date]
FROM [Yearly Donations Received Querry];

And this is what the code looks like for the other query "Yearly
Donations Received Querry":
SELECT [Yearly Donations Received].Name, [Yearly Donations Received].
[Donation Type], [Yearly Donations Received].[Donation Amount],
[Yearly Donations Received].[Donation Date], [Yearly Donations
Received].[Mailing Address], [Yearly Donations Received].City, [Yearly
Donations Received].State, [Yearly Donations Received].Zip
FROM [Yearly Donations Received]
GROUP BY [Yearly Donations Received].Name, [Yearly Donations Received].
[Donation Type], [Yearly Donations Received].[Donation Amount],
[Yearly Donations Received].[Donation Date], [Yearly Donations
Received].[Mailing Address], [Yearly Donations Received].City, [Yearly
Donations Received].State, [Yearly Donations Received].Zip;

I don't think it's necessary for both to exist. So if I can fix the
one to show the duplicate date & amount entries, I should be able to
delete the other, right?

I feel kinda silly talking about things I know hardly anything about,
buy you guys are helping me understand bits & pieces and I'm doing my
best to explain it clearly.

Thanks again!

Hmmm.
First off, let me say that no offense is intended to the original database
designer, as I wouldn't be surprised if she may have volunteered to create
it without expecting to be compensated financially.

What I can see by glancing quickly here is that the table structure is poor,
and not "normalized", IMO.
Access is a *relational* database, and it seems to be set up (somewhat
typically for the inexperienced / casual user) in what is referred to as
"flat-file database" style.

If it were my decision to make, I would have at least two (related) tables
to contain the data.
One table for the donors ("tblDonors"), and another for their multiple
donations ("tblDonations").
Set up properly with a "one to many" relationship, it would accurately
identify the donor ... and display (and sum) all donations that were made.

Flat-file, "un-normalized" tables such as this are prone to a lot of
problems.
"J. Smith" for example might be John Smith or could be Joan Smith
Or maybe you have more than one "John Smith" that live at very different
addresses.

That bring up another point and another good reason to split his data up ...
unnecessary typing of duplicate data. As it is now, you are having to
re-enter all of that address and contact information over and over again,
right?

I'm not sure which version of Access you are working with.
I'm still using Access 97 for the bulk of my work, so I hope what follows
makes sense.

If you want to see what I'm talking about with the duplication of data,
1.) Go to the database window and click on the "Queries" tab.
2.) Click on "New" and then select "Find Duplicates Query Wizard"
3.) Select the table itself ("Yearly Donations Received")
4.) Access will ask you "Which fields might contain duplicate information?)
5.) In this case, you'll want to select the "Name" field (poor choice for a
field name, but that's another story.)
6.) Access will then ask you if you'd like to display other fields along
with the dupe data.
7) Yes, please. You should probably include most or even all of the other
fields so that you get the "big picture" here ... but all of the address
fields as a minimum, as the intent here is to show you the repetitive
entries of the same data. Maybe you'll even spot the "J. Smith" scenarios.

OK. To move on and answer one of the questions that you have specifically
asked.

"So if I can fix the one to show the duplicate date & amount entries, I
should be able to delete the other, right?"
Well, no. As it sits right now, the second query ("Yearly Donations Received
Querry Query") is based on the first one ("Yearly Donations Received
Querry")
If you look at that SQL string "SELECT [Yearly Donations Received Querry]
..... yada yada ... FROM [Yearly Donations Received Querry];" it's somewhat
obvious.

If you delete on or the other, you'll have to inspect and/or change the
RecordSource for the report. Otherwise you'll get a "cannot open report "
kind of error.

In summary, I know this is likely a little overwhelming, but stick around
and continue to ask questions. I'd offer more of my help (assuming that it
would be welcome) but my daughter is getting married in about 3 weeks ... so
I don't know for certain that I'd be available.


"Calvary Chapel" <cchavasu (AT) gmail (DOT) com> wrote

<snip>

Hi Nichole,

I am starting to see what is going on here. It looks like the original
query was doing the totalling (Group By Clause in the old query) and
then the report was displaying the resulting dataset in the report
viewer. The newer query is shorter because the part that did the
totalling (the Group By section of the 1st query) was removed and most
likely, the totalling (Grouping) is now being done in the report itself
-- Reports have a Totals feature I will explain later. In the meantime,
it looks like the newer query (the 2nd query) displays all the rows
entered but they get totalled (Grouped) in the report. So now I suggest
you try the following:

In the query design view of the 2nd (newer - shorter) query - click on
the Run button (the Red exclamation symbol near the top middle of the
page). This will run the query manually. Look at the resulting dataset
and see if it includes the records you say are missing. I will wager
that you WILL see the missing records here. In that case - the fix will
be to make some adjustments in the report. But first lets see if your
missing records show up in the query when you run it straight from the
query Designer. And then let us know.

Note: if the query is taking paramertes (which it doesn't sound like it)
you will get a prompt to enter some values. Read the title of the
prompt if this happens and you should know what values to enter. But it
doesn't sound like the query is taking parameters.



Rich

*** Sent via Developersdex http://www.developersdex.com ***

Calvary Chapel wrote:
You have 2 queries. It looks like the first is taking the columns
processed from the second query...but I could be wrong.

Since you can see both values, here is a suggestion. In your original
post you mentioned somebody made 2 checks for the same day for the same
amount. If you can spot those 2, make one $50.00 and the other $50.01.
That will create a difference between the two records.

In my post, I mentioned that GroupBy could be a problem. In REALITY,
you should be SUMMING, not grouping. Ex:
TitheDate 1/1/2010 Amt 50
TitheDate 1/1/2010 Amt 50
Result with GroupBy is
TitheDate 1/1/2010 Amt 50
or 1 record. The date is the same, amount is the same, name is the
same, so you get 1 record. If one of those were $50.01 (we are talking
about testing) then you'd end up with 2 recorrd.

However If you Summed Amt, not GroupBy, you'd end up with 1 record but
the amt total would be $100, not $50 are you get with GroupBy. So open
up the query in the querybuilder and change that GROUP BY to SUM.

BTW, make a backup prior to changeing it. Find the backend database if
it is the tables are linked and from Explorer make a backup copy of the mdb.

On Jul 30, 2:36*pm, Rich P <rpng... (AT) aol (DOT) com> wrote:
Just wanted to update you.
I had a friend come in today who is helping us with our website. I
asked if she had any access experience. She doesn't, but she
understands the code. So I told her about my problem and asked her to
look at it for me. She changed the SQL code to look like this:

SELECT [Name], [Donation Type], [Donation Amount], [Donation Date]
FROM [Yearly Donations Received Querry];

and now all the reports are correct and display the correct amount.
I am SO relieved!
Thank you all so much for taking your time to help me. I know it's
hard when you're not looking at it and I really appreciate it! God
bless.