Access Report

[Mike T]

I have a report that I use for all my spot play queries. I recently changed my setup so I can check the spot plays before scratches. I have ALL_HX tables that has the numbers before the results are added and an ALL_HXRESULTS with the results added. If I just open the queries to look at them, they work fine, but when I open them thru a form that opens the report and sends the query name to use, it runs the query and then it just stays on the "formating page, press Ctrl+Break to stop" forever(The CTRL Break doesn't work either). When I use the query as the record source for the report and just open the report, the report opens but it doesn't seem to be able to use the xFIN and xWIN fields(trying to count the xFin = 1 to find all winners), just gives an error. This report worked fine before I added the "before race" databases. The only change I made in the queries was to add the ALL_HX4RESULT table, add the relationships to connect them and change the table to get the xFin and xWIN from. Does anyone know why that would make it stop working?

[AwolAtHTR]

...Does anyone know why that would make it stop working?

no, I do not know for sure but this rings of problems I have had with a revised query by adding a table and ONLY setting the linked fields for joining the two tables. Sure, shoulda been a simple change to use connected data.

when the join was wrong, multiple records were being connected for EVERY record instead of matching ONLY a single record for each race. So, a query that took seconds, I once waited over five (5) minutes and the query came back with over half a million records instead of a few thousand!!

so, try running the query and see how many records are reported.

just a hunch.

duane

[Mike T]

The query only had 833 records, and no duplicates. The query runs fine when I view it, for some reason the report has problems using the new table data. I saved a copy of the report, then played around with it to see how much would actually work correctly. The report is a modified version of the ROI report in MikeDees template on HTR. I took off everything to the right of the # of races and the report worked fine. When I put the wins box back in, it hangs. It finally displayed the report after close to 10 minutes(after it said the query was done and went to the "formatting page" line in the status bar), but as I add other fields from the new table back on, it gets longer and longer. It also takes that long to move from page 1 to page 2. It shouldn't take that long for the report to display adding one additional box should it? It is the wins box with the following"

=DCount("[xFIN]",[Forms]![FRMQUERY]![QUERYNAME],"([xFIN]=1) and ([tname] = [text136])")

I didn't even want to see how long it would take with the other two boxes that used fields from the new table added back into the report.

[MikeDee]

I am not strong on reports and form queries. The stuff in the template is the limit of my knowledge.

I think you are on the bleeding edge with this one.

Maybe Donnie or Hurrikane can offer some advice.

When you do the joins and the queries and counts access has to do all of these calculations before it get to displaying the report.

It sounds like access is getting stuck somewhere but I don't know where to tell you to look.

I try to avoid reports and forms as much as possible because you have to spend all of that time formatting things.

Rather then trying to do run time calculations in the report you might want to put the domain calculations in a query and just run the query.

If you get something you really like or you want to save the results for future reference you can append the query to a results analysis table or you can analyze the query with excel and save the results in spreadsheet.

If all else fails and you want to go this way let me know and I can give you some tips.

[Donnie]

Since it sounds like all of your troubles begin once you link the Results table to your original All_HX table, and the report slows down once you include even one field from that ALL_HXRESULTS table, I would assume there is either a problem in your linking fields between the two tables (which fields are you linking on?) or your relationship between the two tables needs to be changed (from an Equijoin to a left outter join?). The reason your report is slowing down is because you are now probably dealing with either hundreds of thousands of records (or even millions of records returned) because as AWOL pointed out you are probably getting duplicates due to an improper relationship. What fields are you using to the link the two tables? You should be using TRK, DATE, RACE, and PRG. This will prevent duplicate records. If your links are correct then click once on each link line (it will go bold) then right click to get an option of Join Properties. Change the join property from #1 to the option that reads "Include all records from the Table ALL_HX and....." This will bring back all horses "pre-scratch". Changing it to the 3rd option brings back all horses post scrtach. Let me know if this helps.

Also to all....to speed the running of queries and reports you should have a number of fields indexed in your tables....TRACK, DATE, GENDER, RACE NO, DIST, .... basicly a good rule of thumb to use on whether you should incex a field or not in your table is if you consistently sort, group, or use sometype of filter or criteria against that field. Go to Design view of your table, click on the field you wish to Index and under that property choose YES(duplicates ok)

Mike...let me know if this helps!

[Mike T]

Thanks for all the suggestions everyone. I had tried most of those things, have the above fields indexed. In this query, I have four tables linked together. I have a HX4 table linked to a HX4RESULTS and HX5 table with tTRK, tDate, nRace, and tPGM. I have a 4th table, TRACKS, linked to HX4 by tTRK. The query alone works correctly. I get 883 results from about 400,000 records.

I did a lot of playing around and here is what I have noticed.

The big hangup seems to be when I have to do a calculation on a field in the query. I don't think it is neccesarily from the new table. It's just a coincidence that the fields I am doing calculations on are from the new table. I saved my old queries when i just used the race results and it seems to hang at the same areas, any calculation.

=DCount("[xFIN]",[Forms]![FRMQUERY]![QUERYNAME],"([xFIN]=1) and ([tname] = [Track])")

=DAvg("[xwin]",[Forms]![FRMQUERY]![QUERYNAME],"([xFIN]=1) and ([tname] = [Track])")

=DSum("[xWIN]",[Forms]![FRMQUERY]![QUERYNAME],"([xFIN]=1) and ([tname] = [Track]) ")/([TrackPlays]*2)

These areas of the report take forever. When I take out all of them but one, it takes about 4-5 minutes from the time the query finishes and the "report Formatting" is done. It then takes 4-5 minutes to move from page to page. If I leave them all in, I have given it 30 minutes without the report showing up. When I take the calculations out and just use the field names(xFIN) from the new table, the report loads about 10-15 seconds after the query ends and moving from page to page is instant. This particular report breaks the numbers down by track. Do the formulas re-do the entire query each time? If so, would it be better if I made the query an append query, made a macro that cleared the table, then append the query results to the table and use the table for the report so it would have 800 records to go thru instead of 400,000? Any thought?

Thanks for all your time and efforts.

[MikeDee]

Do the formulas re-do the entire query each time?

yes I believe that they do

If so, would it be better if I made the query an append query, made a macro that cleared the table, then append the query results to the table and use the table for the report so it would have 800 records to go thru instead of 400,000? Any thought

I think this would be a lot faster

some suggestions

when you set up your append query for the first time set it up as a make table query. Then you will have a new table with all of the fields and fields defs in it.

Thereafter you can run it as an append query.

You can change the record source your report uses by going to the design view of your report then click on
view
properties
(in the report pop-up) data tab
then change the query or table that the report is using

[Mike T]

Thanks for the suggestions. Setting the query to a make table query the first time thru is an excellent idea. I never thought of that. I would always design the table out and hope it was done with the same format.:)

[hurrikane]

my experience with access reports is that anytime you are working from complex queries to build a report the processing time is prohibitive.

With other dbs it is better. Remember that Access is not a 'true' relational db. there are no constraints. There are a lot of checks and balances to be sure the user is not affected with real db decisions or building a db under db construction constraints. Add to that the fact that not many of the HTR db's I've see are relational on any level. Most are just a big flat table.

That, I'm sure, did nothing for you.

On thing to consider is how a db does it's queries. If it is a number things are very very simple. It either is this number defined in binary or it is not.
With text it becomes much more complicated. All of your Dcounts rely on a text field called 'track'. If your text field' 'track' were replaced with a number representing the track your process time would improve exponentially.

without seeing your queries I can't do much more than that. In access any time you can work from a table with filters instead of queries your reports will work much much faster.

[Donnie]

Kane-
you are absolutely correct....let me put it in layman's terms....

Most HTR access users are doing this as part of a hobby. Simply put, we import ALL the data into an Access table and do all of our queries and reports from that single table.

If this were your fulltime job, then a need would quickly exist that would force you to, first, remove all the data that is not going to be used in our tables. (how many fields do you not even look at in your table structure?) Then that master table would be broken down into smaller tables....maybe a table that holds just female horses as well as a table that holds just male horses, or possibly all races, versus tables that hold just dirst races vs a table that holds just turf races. Some of the members on this board already break the data down to just the few tracks they play. Much like you want to specialize at one track, your tables should be "specialized" to hold only certain bits and pieces of data. Those of you who have attended the past seminars, pull up that Access book (Level 1) I distributed and read the chapter concerning Normalizing Data. Then go back and study the chapter on table relationships.

It comes down to hobby vs. vocation. A hobbyist doesn't have (make) the time to properly maintain the data in the database....we are spotplayers..... Someone who does this for a living needs to understand how to streamline the data management so no redundant data is found within the tables and the tables hold only data that is important to the output. This is the end result for a true "data-miner".

Someday I hope this will become a vocation of sorts. For now, call me Spot!