Couple of Access Questions

[Cliff]

Guys,

1) When wanting to do a query on a horse, or bunch of horses, is there a way to set up the criteria for the actual program number of the horse as opposed to the post position number?

As you probably know, the post position number does not always kick back the correct horse due to scratches, combined entries, etc. I have not linked tables and "stripped" out the program number yet.

2) Is there a quick/easy way to rank a self-generated expression through the queries? Seems like there should be a way, but it might involve VBA.

Thanks as usual,
Cliff

[Rick]

SELECT (Left(ALL_MSA.thorse,2)) AS [tPGM#], ALL_MSA.tTRACK, ALL_MSA.tDATE, ALL_MSA.nRACE, ALL_MSA.nDIST, ALL_MSA.tSURF, ALL_MSA.tTYPE, ALL_MSA.tCLASS, ALL_MSA.nPAR, ALL_MSA.tHORSE, ALL_MSA.nPPO, ALL_MSA.nMLO, ALL_MSA.nLAY, ALL_MSA.nEQC, ALL_MSA.tRS, ALL_MSA.nQPT, ALL_MSA.[nEEN%], ALL_MSA.nCLCH, ALL_MSA.[nJTW%], ALL_MSA.nLFIN, ALL_MSA.nBLF, ALL_MSA.nFAV, ALL_MSA.nSPCA, ALL_MSA.rF1, ALL_MSA.rF2, ALL_MSA.rF3, ALL_MSA.rAP, ALL_MSA.rEP, ALL_MSA.rSP, ALL_MSA.rFX, ALL_MSA.rLP, ALL_MSA.rVEL, ALL_MSA.rCLA, ALL_MSA.rPER, ALL_MSA.rEFF, ALL_MSA.rPOW, ALL_MSA.[r(K)], ALL_MSA.rJKY, ALL_MSA.rTRN_M, ALL_MSA.rHTR, ALL_MSA.[rPRB%], ALL_MSA.nCLA, ALL_MSA.nPER, ALL_MSA.nPOW, ALL_MSA.nVEL, ALL_MSA.nHTR, ALL_MSA.nVBET, ALL_MSA.[nPRB%], ALL_MSA.nK2, ALL_MSA.nSTAR, ALL_MSA.nLSH, ALL_MSA.nFLD, ALL_MSA.nFIN, ALL_MSA.nODDS, ALL_MSA.nPL, ALL_MSA.vfr1, ALL_MSA.vfr2, ALL_MSA.vfr3, ALL_MSA.[vA/P], ALL_MSA.[vE/P], ALL_MSA.[vS/P], ALL_MSA.[vF/X], ALL_MSA.[vL/P], ALL_MSA.nPSCN, ALL_MSA.nPdif, ALL_MSA.rPSCN, ALL_MSA.nACL, ALL_MSA.rACL, ALL_MSA.nSPEED, ALL_MSA.nDAY, ALL_MSA.nPOS1, ALL_MSA.[nWIN$], ALL_MSA.[nEXA$], ALL_MSA.[nTRI$], ALL_MSA.rLONEF, ALL_MSA.nEVDIF, ALL_MSA.nEPDIF, ALL_MSA.rSPEED, ALL_MSA.r3P, ALL_MSA.n3P
FROM ALL_MSA;

The above is a query in SQL view for adding the program #. Create this query and save. Then use this query instead of the ALL_MSA table in your other queries.

This query is for those using HTR4MSAC.exe. If you are using HTR4MSA.exe just remove the additional field at the end.

I used the above query with an append query to create a master table combining the fields of ALL_MSA and ALL_IMPC tables. When I started my database was 800 mb. When I was done and removed the old tables my database was about 200 mb. I still haven't figured that one out yet.

[Cliff]

Ricks,

I tried this but kept getting an error message:

Syntax error (missing operator) in query expression '(Left(ALL.thorse,2))'.

The only thing I changed was to delete the "_MSA" extension as I have my main table just named "ALL". Any ideas?

Thanks again,
Cliff

[Rick]

The only thing I can think to try is putting [] around thorse like ALL.[thorse]

If that don't work you can send me a copy of your table (without records) along with this query for me to check out.

It looks like everything is right.

[MikeDee]

Cliff

the only way I know how to rank runners in a race is with vba. I'm at KEE for a few days and I cna help you with it this weekend.

[Glen]

Cliff to get the program number just use tHorse. The number before the name of the horse is the program number and not the Post Postion number.

For example:
tTRACK tCLASS rF1 tHORSE nODDS
CRC C05 2 7 Pearl in 6.50
AP C05 3 3 Sister Ca 2.50
LAD C06 1 7 Smilin Fo 9.50
LAD C08 4 9 Call to O 14.10
LAD C05 6 6 Womble 4.70
CRC C05 2 8 Let's Mak 4.50
PIM C08 1 3 Peatterly 6.20

[Cliff]

Glen,

I tried using #????????? (where # was the actual program number of the horse) in the thorse column, but Access didn't like it.

I might have the number of spaces and/or question marks wrong though. Wasn't real sure how to proceed. LMK if you have a solution.

Ricks,

I will try the brackets when I have some time to spend on it again. Thanks.

Cliff

[MikeDee]

Clff

take one of your good queries that works and where you would like to parse the porgram number off of the horse name. In the design of that query inasert a new column where you want the prg no to appear. Put your cursor in the field name row of the column you inserted and type the followng exactly as shown.

prgno:left(thorse,2)

This will create a field in this query named prgno with the 1st two characters of the thorse fiield whch is the program number. This will only be in this query. Let me know if this worked and if you want it in your master table.

[Frank N]

Cliff: if you want to query by program number, except for program number 1, put this into tHORSE

Like "#*"

where # is the number you are querying.

So if you want to know how program number 4 does, you'd put in:

Like "4*"

This will return all horses with program number 4. The wildcard ( * ) takes care of everything to the right of the number.

With program number 1, you have to do it differently, for two reasons: using 1* would also get you 10, 11, 12, etc. And using 1 * (note the space) would get you 1, but not 1A or other parts of any entry. (Of course this wouldn't be a problem with other program numbers, since such as 2* would get all of the parts of the entry)

You can solve this by doing the following:

Like "1*" And Not Like "10*" And Not Like "11*" And Not Like "12*" And Not Like "13*" And Not Like "14*"

[Cliff]

MikeDee & Frank N,

Thanks for the suggestions. I'll try both approaches on my next Access session. Welcome back Frank N.

Cliff