Records in query field not showing

[Equine Investor]

I hope someone can help.

I have two fields in tblSP

One is SP and the other is SPDEC

SP contains SP ODDS and the other contains the decimal equivalent.

SP SPDEC
1/1 $2.00
1/1E $2.00
1/1F $2.00
2/1 $3.00
2/1E $3.00
2/1F $3.00

etc.

There is a relationship to the SP ODDS in another table.

Now when I set up a query to show the decimal equivalent only based on this relationship the field is blank.

Can anyone shed some light on why it won't show the decimal odds?

The SP field is text and the SPDEC is currency.

Is there a workaround or something that someone could suggest as there are over 260 types of SP odds?

Thanks.

 

[Andrew Fergus]

Hi
Can you explain some more about the relationship? Which field is linked to which field from which table?

Also, can you post the query SQL? You can get into the SQL in your query design by clicking on View -> SQL.

Andrew

 

[Equine Investor]

tblSP.SP is related to tblLASTRUN.SP and in the query I want to show the related field which is tblSP.SPDEC


SQL...

SELECT [tblLAST RUN].KEY, [tblLAST RUN].HORSE, [tblLAST RUN].PLACING, [tblLAST RUN].STARTERS, [tblLAST RUN].MARGIN, [tblLAST RUN].DATE, TRACKS.TRACK, [tblLAST RUN].M, [tblLAST RUN].RNO, [tblLAST RUN].PRIZEMONEY, [tblLAST RUN].[PRIZEMONEY WON], [tblLAST RUN].EVENT, [tblLAST RUN].CLASS, [tblLAST RUN].AGE, [tblLAST RUN].REST, GOING.GOING, [tblLAST RUN].DISTANCE, [tblLAST RUN].TIME, [tblLAST RUN].Field19, [tblLAST RUN].[LAST 600m], [tblLAST RUN].Field21, [tblLAST RUN].Field22, SP.SP, [tblLAST RUN].WGT, [tblLAST RUN].[ALL], [tblLAST RUN].LIMIT, [tblLAST RUN].JOCKEY, [tblLAST RUN].BP, [tblLAST RUN].Field29, [tblLAST RUN].Field30, [tblLAST RUN].[800m], [tblLAST RUN].[400m], [tblLAST RUN].Field33, [tblLAST RUN].WINNER, [tblLAST RUN].[WINNER WGT], [tblLAST RUN].Field36, [tblLAST RUN].Field37, [tblLAST RUN].Field38, [tblLAST RUN].Field39, [tblLAST RUN].Field40, [tblLAST RUN].Field41, [tblLAST RUN].Field42, [tblLAST RUN].Field43
FROM (([tblLAST RUN] LEFT JOIN TRACKS ON [tblLAST RUN].VENUE = TRACKS.TRACKS) LEFT JOIN GOING ON [tblLAST RUN].GOING = GOING.G) LEFT JOIN SP ON [tblLAST RUN].SP = SP.SPDEC
WITH OWNERACCESS OPTION;

 

[Andrew Fergus]

Should SP from tblLASTRUN be linked to SP or SPDEC? From a very brief look at your SQL, I think it should be linked to SP in the SP table but you have linked it to SPDEC. Is it just this one field that is not displaying correctly?
HTH, Andrew.

 

[Equine Investor]

Should SP from tblLASTRUN be linked to SP or SPDEC? From a very brief look at your SQL, I think it should be linked to SP in the SP table but you have linked it to SPDEC. Is it just this one field that is not displaying correctly?
HTH, Andrew.

Andrew,

I've just renamed some of the tables because it was getting confusing between the tables and the fields.

It is linked from SP to SP, but my field names were wrong

Here is the modified SQL, but the problem remains.

SELECT [tblLAST RUN].KEY, [tblLAST RUN].HORSE, [tblLAST RUN].PLACING, [tblLAST RUN].STARTERS, [tblLAST RUN].MARGIN, [tblLAST RUN].DATE, tblTRACKS.TRACK, [tblLAST RUN].M, [tblLAST RUN].RNO, [tblLAST RUN].PRIZEMONEY, [tblLAST RUN].[PRIZEMONEY WON], [tblLAST RUN].EVENT, [tblLAST RUN].CLASS, [tblLAST RUN].AGE, [tblLAST RUN].REST, tblGOING.GOING, [tblLAST RUN].DISTANCE, [tblLAST RUN].TIME, [tblLAST RUN].Field19, [tblLAST RUN].[LAST 600m], [tblLAST RUN].Field21, [tblLAST RUN].Field22, tblSP.SPDEC, [tblLAST RUN].WGT, [tblLAST RUN].[ALL], [tblLAST RUN].LIMIT, [tblLAST RUN].JOCKEY, [tblLAST RUN].BP, [tblLAST RUN].Field29, [tblLAST RUN].Field30, [tblLAST RUN].[800m], [tblLAST RUN].[400m], [tblLAST RUN].Field33, [tblLAST RUN].WINNER, [tblLAST RUN].[WINNER WGT], [tblLAST RUN].Field36, [tblLAST RUN].Field37, [tblLAST RUN].Field38, [tblLAST RUN].Field39, [tblLAST RUN].Field40, [tblLAST RUN].Field41, [tblLAST RUN].Field42, [tblLAST RUN].Field43
FROM (([tblLAST RUN] LEFT JOIN tblTRACKS ON [tblLAST RUN].VENUE=tblTRACKS.TRACKS) LEFT JOIN tblGOING ON [tblLAST RUN].GOING=tblGOING.G) LEFT JOIN tblSP ON [tblLAST RUN].SP=tblSP.[SP]
WITH OWNERACCESS OPTION;


What is baffling me is that tblTRACKS and tblGOING are linked in exactly the same way, and they are working fine, just the tblSP is not showing the SPDEC field in my Query.

Could it have anything to do with the fact that tblSP.SP is a text field and tblSP.SPDEC is a currency field?
Really at a loss.

 

[Andrew Fergus]

It shouldn't matter that SPDEC is formatted as currency because it isn't linked on that field. Following is my last attempt at fixing this (I too will be baffled if this doesn't work).

Open the tblSP table, check the format of the SP field - in particular check that it is the same format as the SP field in the tblLASTRUN table (both fields should be text). While you have these tables open, check that the data in the LASTRUN table is actually identical to that in the SP table - in particular check that one of the fields doesn't have an additional space.

You can check the data by making some new queries and checking the data is, or is not, identical using the following SQL. See what these three queries return and this may answer your question :

Query to check for fields that are in both tables
SELECT [tblLAST RUN].SP, tblSP.SP, tblSP.SPDEC
FROM [tblLAST RUN] INNER JOIN tblSP ON [tblLAST RUN].SP = tblSP.SP;

Query to check for fields in tblLASTRUN that do not have a match in tblSP
SELECT [tblLAST RUN].SP, tblSP.SP, tblSP.SPDEC
FROM [tblLAST RUN] LEFT JOIN tblSP ON [tblLAST RUN].SP = tblSP.SP
WHERE (((tblSP.SP) Is Null));

Query to check for fields in tblSP that do not have a match in tblLASTRUN
SELECT [tblLAST RUN].SP, tblSP.SP, tblSP.SPDEC
FROM [tblLAST RUN] RIGHT JOIN tblSP ON [tblLAST RUN].SP = tblSP.SP
WHERE ((([tblLAST RUN].SP) Is Null));

HTH, Andrew.

 

[Equine Investor]

Andrew thankyou for your advice.

I've finally figured it out with your guidance :wink:

The problem was that the SP field contained fractional odds and there were "invisible" spaces, so the records could not be matched.

Why these spaces are there is beyond me, but after a lot of individual record adjustment, and spaces put in the various odds depending on the number of characters in the field, it was solved.

Thanks for getting me to look in the right direction.

Much appreciated.

***SOLVED***