Queries, Recordset, and VB

[Bianca.Sayan]

I hope this is the appropriate group for a question like this, which
involves Queries, Recordset, and VB. Okay, its looking as though I
might be over my head with some code here. Any help would be
appreciated.

I have a table in my microsoft access that looks a bit like this:

Invasive Species
______________
bears
bears, raccoons
raccoons
raccoons, skunks
skunks
skunks
raccoons



What I'd like to do with this table is create a new recordset where
the records with more than one animal delimited by a comma becomes 2
records
so one record that is "bears, raccoons" becomes 2 records, "bears" and
"raccoons" respectively.
so, it will look like this.

Invasive Species
______________
bears
bears
raccoons
raccoons
raccoons
skunks
skunks
skunks
raccoons


Then, I want to tally all the distinct entries up so that it will look
like this

Invasive Species Count
________________________________________________________
bears 2
skunks 3
raccoons 4



Then I'd like to throw that into a report.....


I've written some code. I suspect its bad. My first clue is that it
doesnt work. My second clue is I'm not sure I constructively know how
to go about fixing it. Here is the code:

_____________________________________________________________________________

Dim db As Database
Dim rs As Recordset
Dim thequery As QueryDef
Dim mysqlquery As String
Dim newthequery As QueryDef
Dim newmysqlquery As String
Dim newrs As Recordset
Dim finalrs As Recordset
Dim shorttemp As String
Dim longtemp As String
Set mysqlquery = "SELECT [Part F - Biodiversity #11-21].F14v1 AS
F14v1 FROM [Part F - Biodiversity #11-21] WHERE (((IsNull([F14v1])) =
False));"
Set db = CurrentDb()
Set thequery = db.CreateQueryDef("Invasives", mysqlquery)
Set rs = thequery.OpenRecordset()
Print rs.RecordCount
Me.Text28.Value = rs.Fields("F14v1").Value
While (rs.EOF = False)
longtemp = rs.Fields("F14v1").Value
While (Not InStr("", longtemp, ",") = 0)
shorttemp = Left(longtemp, (InStr("", ",", longtemp) -
1)).Trim
longtemp = Right(longtemp, (InStr("", ",", longtemp) +
1)).Trim
newrs.AddNew
newrs.Fields("LongUncountedList").Value = shorttemp
End If
newrs.AddNew
newrs.Fields("LongUncountedList").Value = longtemp
rs.MoveNext
End If

Set newmysqlquery = "SELECT DISTINCT LongUncountedList AS names,
Count(LongUncountedList) AS tally FROM Invasives WHERE
(((IsNull(LongUncountedList)) = False)) GROUP BY LongUncountedList;"
Set newthequery = db.CreateQueryDef("FinalInvasives",
newmysqlquery)
Me.RecordSource = newthequery.OpenRecordset()



_____________________________________________________________________________



So, can anyone give me a hint as to what the problem(s) are? Or
alternative methods of going about it?

I suspect it relates to my defiencies in understanding exactly how to
use VB to interact with the Access objects.

 

[Guest]

Create a table of unique species

tblInvasiveSpecies
=================
InvSpec

Add each unique value "bears","Raccoons","Skunks",...

Add this table to a query with your other table and set the criteria under
[Invasive Species] to:
Like "*" & [InvSpec] & "*"
Add the InvSpec field to the query to get all the values that can later be
used in a totals query.

--
Duane Hookom
Microsoft Access MVP

I hope this is the appropriate group for a question like this, which
involves Queries, Recordset, and VB. Okay, its looking as though I
might be over my head with some code here. Any help would be
appreciated.

I have a table in my microsoft access that looks a bit like this:

Invasive Species
______________
bears
bears, raccoons
raccoons
raccoons, skunks
skunks
skunks
raccoons



What I'd like to do with this table is create a new recordset where
the records with more than one animal delimited by a comma becomes 2
records
so one record that is "bears, raccoons" becomes 2 records, "bears" and
"raccoons" respectively.
so, it will look like this.

Invasive Species
______________
bears
bears
raccoons
raccoons
raccoons
skunks
skunks
skunks
raccoons


Then, I want to tally all the distinct entries up so that it will look
like this

Invasive Species Count
________________________________________________________
bears 2
skunks 3
raccoons 4



Then I'd like to throw that into a report.....


I've written some code. I suspect its bad. My first clue is that it
doesnt work. My second clue is I'm not sure I constructively know how
to go about fixing it. Here is the code:

_____________________________________________________________________________

Dim db As Database
Dim rs As Recordset
Dim thequery As QueryDef
Dim mysqlquery As String
Dim newthequery As QueryDef
Dim newmysqlquery As String
Dim newrs As Recordset
Dim finalrs As Recordset
Dim shorttemp As String
Dim longtemp As String
Set mysqlquery = "SELECT [Part F - Biodiversity #11-21].F14v1 AS
F14v1 FROM [Part F - Biodiversity #11-21] WHERE (((IsNull([F14v1])) =
False));"
Set db = CurrentDb()
Set thequery = db.CreateQueryDef("Invasives", mysqlquery)
Set rs = thequery.OpenRecordset()
Print rs.RecordCount
Me.Text28.Value = rs.Fields("F14v1").Value
While (rs.EOF = False)
longtemp = rs.Fields("F14v1").Value
While (Not InStr("", longtemp, ",") = 0)
shorttemp = Left(longtemp, (InStr("", ",", longtemp) -
1)).Trim
longtemp = Right(longtemp, (InStr("", ",", longtemp) +
1)).Trim
newrs.AddNew
newrs.Fields("LongUncountedList").Value = shorttemp
End If
newrs.AddNew
newrs.Fields("LongUncountedList").Value = longtemp
rs.MoveNext
End If

Set newmysqlquery = "SELECT DISTINCT LongUncountedList AS names,
Count(LongUncountedList) AS tally FROM Invasives WHERE
(((IsNull(LongUncountedList)) = False)) GROUP BY LongUncountedList;"
Set newthequery = db.CreateQueryDef("FinalInvasives",
newmysqlquery)
Me.RecordSource = newthequery.OpenRecordset()



_____________________________________________________________________________



So, can anyone give me a hint as to what the problem(s) are? Or
alternative methods of going about it?

I suspect it relates to my defiencies in understanding exactly how to
use VB to interact with the Access objects.

 

[Bianca.Sayan]

I thought of this before. but, if I understand correctly, I would have
to know all the unique values beforehand. I don't. I have to derive
the list of unique entries dynamically from the original table. And
the problem with that is some unique entries are buried in the records
with several entries (like: if "bear" is unique I wont be able to get
"bear" from "raccoon, bear, skunk" I'll just get "raccoon, bear,
skunk" as a unique entry)

That's why my reasoning is pushing me towards my original strategy...
Am I missing something?

Create a table of unique species

tblInvasiveSpecies
=================
InvSpec

Add each unique value "bears","Raccoons","Skunks",...

Add this table to a query with your other table and set the criteria under
[Invasive Species] to:
Like "*" & [InvSpec] & "*"
Add the InvSpec field to the query to get all the values that can later be
used in a totals query.

--
Duane Hookom
Microsoft Access MVP

I hope this is the appropriate group for a question like this, which
involves Queries, Recordset, and VB. Okay, its looking as though I
might be over my head with some code here. Any help would be
appreciated.

I have a table in my microsoft access that looks a bit like this:

Invasive Species
______________
bears
bears, raccoons
raccoons
raccoons, skunks
skunks
skunks
raccoons

What I'd like to do with this table is create a new recordset where
the records with more than one animal delimited by a comma becomes 2
records
so one record that is "bears, raccoons" becomes 2 records, "bears" and
"raccoons" respectively.
so, it will look like this.

Invasive Species
______________
bears
bears
raccoons
raccoons
raccoons
skunks
skunks
skunks
raccoons

Then, I want to tally all the distinct entries up so that it will look
like this

Invasive Species Count
________________________________________________________
bears 2
skunks 3
raccoons 4

Then I'd like to throw that into a report.....

I've written some code. I suspect its bad. My first clue is that it
doesnt work. My second clue is I'm not sure I constructively know how
to go about fixing it. Here is the code:

_____________________________________________________________________________

Dim db As Database
Dim rs As Recordset
Dim thequery As QueryDef
Dim mysqlquery As String
Dim newthequery As QueryDef
Dim newmysqlquery As String
Dim newrs As Recordset
Dim finalrs As Recordset
Dim shorttemp As String
Dim longtemp As String
Set mysqlquery = "SELECT [Part F - Biodiversity #11-21].F14v1 AS
F14v1 FROM [Part F - Biodiversity #11-21] WHERE (((IsNull([F14v1])) =
False));"
Set db = CurrentDb()
Set thequery = db.CreateQueryDef("Invasives", mysqlquery)
Set rs = thequery.OpenRecordset()
Print rs.RecordCount
Me.Text28.Value = rs.Fields("F14v1").Value
While (rs.EOF = False)
longtemp = rs.Fields("F14v1").Value
While (Not InStr("", longtemp, ",") = 0)
shorttemp = Left(longtemp, (InStr("", ",", longtemp) -
1)).Trim
longtemp = Right(longtemp, (InStr("", ",", longtemp) +
1)).Trim
newrs.AddNew
newrs.Fields("LongUncountedList").Value = shorttemp
End If
newrs.AddNew
newrs.Fields("LongUncountedList").Value = longtemp
rs.MoveNext
End If

Set newmysqlquery = "SELECT DISTINCT LongUncountedList AS names,
Count(LongUncountedList) AS tally FROM Invasives WHERE
(((IsNull(LongUncountedList)) = False)) GROUP BY LongUncountedList;"
Set newthequery = db.CreateQueryDef("FinalInvasives",
newmysqlquery)
Me.RecordSource = newthequery.OpenRecordset()

So, can anyone give me a hint as to what the problem(s) are? Or
alternative methods of going about it?

I suspect it relates to my defiencies in understanding exactly how to
use VB to interact with the Access objects.

 

[Guest]

I wasn't aware there would be an issue creating the unique species table. You
may need to write some code to loop through the comma separated values to
build the table. Then use the table as suggested once it has been created.
There is a Split() function that might be helpful in separating out the
different species.
--
Duane Hookom
Microsoft Access MVP

I thought of this before. but, if I understand correctly, I would have
to know all the unique values beforehand. I don't. I have to derive
the list of unique entries dynamically from the original table. And
the problem with that is some unique entries are buried in the records
with several entries (like: if "bear" is unique I wont be able to get
"bear" from "raccoon, bear, skunk" I'll just get "raccoon, bear,
skunk" as a unique entry)

That's why my reasoning is pushing me towards my original strategy...
Am I missing something?

Create a table of unique species

tblInvasiveSpecies
=================
InvSpec

Add each unique value "bears","Raccoons","Skunks",...

Add this table to a query with your other table and set the criteria under
[Invasive Species] to:
Like "*" & [InvSpec] & "*"
Add the InvSpec field to the query to get all the values that can later be
used in a totals query.

--
Duane Hookom
Microsoft Access MVP

I hope this is the appropriate group for a question like this, which
involves Queries, Recordset, and VB. Okay, its looking as though I
might be over my head with some code here. Any help would be
appreciated.

I have a table in my microsoft access that looks a bit like this:

Invasive Species
______________
bears
bears, raccoons
raccoons
raccoons, skunks
skunks
skunks
raccoons

What I'd like to do with this table is create a new recordset where
the records with more than one animal delimited by a comma becomes 2
records
so one record that is "bears, raccoons" becomes 2 records, "bears" and
"raccoons" respectively.
so, it will look like this.

Invasive Species
______________
bears
bears
raccoons
raccoons
raccoons
skunks
skunks
skunks
raccoons

Then, I want to tally all the distinct entries up so that it will look
like this

Invasive Species Count
________________________________________________________
bears 2
skunks 3
raccoons 4

Then I'd like to throw that into a report.....

I've written some code. I suspect its bad. My first clue is that it
doesnt work. My second clue is I'm not sure I constructively know how
to go about fixing it. Here is the code:

_____________________________________________________________________________

Dim db As Database
Dim rs As Recordset
Dim thequery As QueryDef
Dim mysqlquery As String
Dim newthequery As QueryDef
Dim newmysqlquery As String
Dim newrs As Recordset
Dim finalrs As Recordset
Dim shorttemp As String
Dim longtemp As String
Set mysqlquery = "SELECT [Part F - Biodiversity #11-21].F14v1 AS
F14v1 FROM [Part F - Biodiversity #11-21] WHERE (((IsNull([F14v1])) =
False));"
Set db = CurrentDb()
Set thequery = db.CreateQueryDef("Invasives", mysqlquery)
Set rs = thequery.OpenRecordset()
Print rs.RecordCount
Me.Text28.Value = rs.Fields("F14v1").Value
While (rs.EOF = False)
longtemp = rs.Fields("F14v1").Value
While (Not InStr("", longtemp, ",") = 0)
shorttemp = Left(longtemp, (InStr("", ",", longtemp) -
1)).Trim
longtemp = Right(longtemp, (InStr("", ",", longtemp) +
1)).Trim
newrs.AddNew
newrs.Fields("LongUncountedList").Value = shorttemp
End If
newrs.AddNew
newrs.Fields("LongUncountedList").Value = longtemp
rs.MoveNext
End If

Set newmysqlquery = "SELECT DISTINCT LongUncountedList AS names,
Count(LongUncountedList) AS tally FROM Invasives WHERE
(((IsNull(LongUncountedList)) = False)) GROUP BY LongUncountedList;"
Set newthequery = db.CreateQueryDef("FinalInvasives",
newmysqlquery)
Me.RecordSource = newthequery.OpenRecordset()

So, can anyone give me a hint as to what the problem(s) are? Or
alternative methods of going about it?

I suspect it relates to my defiencies in understanding exactly how to
use VB to interact with the Access objects.

 

[Bianca.Sayan]

Yes, I agree, that seems like it would work. However, I think it would
require more work because in order to build the unique table I would
have to compare every entry to what i have in my unique table.
Whereas, my existing code would just get rid of the delimiters and let
this query:

SELECT DISTINCT LongUncountedList AS names,
Count(LongUncountedList) AS tally FROM Invasives WHERE
(((IsNull(LongUncountedList)) = False)) GROUP BY LongUncountedList;"

do all the work for me.

I think my problem isn't so much how to solve the general problem, but
why my code isn't working. Admittedly, I'm not well versed in vb
syntax or how to use access objects properly, so I don't rightly know
whether its actually retrieving the recordsets I want it to. Nor do I
know if I'm using the new recordset (newrs) properly, or if I'm
allowed to just assign a recordset to RecordSource like I did in the
last line.... Any hints along the lines of correct syntax would be
most helpful.

I wasn't aware there would be an issue creating the unique species table. You
may need to write some code to loop through the comma separated values to
build the table. Then use the table as suggested once it has been created.
There is a Split() function that might be helpful in separating out the
different species.
--
Duane Hookom
Microsoft Access MVP

I thought of this before. but, if I understand correctly, I would have
to know all the unique values beforehand. I don't. I have to derive
the list of unique entries dynamically from the original table. And
the problem with that is some unique entries are buried in the records
with several entries (like: if "bear" is unique I wont be able to get
"bear" from "raccoon, bear, skunk" I'll just get "raccoon, bear,
skunk" as a unique entry)

That's why my reasoning is pushing me towards my original strategy...
Am I missing something?

Create a table of unique species
tblInvasiveSpecies
=================
InvSpec
Add each unique value "bears","Raccoons","Skunks",...
Add this table to a query with your other table and set the criteria under
[Invasive Species] to:
Like "*" & [InvSpec] & "*"
Add the InvSpec field to the query to get all the values that can later be
used in a totals query.
--
Duane Hookom
Microsoft Access MVP
:
I hope this is the appropriate group for a question like this, which
involves Queries, Recordset, and VB. Okay, its looking as though I
might be over my head with some code here. Any help would be
appreciated.
I have a table in my microsoft access that looks a bit like this:
Invasive Species
______________
bears
bears, raccoons
raccoons
raccoons, skunks
skunks
skunks
raccoons
What I'd like to do with this table is create a new recordset where
the records with more than one animal delimited by a comma becomes 2
records
so one record that is "bears, raccoons" becomes 2 records, "bears" and
"raccoons" respectively.
so, it will look like this.
Invasive Species
______________
bears
bears
raccoons
raccoons
raccoons
skunks
skunks
skunks
raccoons
Then, I want to tally all the distinct entries up so that it will look
like this
Invasive Species Count
________________________________________________________
bears 2
skunks 3
raccoons 4
Then I'd like to throw that into a report.....
I've written some code. I suspect its bad. My first clue is that it
doesnt work. My second clue is I'm not sure I constructively know how
to go about fixing it. Here is the code:
_____________________________________________________________________________
Dim db As Database
Dim rs As Recordset
Dim thequery As QueryDef
Dim mysqlquery As String
Dim newthequery As QueryDef
Dim newmysqlquery As String
Dim newrs As Recordset
Dim finalrs As Recordset
Dim shorttemp As String
Dim longtemp As String
Set mysqlquery = "SELECT [Part F - Biodiversity #11-21].F14v1 AS
F14v1 FROM [Part F - Biodiversity #11-21] WHERE (((IsNull([F14v1])) =
False));"
Set db = CurrentDb()
Set thequery = db.CreateQueryDef("Invasives", mysqlquery)
Set rs = thequery.OpenRecordset()
Print rs.RecordCount
Me.Text28.Value = rs.Fields("F14v1").Value
While (rs.EOF = False)
longtemp = rs.Fields("F14v1").Value
While (Not InStr("", longtemp, ",") = 0)
shorttemp = Left(longtemp, (InStr("", ",", longtemp) -
1)).Trim
longtemp = Right(longtemp, (InStr("", ",", longtemp) +
1)).Trim
newrs.AddNew
newrs.Fields("LongUncountedList").Value = shorttemp
End If
newrs.AddNew
newrs.Fields("LongUncountedList").Value = longtemp
rs.MoveNext
End If
Set newmysqlquery = "SELECT DISTINCT LongUncountedList AS names,
Count(LongUncountedList) AS tally FROM Invasives WHERE
(((IsNull(LongUncountedList)) = False)) GROUP BY LongUncountedList;"
Set newthequery = db.CreateQueryDef("FinalInvasives",
newmysqlquery)
Me.RecordSource = newthequery.OpenRecordset()
_____________________________________________________________________________
So, can anyone give me a hint as to what the problem(s) are? Or
alternative methods of going about it?
I suspect it relates to my defiencies in understanding exactly how to
use VB to interact with the Access objects.

 

[Guest]

I would create/update the tblSpecies with code like:
Function GetUniqueSpecies()
'Table Name: Part F - Biodiversity #11-21
'Field Name: F14v1
Dim db As DAO.Database
Dim rsData As DAO.Recordset
Dim arSpecies As Variant
Dim i As Integer
Dim strSQL As String
Dim strSpecies As String
Set db = CurrentDb
strSQL = "SELECT DISTINCT F14v1 FROM [Part F - Biodiversity #11-21] " & _
"WHERE F14v1 Is Not Null"
Set rsData = db.OpenRecordset(strSQL)
With rsData
Do Until .EOF
arSpecies = Split(!F14v1, ",")
'Debug.Print !F14v1
For i = 0 To UBound(arSpecies)
strSQL = "INSERT INTO tblSpecies (Species) Values (""" & _
Trim(arSpecies(i)) & """)"
db.Execute strSQL
Next

.MoveNext
Loop
.Close
End With
Set rsData = Nothing
Set db = Nothing
End Function

Then your final query would be something like:

SELECT tblSpecies.Species, Count(tblSpecies.Species) AS CountOfSpecies
FROM tblSpecies, [Part F - Biodiversity #11-21]
WHERE ((([Part F - Biodiversity #11-21].F14v1) Like "*" & [Species] & "*"))
GROUP BY tblSpecies.Species;
--
Duane Hookom
Microsoft Access MVP

Yes, I agree, that seems like it would work. However, I think it would
require more work because in order to build the unique table I would
have to compare every entry to what i have in my unique table.
Whereas, my existing code would just get rid of the delimiters and let
this query:

SELECT DISTINCT LongUncountedList AS names,
Count(LongUncountedList) AS tally FROM Invasives WHERE
(((IsNull(LongUncountedList)) = False)) GROUP BY LongUncountedList;"

do all the work for me.

I think my problem isn't so much how to solve the general problem, but
why my code isn't working. Admittedly, I'm not well versed in vb
syntax or how to use access objects properly, so I don't rightly know
whether its actually retrieving the recordsets I want it to. Nor do I
know if I'm using the new recordset (newrs) properly, or if I'm
allowed to just assign a recordset to RecordSource like I did in the
last line.... Any hints along the lines of correct syntax would be
most helpful.

I wasn't aware there would be an issue creating the unique species table. You
may need to write some code to loop through the comma separated values to
build the table. Then use the table as suggested once it has been created.
There is a Split() function that might be helpful in separating out the
different species.
--
Duane Hookom
Microsoft Access MVP

I thought of this before. but, if I understand correctly, I would have
to know all the unique values beforehand. I don't. I have to derive
the list of unique entries dynamically from the original table. And
the problem with that is some unique entries are buried in the records
with several entries (like: if "bear" is unique I wont be able to get
"bear" from "raccoon, bear, skunk" I'll just get "raccoon, bear,
skunk" as a unique entry)

That's why my reasoning is pushing me towards my original strategy...
Am I missing something?

On Aug 23, 10:00 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
Create a table of unique species

Add each unique value "bears","Raccoons","Skunks",...

Add this table to a query with your other table and set the criteria under
[Invasive Species] to:
Like "*" & [InvSpec] & "*"
Add the InvSpec field to the query to get all the values that can later be
used in a totals query.

:
I hope this is the appropriate group for a question like this, which
involves Queries, Recordset, and VB. Okay, its looking as though I
might be over my head with some code here. Any help would be
appreciated.

I have a table in my microsoft access that looks a bit like this:

Invasive Species
______________
bears
bears, raccoons
raccoons
raccoons, skunks
skunks
skunks
raccoons

What I'd like to do with this table is create a new recordset where
the records with more than one animal delimited by a comma becomes 2
records
so one record that is "bears, raccoons" becomes 2 records, "bears" and
"raccoons" respectively.
so, it will look like this.

Invasive Species
______________
bears
bears
raccoons
raccoons
raccoons
skunks
skunks
skunks
raccoons

Then, I want to tally all the distinct entries up so that it will look
like this

Invasive Species Count
________________________________________________________
bears 2
skunks 3
raccoons 4

Then I'd like to throw that into a report.....

I've written some code. I suspect its bad. My first clue is that it
doesnt work. My second clue is I'm not sure I constructively know how
to go about fixing it. Here is the code:

_____________________________________________________________________________

Dim db As Database
Dim rs As Recordset
Dim thequery As QueryDef
Dim mysqlquery As String
Dim newthequery As QueryDef
Dim newmysqlquery As String
Dim newrs As Recordset
Dim finalrs As Recordset
Dim shorttemp As String
Dim longtemp As String
Set mysqlquery = "SELECT [Part F - Biodiversity #11-21].F14v1 AS
F14v1 FROM [Part F - Biodiversity #11-21] WHERE (((IsNull([F14v1])) =
False));"
Set db = CurrentDb()
Set thequery = db.CreateQueryDef("Invasives", mysqlquery)
Set rs = thequery.OpenRecordset()
Print rs.RecordCount
Me.Text28.Value = rs.Fields("F14v1").Value
While (rs.EOF = False)
longtemp = rs.Fields("F14v1").Value
While (Not InStr("", longtemp, ",") = 0)
shorttemp = Left(longtemp, (InStr("", ",", longtemp) -
1)).Trim
longtemp = Right(longtemp, (InStr("", ",", longtemp) +
1)).Trim
newrs.AddNew
newrs.Fields("LongUncountedList").Value = shorttemp
End If
newrs.AddNew
newrs.Fields("LongUncountedList").Value = longtemp
rs.MoveNext
End If

Set newmysqlquery = "SELECT DISTINCT LongUncountedList AS names,
Count(LongUncountedList) AS tally FROM Invasives WHERE
(((IsNull(LongUncountedList)) = False)) GROUP BY LongUncountedList;"
Set newthequery = db.CreateQueryDef("FinalInvasives",
newmysqlquery)
Me.RecordSource = newthequery.OpenRecordset()

So, can anyone give me a hint as to what the problem(s) are? Or
alternative methods of going about it?

I suspect it relates to my defiencies in understanding exactly how to
use VB to interact with the Access objects.

 

[Bianca.Sayan]

Works like a charm! This is much neater than mine. Thanks so much,
Duane. This should help alot when I wrestle with VB next time.

I would create/update the tblSpecies with code like:
Function GetUniqueSpecies()
'Table Name: Part F - Biodiversity #11-21
'Field Name: F14v1
Dim db As DAO.Database
Dim rsData As DAO.Recordset
Dim arSpecies As Variant
Dim i As Integer
Dim strSQL As String
Dim strSpecies As String
Set db = CurrentDb
strSQL = "SELECT DISTINCT F14v1 FROM [Part F - Biodiversity #11-21] " & _
"WHERE F14v1 Is Not Null"
Set rsData = db.OpenRecordset(strSQL)
With rsData
Do Until .EOF
arSpecies = Split(!F14v1, ",")
'Debug.Print !F14v1
For i = 0 To UBound(arSpecies)
strSQL = "INSERT INTO tblSpecies (Species) Values (""" & _
Trim(arSpecies(i)) & """)"
db.Execute strSQL
Next

.MoveNext
Loop
.Close
End With
Set rsData = Nothing
Set db = Nothing
End Function

Then your final query would be something like:

SELECT tblSpecies.Species, Count(tblSpecies.Species) AS CountOfSpecies
FROM tblSpecies, [Part F - Biodiversity #11-21]
WHERE ((([Part F - Biodiversity #11-21].F14v1) Like "*" & [Species] & "*"))
GROUP BY tblSpecies.Species;
--
Duane Hookom
Microsoft Access MVP

Yes, I agree, that seems like it would work. However, I think it would
require more work because in order to build the unique table I would
have to compare every entry to what i have in my unique table.
Whereas, my existing code would just get rid of the delimiters and let
this query:

SELECT DISTINCT LongUncountedList AS names,
Count(LongUncountedList) AS tally FROM Invasives WHERE
(((IsNull(LongUncountedList)) = False)) GROUP BY LongUncountedList;"

do all the work for me.

I think my problem isn't so much how to solve the general problem, but
why my code isn't working. Admittedly, I'm not well versed in vb
syntax or how to use access objects properly, so I don't rightly know
whether its actually retrieving the recordsets I want it to. Nor do I
know if I'm using the new recordset (newrs) properly, or if I'm
allowed to just assign a recordset to RecordSource like I did in the
last line.... Any hints along the lines of correct syntax would be
most helpful.

I wasn't aware there would be an issue creating the unique species table. You
may need to write some code to loop through the comma separated values to
build the table. Then use the table as suggested once it has been created.
There is a Split() function that might be helpful in separating out the
different species.
--
Duane Hookom
Microsoft Access MVP
:
I thought of this before. but, if I understand correctly, I would have
to know all the unique values beforehand. I don't. I have to derive
the list of unique entries dynamically from the original table. And
the problem with that is some unique entries are buried in the records
with several entries (like: if "bear" is unique I wont be able to get
"bear" from "raccoon, bear, skunk" I'll just get "raccoon, bear,
skunk" as a unique entry)
That's why my reasoning is pushing me towards my original strategy...
Am I missing something?
On Aug 23, 10:00 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
Create a table of unique species
tblInvasiveSpecies
=================
InvSpec
Add each unique value "bears","Raccoons","Skunks",...
Add this table to a query with your other table and set the criteria under
[Invasive Species] to:
Like "*" & [InvSpec] & "*"
Add the InvSpec field to the query to get all the values that can later be
used in a totals query.
--
Duane Hookom
Microsoft Access MVP
:
I hope this is the appropriate group for a question like this, which
involves Queries, Recordset, and VB. Okay, its looking as though I
might be over my head with some code here. Any help would be
appreciated.
I have a table in my microsoft access that looks a bit like this:
Invasive Species
______________
bears
bears, raccoons
raccoons
raccoons, skunks
skunks
skunks
raccoons
What I'd like to do with this table is create a new recordset where
the records with more than one animal delimited by a comma becomes 2
records
so one record that is "bears, raccoons" becomes 2 records, "bears" and
"raccoons" respectively.
so, it will look like this.
Invasive Species
______________
bears
bears
raccoons
raccoons
raccoons
skunks
skunks
skunks
raccoons
Then, I want to tally all the distinct entries up so that it will look
like this
Invasive Species Count
________________________________________________________
bears 2
skunks 3
raccoons 4
Then I'd like to throw that into a report.....
I've written some code. I suspect its bad. My first clue is that it
doesnt work. My second clue is I'm not sure I constructively know how
to go about fixing it. Here is the code:
_____________________________________________________________________________
Dim db As Database
Dim rs As Recordset
Dim thequery As QueryDef
Dim mysqlquery As String
Dim newthequery As QueryDef
Dim newmysqlquery As String
Dim newrs As Recordset
Dim finalrs As Recordset
Dim shorttemp As String
Dim longtemp As String
Set mysqlquery = "SELECT [Part F - Biodiversity #11-21].F14v1 AS
F14v1 FROM [Part F - Biodiversity #11-21] WHERE (((IsNull([F14v1])) =
False));"
Set db = CurrentDb()
Set thequery = db.CreateQueryDef("Invasives", mysqlquery)
Set rs = thequery.OpenRecordset()
Print rs.RecordCount
Me.Text28.Value = rs.Fields("F14v1").Value
While (rs.EOF = False)
longtemp = rs.Fields("F14v1").Value
While (Not InStr("", longtemp, ",") = 0)
shorttemp = Left(longtemp, (InStr("", ",", longtemp) -
1)).Trim
longtemp = Right(longtemp, (InStr("", ",", longtemp) +
1)).Trim
newrs.AddNew
newrs.Fields("LongUncountedList").Value = shorttemp
End If
newrs.AddNew
newrs.Fields("LongUncountedList").Value = longtemp
rs.MoveNext
End If
Set newmysqlquery = "SELECT DISTINCT LongUncountedList AS names,
Count(LongUncountedList) AS tally FROM Invasives WHERE
(((IsNull(LongUncountedList)) = False)) GROUP BY LongUncountedList;"
Set newthequery = db.CreateQueryDef("FinalInvasives",
newmysqlquery)
Me.RecordSource = newthequery.OpenRecordset()
_____________________________________________________________________________
So, can anyone give me a hint as to what the problem(s) are? Or
alternative methods of going about it?
I suspect it relates to my defiencies in understanding exactly how to
use VB to interact with the Access objects.

 

[wphx]

I've done a fair-bit of parsing of data from various sources into access
tables. I usually do a trade-off of effort. The VB code looks great.
Alternatively, if its a one-off situation and if you know that you may only
have 2 or 3 animals listed in a single field, you could create an append
query and run it three times, each time modifying it to pick up the first
animal, second animal, third animal etc.

Works like a charm! This is much neater than mine. Thanks so much,
Duane. This should help alot when I wrestle with VB next time.

I would create/update the tblSpecies with code like:
Function GetUniqueSpecies()
'Table Name: Part F - Biodiversity #11-21
'Field Name: F14v1
Dim db As DAO.Database
Dim rsData As DAO.Recordset
Dim arSpecies As Variant
Dim i As Integer
Dim strSQL As String
Dim strSpecies As String
Set db = CurrentDb
strSQL = "SELECT DISTINCT F14v1 FROM [Part F - Biodiversity #11-21] "
& _
"WHERE F14v1 Is Not Null"
Set rsData = db.OpenRecordset(strSQL)
With rsData
Do Until .EOF
arSpecies = Split(!F14v1, ",")
'Debug.Print !F14v1
For i = 0 To UBound(arSpecies)
strSQL = "INSERT INTO tblSpecies (Species) Values (""" &
_
Trim(arSpecies(i)) & """)"
db.Execute strSQL
Next

.MoveNext
Loop
.Close
End With
Set rsData = Nothing
Set db = Nothing
End Function

Then your final query would be something like:

SELECT tblSpecies.Species, Count(tblSpecies.Species) AS CountOfSpecies
FROM tblSpecies, [Part F - Biodiversity #11-21]
WHERE ((([Part F - Biodiversity #11-21].F14v1) Like "*" & [Species] &
"*"))
GROUP BY tblSpecies.Species;
--
Duane Hookom
Microsoft Access MVP

Yes, I agree, that seems like it would work. However, I think it would
require more work because in order to build the unique table I would
have to compare every entry to what i have in my unique table.
Whereas, my existing code would just get rid of the delimiters and let
this query:

SELECT DISTINCT LongUncountedList AS names,
Count(LongUncountedList) AS tally FROM Invasives WHERE
(((IsNull(LongUncountedList)) = False)) GROUP BY LongUncountedList;"

do all the work for me.

I think my problem isn't so much how to solve the general problem, but
why my code isn't working. Admittedly, I'm not well versed in vb
syntax or how to use access objects properly, so I don't rightly know
whether its actually retrieving the recordsets I want it to. Nor do I
know if I'm using the new recordset (newrs) properly, or if I'm
allowed to just assign a recordset to RecordSource like I did in the
last line.... Any hints along the lines of correct syntax would be
most helpful.

On Aug 24, 10:20 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
I wasn't aware there would be an issue creating the unique species
table. You
may need to write some code to loop through the comma separated
values to
build the table. Then use the table as suggested once it has been
created.
There is a Split() function that might be helpful in separating out
the
different species.

:
I thought of this before. but, if I understand correctly, I would
have
to know all the unique values beforehand. I don't. I have to derive
the list of unique entries dynamically from the original table. And
the problem with that is some unique entries are buried in the
records
with several entries (like: if "bear" is unique I wont be able to
get
"bear" from "raccoon, bear, skunk" I'll just get "raccoon, bear,
skunk" as a unique entry)

That's why my reasoning is pushing me towards my original
strategy...
Am I missing something?

On Aug 23, 10:00 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
Create a table of unique species

Add each unique value "bears","Raccoons","Skunks",...

Add this table to a query with your other table and set the
criteria under
[Invasive Species] to:
Like "*" & [InvSpec] & "*"
Add the InvSpec field to the query to get all the values that can
later be
used in a totals query.

:
I hope this is the appropriate group for a question like this,
which
involves Queries, Recordset, and VB. Okay, its looking as
though I
might be over my head with some code here. Any help would be
appreciated.

I have a table in my microsoft access that looks a bit like
this:

Invasive Species
______________
bears
bears, raccoons
raccoons
raccoons, skunks
skunks
skunks
raccoons

What I'd like to do with this table is create a new recordset
where
the records with more than one animal delimited by a comma
becomes 2
records
so one record that is "bears, raccoons" becomes 2 records,
"bears" and
"raccoons" respectively.
so, it will look like this.

Invasive Species
______________
bears
bears
raccoons
raccoons
raccoons
skunks
skunks
skunks
raccoons

Then, I want to tally all the distinct entries up so that it
will look
like this

Invasive Species Count
________________________________________________________
bears 2
skunks 3
raccoons 4

Then I'd like to throw that into a report.....

I've written some code. I suspect its bad. My first clue is
that it
doesnt work. My second clue is I'm not sure I constructively
know how
to go about fixing it. Here is the code:

_____________________________________________________________________________

Dim db As Database
Dim rs As Recordset
Dim thequery As QueryDef
Dim mysqlquery As String
Dim newthequery As QueryDef
Dim newmysqlquery As String
Dim newrs As Recordset
Dim finalrs As Recordset
Dim shorttemp As String
Dim longtemp As String
Set mysqlquery = "SELECT [Part F - Biodiversity
#11-21].F14v1 AS
F14v1 FROM [Part F - Biodiversity #11-21] WHERE
(((IsNull([F14v1])) =
False));"
Set db = CurrentDb()
Set thequery = db.CreateQueryDef("Invasives", mysqlquery)
Set rs = thequery.OpenRecordset()
Print rs.RecordCount
Me.Text28.Value = rs.Fields("F14v1").Value
While (rs.EOF = False)
longtemp = rs.Fields("F14v1").Value
While (Not InStr("", longtemp, ",") = 0)
shorttemp = Left(longtemp, (InStr("", ",",
longtemp) -
1)).Trim
longtemp = Right(longtemp, (InStr("", ",",
longtemp) +
1)).Trim
newrs.AddNew
newrs.Fields("LongUncountedList").Value = shorttemp
End If
newrs.AddNew
newrs.Fields("LongUncountedList").Value = longtemp
rs.MoveNext
End If

Set newmysqlquery = "SELECT DISTINCT LongUncountedList AS
names,
Count(LongUncountedList) AS tally FROM Invasives WHERE
(((IsNull(LongUncountedList)) = False)) GROUP BY
LongUncountedList;"
Set newthequery = db.CreateQueryDef("FinalInvasives",
newmysqlquery)
Me.RecordSource = newthequery.OpenRecordset()

So, can anyone give me a hint as to what the problem(s) are? Or
alternative methods of going about it?

I suspect it relates to my defiencies in understanding exactly
how to
use VB to interact with the Access objects.