Solved Recordset (1 Viewer)

[Momma]

I want to select data from tblMedicalRecords which match a certain critera and write them to a table called tblWeights. Below is an example of what the SQL query would look like.
The records from the table tblDogs have to match the value that is in field ReproductionID with the value in variable ReproID. Those records' DogID should then match up with the value in Dogid from table tblMedicalTreatments where TreatmentTypeID in tblMedicalTreatments should equal 7.
The selected records should then be written to table tblWeights.
I know this should be done within a recordset and I'm not at all familiar with recordsets yet.
I would very much appreciate help in setting up a recordset that will give me the result as explained above.

SQLQuery:
SELECT tblDogs.PuppyNumber, tblMedicalTreatments.TreatmentDate, tblMedicalTreatments.Weight, tblMedicalTreatments.TreatmentTypeID
FROM tblMedicalTreatments INNER JOIN tblDogs ON tblMedicalTreatments.DogID = tblDogs.DogID
WHERE (((tblMedicalTreatments.TreatmentTypeID)=7))
ORDER BY tblDogs.PuppyNumber;

    Dim sql As String
    Dim MotherID As Long
    MotherID = Me.DogID
    Dim Mother As String
    Mother = Me.CallName
    Dim Litternumber As Long
    Litternumber = InputBox("Enter Litter number")
    Dim ReproID As Long
    ReproID = DLookup("reproductionid", "[tblreproduction]", "[motherid]=" & [MotherID] & " and [mlittercount] = " & Litternumber & "")
    DoCmd.OpenQuery "Empty tblWeights"

' Start Recordset....
    
    DoCmd.RunSQL "INSERT INTO tblWeights (Puppynumber, Weight, TreatmentDate) " & _
        "VALUES (" & PuppyNumber & ", " & Weight & ",,#" & TreatmentDate & "#)"

'.....Close Recordset

 

[MarkK]

Why do you need to write them to a table? That is an unusual step.
Typically you design a database in which the tables store the raw data. Then, as needed, you write queries to retrieve that data. If your table design is correct, you should not need query your data, and write it to a table. Just use the query.
hth

 

[Momma]

Let me explain why I do it this way.
I read the data from tblWeights into an excel spreadsheet to create a pivot table. The whole process will be automated with only one input from the user, which is the litter number.

 

[June7]

Why do you think should be done with recordset?

Why are you opening a query object?

Export query instead of saving to table.

Why do you need to pivot in Excel? Could a CROSSTAB query serve purpose?

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[Momma]

Why do you think should be done with recordset?

Why are you opening a query object?

Export query instead of saving to table.

Why do you need to pivot in Excel? Could a CROSSTAB query serve purpose?

If you want to provide db for analysis, follow instructions at bottom of my post.

I thought it require a recordset because there are multiple records to select.
If I export the query, I need a few user inputs where as in vba I can collect all the values with only one user input.
I need to have the CROSSTAB in report format and be able to print it. It's way easier to do it in Excel.
I don't see the instructions, you mention, in your post.

 

[June7]

Sorry, I don't understand your reasons.

I know this report output can be handled completely within Access, which would be my preference.

Instructions in fine print just below this line.

 

[Momma]

Sorry, I don't understand your reasons.

I know this report output can be handled completely within Access, which would be my preference.

Instructions in fine print just below this line.

Do you or anyone else have an example database with a printable CrossTab report?

 

[arnelgp]

he records from the table tblDogs have to match the value that is in field ReproductionID

what is the fieldname of ReproductionID field to your tblDogs table?

 

[June7]

Building a stable report based on CROSSTAB can be difficult but not impossible. This article has lots of information, including link to a sample db http://allenbrowne.com/ser-67.html

It would be easier and faster to advise if we had your data to work with.

 

[arnelgp]

    Dim sql As String
    Dim MotherID As Long
    MotherID = Me.DogID
    Dim Mother As String
    Mother = Me.CallName
    Dim Litternumber As Long
    Litternumber = InputBox("Enter Litter number")
    Dim ReproID As Long
    ReproID = DLookup("reproductionid", "[tblreproduction]", "[motherid]=" & [MotherID] & " and [mlittercount] = " & Litternumber & "")
    DoCmd.OpenQuery "Empty tblWeights"

' Start Recordset....
    
    'arnelgp
    Dim sql As String
    sql = "SELECT Puppynumber, Weight, TreatmentDate FROM " & _
          "(SELECT tblDogs.PuppyNumber, tblMedicalTreatments.TreatmentDate, tblMedicalTreatments.Weight, tblMedicalTreatments.TreatmentTypeID " & _
          "FROM tblMedicalTreatments INNER JOIN tblDogs ON tblMedicalTreatments.DogID = tblDogs.DogID " & _
          "WHERE (((tblMedicalTreatments.TreatmentTypeID) = 7)) AND tblDogs.ReproductionID = " & ReproID & " " & _
          "ORDER BY tblDogs.PuppyNumber)"
    
    DoCmd.RunSQL "INSERT INTO tblWeights (Puppynumber, Weight, TreatmentDate) " & sql

 

[Momma]

what is the fieldname of ReproductionID field to your tblDogs table?

That is ReproductionID. This field defines the puppies belonging to a specific litter in my Reproduction table.

 

[Momma]

Building a stable report based on CROSSTAB can be difficult but not impossible. This article has lots of information, including link to a sample db http://allenbrowne.com/ser-67.html

It would be easier and faster to advise if we had your data to work with.

I'm able to do the CrossTab querry as explained in the allenbrowne link. I do not have the knowledge to take it further in order to format the report in the way I want it displayed and also print it. I would love to know how to do it all in Access. I have two more reports which I have to use a CrossTab for.
I still don't get the fine print instructions.

 

[Momma]

    Dim sql As String
    Dim MotherID As Long
    MotherID = Me.DogID
    Dim Mother As String
    Mother = Me.CallName
    Dim Litternumber As Long
    Litternumber = InputBox("Enter Litter number")
    Dim ReproID As Long
    ReproID = DLookup("reproductionid", "[tblreproduction]", "[motherid]=" & [MotherID] & " and [mlittercount] = " & Litternumber & "")
    DoCmd.OpenQuery "Empty tblWeights"

' Start Recordset....
   
    'arnelgp
    Dim sql As String
    sql = "SELECT Puppynumber, Weight, TreatmentDate FROM " & _
          "(SELECT tblDogs.PuppyNumber, tblMedicalTreatments.TreatmentDate, tblMedicalTreatments.Weight, tblMedicalTreatments.TreatmentTypeID " & _
          "FROM tblMedicalTreatments INNER JOIN tblDogs ON tblMedicalTreatments.DogID = tblDogs.DogID " & _
          "WHERE (((tblMedicalTreatments.TreatmentTypeID) = 7)) AND tblDogs.ReproductionID = " & ReproID & " " & _
          "ORDER BY tblDogs.PuppyNumber)"
   
    DoCmd.RunSQL "INSERT INTO tblWeights (Puppynumber, Weight, TreatmentDate) " & sql

Thank you so much, Arnel, this is working perfectly. I wasn't sure about the Select statement.

 

[June7]

Did you read instructions? Exactly what did you not understand?