Searching Across Multiple Tables

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

Guest

Hi,

I have a couple of questions, I have asked these before, but I cannot find
most of the questions I posted in the program, and I have not recieved any
replies.

1) I need help with a code that will search a collumn for certain data,
provided by the user, then look up the data that corilates with it from
another collumn and use it to search another table and display its form,
this is a little hard to explain so maybe an example will help.

the user inputs Aluminum, and the program searches Table 1 for all instences
of alluminum in the metals collumn, then takes all of the data in the
Materials collumn that is in the same row as Alluminum, and searches Table 2
Materials row for the materials that were found to line up with Alluminum.
Then the program displays that form.

It will also be helpful if you know a way to make a VB code that will search
multiple fields, this will make it easier for me to implement than the way
described above sence all of the data is already entered in multiple fields.

2) I need to see why this code is not working, I want it to search a
particular collumn for all data that is within a user inputed range, for
example if the user inputs 1.5 and 2.5 for the range the program should
bring up the form for that table with all entries that are between 1.5 and
2.5.

Thanks,
James
 
James

Sounds like its a Query that joins both tables on the metals columns to me.

SELECT Materials , OtherColumm, MoreColumns, ...
FROM Table1 INNER JOIN Table2
ON Table1.metals = Table2.metals
WHERE metals ="Aluminum"

Depending what you what to do with the results of this query there might not
be any VBA involved at all.

Your second question is more of the same. Create a query that has
replaceable Parameters and execute it to get the results.

SELECT Foo
FROM Bar
WHERE ColumnToCompare BETWEEN [Enter Min Value] and [Enter Max Value]
ORDER BY Foo

If you are having a difficult time understanding this then I recommend that
you start to do some serious reading. You can start with the Access help
facility. Using queries is an essential skill in developing a database
application.


Ron W
 
I would prefer to use VB, and from what I have read about queries it realy
isn't going to do what I want.

My prefered method of this would be to search multiple fields for a
specific, user inputed, variable, my example Aluminum. And I want the program
to open all of the Forms that have alluminum in any of the searched fields,
ie alluminum can be in field one field two ... or in any combination of
listed fields.

as for the data range, I also need it to open forms directly.

I would prefer to keep things in VB unless there is no possible way to use it.

by the way I am using a form to create this search capability adding on to a
database already created.
Thanks,
James

Ron Weiner said:
James

Sounds like its a Query that joins both tables on the metals columns to me.

SELECT Materials , OtherColumm, MoreColumns, ...
FROM Table1 INNER JOIN Table2
ON Table1.metals = Table2.metals
WHERE metals ="Aluminum"

Depending what you what to do with the results of this query there might not
be any VBA involved at all.

Your second question is more of the same. Create a query that has
replaceable Parameters and execute it to get the results.

SELECT Foo
FROM Bar
WHERE ColumnToCompare BETWEEN [Enter Min Value] and [Enter Max Value]
ORDER BY Foo

If you are having a difficult time understanding this then I recommend that
you start to do some serious reading. You can start with the Access help
facility. Using queries is an essential skill in developing a database
application.


Ron W

James said:
Hi,

I have a couple of questions, I have asked these before, but I cannot find
most of the questions I posted in the program, and I have not recieved any
replies.

1) I need help with a code that will search a collumn for certain data,
provided by the user, then look up the data that corilates with it from
another collumn and use it to search another table and display its form,
this is a little hard to explain so maybe an example will help.

the user inputs Aluminum, and the program searches Table 1 for all instences
of alluminum in the metals collumn, then takes all of the data in the
Materials collumn that is in the same row as Alluminum, and searches Table 2
Materials row for the materials that were found to line up with Alluminum.
Then the program displays that form.

It will also be helpful if you know a way to make a VB code that will search
multiple fields, this will make it easier for me to implement than the way
described above sence all of the data is already entered in multiple fields.

2) I need to see why this code is not working, I want it to search a
particular collumn for all data that is within a user inputed range, for
example if the user inputs 1.5 and 2.5 for the range the program should
bring up the form for that table with all entries that are between 1.5 and
2.5.

Thanks,
James
Click to expand...
Click to expand...
 
James

"You can lead a horse to water, but you can't make him drink."

Queries are typically used as the data source for bound forms. Bound forms
(for better or worse) are the standard Access way of presenting data to the
user. Believe me that you will get a lot further faster if you do things
the Access way when developing in Access. After you have mastered doing
things the Access way you can feel free to change the standard way Access
works and make it work the way you want it to work.

"I want the program to open all the forms that have aluminum in them" makes
absolutely no sense to me. If you have columns named Field One and Field
Two that have the same kind of data in them, then I am afraid that the
database design may be flawed, and you are doomed before you start.

Sounds like you have not yet grasped the basics of database theory. Do
yourself a favor and get a beginners book or two and start reading. Then
look at (and study) the Northwind sample database that came with Access.
Perhaps after a few hours study the light will go on and you will be on your
way.

Good luck with your project.

Ron W
James said:
I would prefer to use VB, and from what I have read about queries it realy
isn't going to do what I want.

My prefered method of this would be to search multiple fields for a
specific, user inputed, variable, my example Aluminum. And I want the program
to open all of the Forms that have alluminum in any of the searched fields,
ie alluminum can be in field one field two ... or in any combination of
listed fields.

as for the data range, I also need it to open forms directly.

I would prefer to keep things in VB unless there is no possible way to use it.

by the way I am using a form to create this search capability adding on to a
database already created.
Thanks,
James

Ron Weiner said:
James

Sounds like its a Query that joins both tables on the metals columns to me.

SELECT Materials , OtherColumm, MoreColumns, ...
FROM Table1 INNER JOIN Table2
ON Table1.metals = Table2.metals
WHERE metals ="Aluminum"

Depending what you what to do with the results of this query there might not
be any VBA involved at all.

Your second question is more of the same. Create a query that has
replaceable Parameters and execute it to get the results.

SELECT Foo
FROM Bar
WHERE ColumnToCompare BETWEEN [Enter Min Value] and [Enter Max Value]
ORDER BY Foo

If you are having a difficult time understanding this then I recommend that
you start to do some serious reading. You can start with the Access help
facility. Using queries is an essential skill in developing a database
application.


Ron W

James said:
Hi,

I have a couple of questions, I have asked these before, but I cannot find
most of the questions I posted in the program, and I have not recieved any
replies.

1) I need help with a code that will search a collumn for certain data,
provided by the user, then look up the data that corilates with it from
another collumn and use it to search another table and display its form,
this is a little hard to explain so maybe an example will help.

the user inputs Aluminum, and the program searches Table 1 for all instences
of alluminum in the metals collumn, then takes all of the data in the
Materials collumn that is in the same row as Alluminum, and searches
Click to expand...
Table
2
Materials row for the materials that were found to line up with Alluminum.
Then the program displays that form.

It will also be helpful if you know a way to make a VB code that will search
multiple fields, this will make it easier for me to implement than the way
described above sence all of the data is already entered in multiple fields.

2) I need to see why this code is not working, I want it to search a
particular collumn for all data that is within a user inputed range, for
example if the user inputs 1.5 and 2.5 for the range the program should
bring up the form for that table with all entries that are between 1.5 and
2.5.

Thanks,
James
Click to expand...
Click to expand...
Click to expand...
 
Ron,

Alright, will do, Thanks for your help and time

James

Ron Weiner said:
James

"You can lead a horse to water, but you can't make him drink."

Queries are typically used as the data source for bound forms. Bound forms
(for better or worse) are the standard Access way of presenting data to the
user. Believe me that you will get a lot further faster if you do things
the Access way when developing in Access. After you have mastered doing
things the Access way you can feel free to change the standard way Access
works and make it work the way you want it to work.

"I want the program to open all the forms that have aluminum in them" makes
absolutely no sense to me. If you have columns named Field One and Field
Two that have the same kind of data in them, then I am afraid that the
database design may be flawed, and you are doomed before you start.

Sounds like you have not yet grasped the basics of database theory. Do
yourself a favor and get a beginners book or two and start reading. Then
look at (and study) the Northwind sample database that came with Access.
Perhaps after a few hours study the light will go on and you will be on your
way.

Good luck with your project.

Ron W
James said:
I would prefer to use VB, and from what I have read about queries it realy
isn't going to do what I want.

My prefered method of this would be to search multiple fields for a
specific, user inputed, variable, my example Aluminum. And I want the program
to open all of the Forms that have alluminum in any of the searched fields,
ie alluminum can be in field one field two ... or in any combination of
listed fields.

as for the data range, I also need it to open forms directly.

I would prefer to keep things in VB unless there is no possible way to use it.

by the way I am using a form to create this search capability adding on to a
database already created.
Thanks,
James

Ron Weiner said:
James

Sounds like its a Query that joins both tables on the metals columns to me.

SELECT Materials , OtherColumm, MoreColumns, ...
FROM Table1 INNER JOIN Table2
ON Table1.metals = Table2.metals
WHERE metals ="Aluminum"

Depending what you what to do with the results of this query there might not
be any VBA involved at all.

Your second question is more of the same. Create a query that has
replaceable Parameters and execute it to get the results.

SELECT Foo
FROM Bar
WHERE ColumnToCompare BETWEEN [Enter Min Value] and [Enter Max Value]
ORDER BY Foo

If you are having a difficult time understanding this then I recommend that
you start to do some serious reading. You can start with the Access help
facility. Using queries is an essential skill in developing a database
application.


Ron W

Hi,

I have a couple of questions, I have asked these before, but I cannot find
most of the questions I posted in the program, and I have not recieved any
replies.

1) I need help with a code that will search a collumn for certain data,
provided by the user, then look up the data that corilates with it from
another collumn and use it to search another table and display its form,
this is a little hard to explain so maybe an example will help.

the user inputs Aluminum, and the program searches Table 1 for all
instences
of alluminum in the metals collumn, then takes all of the data in the
Materials collumn that is in the same row as Alluminum, and searches Table
2
Materials row for the materials that were found to line up with Alluminum.
Then the program displays that form.

It will also be helpful if you know a way to make a VB code that will
search
multiple fields, this will make it easier for me to implement than the way
described above sence all of the data is already entered in multiple
fields.

2) I need to see why this code is not working, I want it to search a
particular collumn for all data that is within a user inputed range, for
example if the user inputs 1.5 and 2.5 for the range the program should
bring up the form for that table with all entries that are between 1.5 and
2.5.

Thanks,
James
Click to expand...
Click to expand...
Click to expand...
 
"You can lead a horse to water, but you can't make him drink."

as any Linda Ronstadt fan knows... <g>
 
Back
Top