Access problem
in [Help]
|
From: Calvary Chapel on 30 Jul 2010 15:28 > 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 > 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!
From: Don Leverton on 30 Jul 2010 17:22 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(a)gmail.com> wrote in message news:8399c1b9-444e-4479-b208-b054793e7bec(a)i4g2000prf.googlegroups.com... <snip> >> > > 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!
From: Rich P on 30 Jul 2010 17:36 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 ***
From: Salad on 30 Jul 2010 22:00 Calvary Chapel wrote: > >>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 >> > > > 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! 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.
First
|
Prev
|
Pages: 1 2 Prev: Will Access 2010 run a 2003 program? Next: Format Numbers on MS Access Report |