Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Access 2010 Search


(!)

Butterfly807's Avatar
Butterfly807 Butterfly807 is offline
Member with 70 posts.
THREAD STARTER
 
Join Date: Jun 2012
15-Jun-2012, 01:59 PM #1
Solved: Access 2010 Search
Hello. I am currently trying to create a Vendor Database. I have created 2 tables:
  • Product/Service Directory - lists various products/services by supplier
  • Vendor Contact by Plant - lists all suppliers and our locations that they provide to
I need to be able to set up a form for users to perform one of the following functions:
1. Select a Supplier: A list of products/services provided by that supplier is populated along with my companies' locations that the supplier provides to
2. Select a Product/Service: A list of suppliers that provide that product/service is populated along with my companies' locations that the supplier provides to

Is this at all possible in Access 2010?
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,524 posts.
 
Join Date: Mar 2005
Location: UK
15-Jun-2012, 03:06 PM #2
Butterfly807, welcome to the forum.
Yes Access does that very well indeed, however I am not sure that your table structure is at it's best.
Can a vendor supply more than one location and can a Vendor supply more than on product.
If yes then you may need a Table that links the items together called a many to many table.

To do what you want a Mainform with a subform would be ideal.
__________________
OBP
I do not give up easily
Butterfly807's Avatar
Butterfly807 Butterfly807 is offline
Member with 70 posts.
THREAD STARTER
 
Join Date: Jun 2012
15-Jun-2012, 03:33 PM #3
OBP,

Yes to both of your questions. Many of our vendor supply multiple locations and provide multiple products or services.

What functions would I need to use in the mainform and subform? I've only taken and Introductory Access class, so I'm by no means an expert on all the functions of Access. But I'm hoping that with some help I can figure it out. Would I have to use Combo or List boxes? And how would using those generate an answer to a selection (ex. select supplier - get an answer of products)

Thanks!
Butterfly807's Avatar
Butterfly807 Butterfly807 is offline
Member with 70 posts.
THREAD STARTER
 
Join Date: Jun 2012
15-Jun-2012, 04:09 PM #4
I'm also not sure how to make a primary key for one of my tables so that I can achieve a one-to-many relationship with my other table. For example, the table lists each supplier (column 1) and the location they sell to (column 2). Both columns are going to have duplicate entries because each supplier will be listed multiple times (across multiple different locations) and each location will be entered multple times (across multiple suppliers). How would I achieve designating a primary key?
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,524 posts.
 
Join Date: Mar 2005
Location: UK
16-Jun-2012, 06:01 AM #5
OK, the set up you need is as follows
A supplier Table with a SupplierID Key field set to Autonumber, plus the other fields you want like name, address etc.
A Locations Table with a LocationID Key field set to Autonumber, plus the other fields you want like address etc.
A Product/Service Table ProductID Key field set to Autonumber, plus the other fields you want like Description etc.
Now you need a table called something like SupplierLocations and it should have a SupplierID field (not key) set to Number type long and a LocationID also (not key) set to Number type long.
This allows you to select as many suppliers and Locations as you like.
You can add a ProductID field of the same type to it add products which now allows as many Supplier/Location/Product combinations as you want.
Or uoi could have a Supplier/Product table of the same design to show Product/Services for suppliers.
Butterfly807's Avatar
Butterfly807 Butterfly807 is offline
Member with 70 posts.
THREAD STARTER
 
Join Date: Jun 2012
18-Jun-2012, 03:35 PM #6
Talking Thanks!
I am really excited to get this all in and test it out! Thanks so much for the help! I'll let you know how it goes!
Butterfly807's Avatar
Butterfly807 Butterfly807 is offline
Member with 70 posts.
THREAD STARTER
 
Join Date: Jun 2012
22-Jun-2012, 10:06 AM #7
I've been able to get my tables set up. I've been playing around with the forms to try to get them to do what I want, but am having difficulty.

What I want to be able to do is create 2 separate forms. In one form, I want users to be able to type a supplier name, or select from a drop down list. Once that supplier is selected, I'd like the form to produce a list of the products and services provided by that supplier, and our locations that the supplier services.

For the second form, I would like the opposite of the first. For users to type a product or service they need (or select from a drop down menu) and the form will product a list of suppliers that provide that product or service, as well as our location that the supplier services.

I am not sure if I can have each of the form provide the service location or not. Perhaps I may have to set that up as a third form? I'm not sure...

Any guidance is greatly appreciated! I've been playing around with the forms for a while and can't seem to figure out how to get the results I'd like to see.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,524 posts.
 
Join Date: Mar 2005
Location: UK
22-Jun-2012, 10:17 AM #8
You should ideally use 4 forms 2 mainforms and 2 subforms.
Mainform 1 would be to select the Supplier and based on the suppier table with a Find Combo and the Supplier ID.
It would have a subtable of product/services based on the Linking table and the Service/Products table.
The 2 forms would use a Master Child link of ServiceProductID

The other pair of tables would be reversed, the maintable would have the Product/service combo & ID and the subform would have the Linking table & Customer. The 2 forms would use a Master Child link of CustomerID
To combine the Linking table and Product Services for the subform you create a Query with Linking table and Products table in it and the other subform you use a query with the linking table and Customer table.
Butterfly807's Avatar
Butterfly807 Butterfly807 is offline
Member with 70 posts.
THREAD STARTER
 
Join Date: Jun 2012
22-Jun-2012, 12:04 PM #9
I'm having an issue where the subform is pulling each product/service multiple times. I have each product/service listed multiple times in my VendorProductService table (combining the Vendor Table and the Product Service Table), but each duplicate it assigned to a different supplier. What have I done wrong, that causes each product/service to appear multiple time in my subform when I've selected a supplier (and it should only appear once)?

I think I need more Access Training. This is a challanging database to figure out!
Butterfly807's Avatar
Butterfly807 Butterfly807 is offline
Member with 70 posts.
THREAD STARTER
 
Join Date: Jun 2012
22-Jun-2012, 12:12 PM #10
I don't know what I did.... but it's working now. Please disregard my last post.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,524 posts.
 
Join Date: Mar 2005
Location: UK
22-Jun-2012, 12:14 PM #11
Excellent, it would be nice if you could figure out what you did, maybe Master/Child link?
Butterfly807's Avatar
Butterfly807 Butterfly807 is offline
Member with 70 posts.
THREAD STARTER
 
Join Date: Jun 2012
27-Jun-2012, 01:38 PM #12
Issue with Form
I'm having an issue getting the Form to act the way I want it to. I want to use a drop down box so that the user may select a supplier, and then a list of products will appear (and vice versa on a second form).

However, each time I create the form, it creates 300 separate records. When I add the combo box, there are still 300 separate records.

Is there a way to have one record in a form, where a user can select 'bolt' for example, and then a list will populate all suppliers that sell a 'bolt'?
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,524 posts.
 
Join Date: Mar 2005
Location: UK
27-Jun-2012, 02:51 PM #13
Do you know how to create a "Find" combo?
Or how to set the Form's Filter?
You can use both techniques to show records.
You would normally have the Supplier as the mainform and the Products as a subform and the opposite for the other requirement.
Butterfly807's Avatar
Butterfly807 Butterfly807 is offline
Member with 70 posts.
THREAD STARTER
 
Join Date: Jun 2012
27-Jun-2012, 03:00 PM #14
I do not know how to do a "Find" combo, but am challenging myself to figure it out. I'll see what I can figure out about setting the Form's Filter also.

I've only taken an Introductory course to Access. Most of what I've learned I either learned from your guidance or figured it out somehow on my own (which takes a looong time).

I'll let you know how it goes...
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,524 posts.
 
Join Date: Mar 2005
Location: UK
27-Jun-2012, 03:14 PM #15
Don't bother I will instruct you, first the Find Combo, use the Combo Wizard to add the Supplier Combo to the Mainform.
When the wizard starts do not choose the default option which is "I want the Combo to Look up the values in a table or Query".
Instead select the bottom one of the 3 which is "Find a Record on my Form based on the value I selected in my Combo box"
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑