Using FORM to feed REPORT using strWHERE

[PatD]

I have a form with a single selection Listbox (LstSkipperID) to select a single Dinghy Skipper. The listbox has 3 columns; SkipperID, FirstName & LastName (the SkipperID is not visible).
I can extract ID, FirstName and LastName using Columns 0, 1 & 2.
The report (RptSkipperRaceResults) works OK and currently lists all the Skippers by Date followed by ID order and a standard CommandOpen Report button works OK.

The next stage is for the Report to list a single Skipper (ID) and with the FirstName and LastName at the top of the Report.
I have tried looking at the methods used by others but I can’t figure out how StrWhere works and how to extract the Data at the Report Stage.
The following is my new Command to open the report but it produces the original report showing only 9 pages when there in fact 22 pages.
I assume the WhereCondition would work as a WHERE condition in the SELECT Query??
I’m Lost, could anybody help please?
Pat

Current Form Button Coding to Open the Report
Private Sub CmdOpenRatingReport_Click()
Dim StrWhere, StrReport, Str1stName, Str2ndName As String
StrReport = "RptSkipperRaceResults"
'StrWhere & "AND Str1stName = LstSkipperID.Column(1)"
'StrWhere = StrWhere & "AND Str2ndName = LstSkipperID.Column(2)"
StrWhere = "(SkipperID = LstSkipperID.Column(0)"
DoCmd.OpenReport StrReport, acViewPreview, WhereCondition:=SkipperID
End Sub


The Query that currently feeds the Report
SELECT Race_Dates.RaceDate, Race_Dates.NrEntries, Rating.Skipper_ID, Rating.Rank
FROM Race_Dates INNER JOIN Rating ON Race_Dates.RaceDate = Rating.Race_Date
ORDER BY RaceDate;

 

[DCrake]

Add the skipper table to your query and join on skipperid. Then bring in the skipper first and last name down then you will be able to add the name to your report if you group by skipperid.

 

[PatD]

Thanks for response David, unfortunately that didn't work for me.
I have a Skippers Table and altered the Query to read:

SELECT Race_Dates.RaceDate, Race_Dates.NrEntries, Rating.Skipper_ID, Rating.Rank, Skippers.ID
FROM Skippers INNER JOIN Rating ON Rating.Skipper_ID = Skippers.ID;

Also changed to Group by SkipperID and then Date

On running the report the system then asked for Race_Date & Nr.Entries.
Do I need a second inner join and how is this written?

Pat

Than

 

[DCrake]

What fields are in the rating table? or do you have a seprate table for skippers?

 

[Simon_MT]

I think you have missed out the Table Race_Dates.

Also trying going up the heirarchy of tables - starting at the bottom.

Simon

 

[vbaInet]

SELECT Race_Dates.RaceDate, Race_Dates.NrEntries, Rating.Skipper_ID, Rating.Rank, Skippers.ID
FROM Skippers INNER JOIN Rating ON Rating.Skipper_ID = Skippers.ID;

Also changed to Group by SkipperID and then Date

On running the report the system then asked for Race_Date & Nr.Entries.
Do I need a second inner join and how is this written?

Just to prompt you, the spellings are not the same. If it's not a typo then that could be your problem.

 

[PatD]

Sorry David, you were right – it was a typo (will try and do better in the future!!). The following is the new Query which now works OK when just running the query. It lists each Skipper ID in turn but thereafter not in Date Sequence.

SELECT Rating.Skipper_ID, Race_Dates.RaceDate, Rating.Rank, Race_Dates.NrEntries
FROM Skippers INNER JOIN (Race_Dates INNER JOIN Rating ON Race_Dates.RaceDate = Rating.Race_Date) ON Skippers.ID = Rating.Skipper_ID;
The Skipper table has 3 columns - ID, First & Last Name only
The Form Button Event – To produce the Report, reads as follows:
Private Sub CmdOpenRatingReport_Click()
Dim StrWhere, StrReport, Str1stName, Str2ndName As String
StrReport = "RptSkipperRaceResults"
StrWhere = "AND Str1stName = LstSkipperID.Column(1)"
StrWhere = StrWhere & "AND Str2ndName = LstSkipperID.Column(2)"
Skipper_ID = LstSkipperID.Column(0)
DoCmd.OpenReport StrReport, acViewPreview, WhereCondition:=Skipper_ID
End Sub

The Report has every other page blank. Also on the actually selected Skipper_ID there is a second record for the same date with unknown data. Running just the Query, there is no duplication or bad records. I have tried using =[Skipper_ID] in the WhereCondition.
Simon, I am not sure what you mean by going up the heirachy of tables, or does the combined INNER JOINS cover rthis?
All ideas gratefully received
Pat

 

[DCrake]

Can you post a sample mdb/accdb to look at?

 

[PatD]

David,
Sorry for delay due unforseen circumstances
File attached as requested in Access 2007

 

[DCrake]

See revision

 

[Simon_MT]

I have two questions.

If the SkipperID is unique there is no need for the str1stName and str2ndName are superfluous.

The method of calculation seems a little odd I thought it would be more along the lines of:

100*([NrEntries]-[Rank]+1)/[NrEntries]

I don't use subs and this is my simple way of calling the the report:

Function RatingReport_Click()
    With CodeContextObject
        DoCmd.OpenReport "RptSkipperRaceResults", acViewPreview, "", "Skipper_ID = " & .LstSkipperID.Column(0)
    End With
End Function

Using a query

SELECT Rating.Skipper_ID, [FirstName] & " " & [LastName] AS [Skipper Name], Skippers.LastName, Skippers.FirstName, Race_Dates.RaceDate, Rating.Rank, Race_Dates.NrEntries
FROM Skippers INNER JOIN (Race_Dates INNER JOIN Rating ON Race_Dates.RaceDate = Rating.Race_Date) ON Skippers.ID = Rating.Skipper_ID;

Simon

 

[PatD]

Simon,
Many, many thanks - I could never have sorted it out myself. Everthing works as planned.
On your two questions the 1st/2nd name was me playing and never took it out or cleaned up.
The maths are a bit odd as I'm trying to see how Skippers progressively improve - or otherwise
Meanwhile thanks to all and hopefully it will help others.
Pat

 

[Simon_MT]

There are different methods of awarding points on race results, another method is to award points based on the number in the fleet but inverse. The idea is that more points are awarded to places the larger the fleet. I think you have to have a minimum of three boats.

Simon

 

[PatD]

Hi Simon,
Thanks for that, I like the idea of varying points on fleet size. These are Model Racing Yachts to a strict specification and races are not timed. The basic idea is to allow each skipper to see how they improve (or otherwise) over a number of races. Each Skipper is initialised with 90 points on his/her first race. For the next race the percentage race position is compared against the 90 points which then provides a new start points for the next race.
Pat

 

[Simon_MT]

Another way to calculate the points is based on position and the Number of Entries again the value of the points reflects how stiff the competition is:

(NrEntries+1) - Position

If there are three boats in the race the maximum value is 3 whilst if there are 20 boats the maximum value would be 20 This may distort the improvement analysis but if there are only three boats against 20 boat race (descending value from 20) giving a fixed value seems a little unfair as last place could get 18 points in a 3 boat race whilst only 1 point in a 20 boat race.

Simon

 

[Bukhari]

can any one help!
Question: attaching file in which u can see the form opening parties ledger with previous/opening balance. everything is working fine. But whenever i am trying to import external data it's not updating in customer_name and Category of IPRN table. i need to import external data.

an immediate response will be highly appreciated. Or if any one can make a db for me of accounting system in which i can import external data then i can pay him.