Prev: Will Access 2010 run a 2003 program?
Next: Format Numbers on MS Access Report
From: Calvary Chapel on 29 Jul 2010 17:12
I am the secretary for our church. We use Access as a database of
those who attend our church and also as a way to keep tithe/offering
records. The secretary here before had plenty of Access background
and is the one who built this database. Other than entering info into
it, I know virtually nothing about Access. I have run into a problem
and since I don't have much Access knowledge, I'll do my best to
explain it. I'm sure it's a small fix, but I just don't know where to
find it.

All the weekly tithes get entered into a table. Then they get updated
to a larger "yearly table." From this, she has made a form that has a
button on it to "Open Yearly Statements." This basically opens up a
report of all the tithes for each person so that each person can get
their own giving statement at the end of the year. The problem I am
running into is if someone, for example, puts in 2 separate $50 checks
in the same week. These two "transactions" will show up as two
separate lines on the table, as I entered them, but when I run the
report, it doesn't show the duplicate(s) or even include it in the
total. This results in an inaccurate report. I've looked around in
the design view and found where it says "Hide Duplicates" and "No" is
selected for every data field. I don't know where else to look to fix
this. It only happens when the giving date and giving amount are the
same. If there was $50 entered one week and $50 entered two weeks
later, they will both show up.

Thank you in advance to anyone who takes the time to read this and
help me out!
From: Rich P on 29 Jul 2010 17:42
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 ***
From: Calvary Chapel on 29 Jul 2010 19:02
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
From: Salad on 29 Jul 2010 22:50
Calvary Chapel wrote:

> 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

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: Rich P on 30 Jul 2010 11:31
>
Re: Access problem
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 ***
 |  Next  |  Last
Pages: 1 2
Prev: Will Access 2010 run a 2003 program?
Next: Format Numbers on MS Access Report