Search results

Thanks Gemma, So in the races with more than 1 candidate with Trait X I don't want to consider either of them. They are disqualified from further consideration. That is why I want to exclude them from further analysis. I only want 1 candidate per race, not 2. The person who created the data...

I've realised why there may be some confusion.. I'll need to explain further. These queries concern analysing Horse-Racing information. The query I ran concerned getting a set of results where some horses have a characteristic called X. There is usually only one horse per race has that trait...

Thanks, Can I make a query into a totals query? That sounds like a good idea. I just want to exclude the dupes so I can analyse the non-dupe records. I'll try that out and get back to you. Thank you. I just did that but it didn't exclude the dupes. The SQL is attached.

I looked at the table in the first instance and saw a couple of dupes. I then ran the Find Dupes Wizard that gave a list of 12 records that are dupes. Rather than delete them, I thought I could run a join that would not include the dupes. But that requires SQL which I'm very rusty at. I...

How do you do that?

Thanks, Maybe it's easy for you. The duplicates are unwanted but understandable as to why they are there. I just want to exclude them at this stage. I imagine there is an SQL statement which has the qryMain records and NOT IN qryDupes like a join that excludes the duplicates. I don't know...

Thanks for the reply, I understand why the duplicates are there, but they don't fit into the analysis I want to do. So in terms of what you have replied I'd say they are unwanted duplicates.

I have a query say, qryMain that has records I'm interested in, however the query contains some duplicates. I created a find duplicates wizard query to identify those records qryDupes. The qryDupes records are on 3 fields. So I'm thinking can I do a Join between these 2 recordsets to exclude...

Thanks, I did do some doubling up on the work, but it all worked out really well.

Thanks Gasman! Believe or not I am using it that way. I decided to focus on the Excel side and the best way of displaying these reports is exactly what you have pointed out, using "mmm-yy". I set the query in Access as follows: SELECT Format([RaceDate],"mmm/yy") AS MthYr, Transaction.Track...

Thanks for the response. I wouldn't mind if you wrote some code, it would be helpful. I have been doing some tinkering with Excel VBA. What happens is I manage the data in Access and then I export it to Excel to organise into reports, which I have a template for. Interesting that you are...

Thank you. I appreciate you are trying to help me. I'm going to experiment with taking the number e.g. 202403 and see if I can tinker around with VBA to see if I can convert the number to mmm-yy. I'll post whether I've got anywhere with it. Thanks very much for your help, you have been terrific.

Hi, when I run that in the Immediate window I get 24-Apr-2024. Maybe something could be done with VBA? When you say it's arbitrary I'm not sure of the syntax you are using in the Format function. Maybe I could do a VBA function in Excel? It's not a big deal really what you've given me is...

Having had 24 hours to consider the expression, I wonder if it is possible to reverse engineer the number into a date type. e.g. 202404 into something that would be like say Apr24? It's more of a presentation thing. Do you have any thoughts on that? Or how I'd take that number in Excel and...

I forgot to mention your 10000 * year formula which creates 4 placeholders. You've certainly turned my head on this ideas. Very lateral thinking. Thanks a lot for these ideas and pushing me to try it when I couldn't see the forest from the trees. Really great and creative! I won't be...

Thanks, I just replied to @Gasman and said what a good idea this is. Using year * 100 to create 2 placeholders for the months to slot in. That is certainly lateral thinking, well done. You had me fooled but I see it now. Most importantly it has helped me to put the months and years in...

Thank you. I see what is happening now, the year * 100 creates 2 placeholders for months to slot in. Very good. Thanks for explaining. What a good idea!

What I would like to know is why multiplying by 100 works? The result is great, I've been able to apply this to my queries and they are working beautifully but my inner mathematician wants to know why? Thanks for sticking with me on this.

Thanks for your reply. I leave the immediate window open all the time, and the others I open as needed.

My apologies. I've just run the SQL and it does exactly what I'm wanting. I was asleep at the wheel. Didn't think it through. That's great I'll be able to apply this in my queries. I've got to check out now it's 21:00 in Australia where I live. I'll get back into it tomorrow and let you...