Creating a Query Program

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to select a group of records with the same text attribute within a field without having to specify that field in the query? Now I know you will probably ask, "then how would Access know what to look for?" But I already know that you can sort records based on a common attribute within a field. All I want to do is go one step further and separate those records based on their attributes so that they are not in the same database

If you have 500 different attributes for a given field it would be laborious and time-consuming to have to manually input each one into the query, run the query, save the query, and then do it ALL over again until all, say, 2000 records had been partitioned to their proper group.

This is my solution: sort records based on a common attribute in the field over and over again, having the computer save the results from the query and then repeat the process untill all records had been extracted based on an attribute. Is there a way to automate this task in Microsoft Access?
 
Assuming that you are only searching in one table paste
the following into a module. (of course tweak for your
stuff) let me know if this works. (See notes)

mweyland at mnqio dot sdps dot org




Private Sub buildResultsTable()
Dim db As Database
Dim tbl As TableDef
Dim strTbl As String
Dim x As Integer

'what this does:
'makes a copy of the table you are looking in
'deletes all values from table so you only have a shell
'appends all matching instances of selection string as
defined by the form
'selects unique from this temp table and makes a table
call results
'you can use the results table to feed your form.
'there may be a little extra overhead here, to prevent
bloat you could use try it recordsets
'i don't now how one would append a recorset to another
recordset, maybe clone????'
'i know that this works.



'remove results table from db
DoCmd.DeleteObject acTable, "results"


'strTbl = "enter your table name here"

Set db = CurrentDb
Set tbl = db.TableDefs(strTbl)

db.Execute ("SELECT * INTO " & strTbl & "temp FROM " &
strTbl)

db.Execute ("DELETE * FROM " & strTbl & "temp")

Dim yourstringhere
yourstringhere = "field from box on form"

For x = 0 To tbl.Fields.Count - 1
db.Execute ("INSERT INTO " & strTbl & "temp SELECT *
FROM " & strTbl & " WHERE " & tbl.Fields(x).Name & "= " &
addQuotes(yourstringhere))

Next x


db.Execute ("SELECT DISTINCTROW * INTO results FROM " &
strTbl & "temp")
DoCmd.DeleteObject acTable, strTbl & "temp"

End Sub

-----Original Message-----

Is there a way to select a group of records with the same
Click to expand...
text attribute within a field without having to specify
that field in the query? Now I know you will probably
ask, "then how would Access know what to look for?" But I
already know that you can sort records based on a common
attribute within a field. All I want to do is go one step
further and separate those records based on their
attributes so that they are not in the same database.
If you have 500 different attributes for a given field
Click to expand...
it would be laborious and time-consuming to have to
manually input each one into the query, run the query,
save the query, and then do it ALL over again until all,
say, 2000 records had been partitioned to their proper
group.
This is my solution: sort records based on a common
Click to expand...
attribute in the field over and over again, having the
computer save the results from the query and then repeat
the process untill all records had been extracted based on
an attribute. Is there a way to automate this task in
Microsoft Access?
 
Back
Top