473,326 Members | 2,126 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

How to filter data in an Access table?

Basically, I want to create a database that holds information for a business. I want the database to have the Product Types (Drinks, Cakes, etc.) and Product Names (Coke, Pepsi; Chocolate, Vanilla, etc.). I have made the tables already but I'm not sure if they're right.

My goal is to make a form with combo boxes and have them filter the information stated above. So, let me explain.

When the user clicks on the combo box for Product Type, Cakes, Drinks, and other product types display. THEN, in the Product Name combo box, I want that to ONLY display information that is related to Product Types. So I don't want any drinks like pepsi and coke to be displayed when I'm looking for cupcakes.

I work pretty well with databases, but this is a little advanced for me.

Any help would be greatly appreciated!!!!
Jan 22 '11 #1
3 2001
beacon
579 512MB
Hi Marty,

Welcome to Bytes!

What you're talking about is called cascading combo boxes. Click HERE for a tutorial on how to set them up.

If you've got two tables, Product Types and Product Names, you just need to make sure that there's some way to identify the Product Type from the Product Name table. So, if you've got a column in your Product Names table for the name of the product (I know, duh!), and it says "Coke", you'll need another column for the Product Type, which I would name ProductTypeFK (the FK stands for foreign key in case that's a foreign term, excuse the pun).

So, what the heck is a foreign key? It's a reference value that you include in one table in order to create a relationship, or link with another table's primary key. A primary key is a unique identifier for your table.

In your Product Types table, you might have ProductTypeID as your primary key, then have all of the other fields as regular attributes. Your primary key, if you do as I've suggested, would be an autonumber field.

In your Product Names table, as I mentioned before, you'll want to add a foreign key. The foreign key, if you do as I've suggested, will be a regular number field.

Then, you'll want to go into the Datasheet mode for your Product Names table and enter the corresponding number from the Product Types table in your ProductTypesFK field.

The last step is to create the relationship between the tables. Go to Tools -> Relationships, add both tables, then click and drag the ProductTypeID field from your Product Types table to the ProductTypeIDFK field on your Product Names table. After you do this you'll get a pop up...put a check mark in the box next to "Enforce Referential Integrity" (you can look this up online if you want to find out more about it).

Once you've gotten this far you can use the link I supplied and get to work on your form.

Good luck,
beacon
Jan 22 '11 #2
Beacon,

Thanks for the quick reply!

I did what you said and I have made the recommended adjustments, as well as the relationship.

I tried the code and it works perfectly! Thank you very much for your help!

By the way, is there any free e-book where I can learn Access coding?

Thanks again!
Jan 23 '11 #3
beacon
579 512MB
Glad it worked.

There are all kinds of sites that are devoted to Visual Basic for Applications, or VBA, which is the language used in Microsoft Access.

The link I sent you in my previous post is a good site, this site has a lot of great articles in the Insights section, and a whole slew of others. I don't want to post any specific sites because I don't want to detract from all the good work that everyone at Bytes does on a daily basis. It's by far the most helpful site I've ever been involved with.

Just do some searches on VBA and you'll find plenty of stuff to get you going.

beacon
Jan 23 '11 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: jonezy | last post by:
i didnt think my sql qeury was that complicated that it would crash my web app. all im trying to do is filter data between two tables. heres my query <cfquery name="GetResults"...
2
by: Fred | last post by:
Hi. How do I import while mapping an excel table to an access table please??? I've searched around and all I can find is a software product or code that does the same thing as the access...
2
by: SenseForAll | last post by:
First please note I am a novice at VBA and not even that experienced with DAO/ADO and MS-SQL. Any assistance is appreciated. That said... I have an application written in Access w/ VBA. I need to...
0
by: Manystrengths | last post by:
I must warn all of you willing to help me that this is my first go at designing a functional data access page. Please bear in mind that we all start somewhere so if I should be asking the absolute...
5
by: Kevin C | last post by:
I was curious to know what some developers out in the industry are doing when it comes to exposing Data access logic, specifically persistence. This is assuming that your not using an O/R framework...
12
by: VMI | last post by:
For some reason, the process of retrieving data (about 20 records) from an Access table that has 400K records to a dataTable is taking over 3 mins. to complete. Below is my code to connect to the...
2
by: keliie | last post by:
Hello, I've created a subform that displays the results of a query. The results of the query contain a unique ID named "Invoice_ID". I want to be able to double click on a specific row in the...
1
by: Mindspring | last post by:
I need some help with filtering. I not very savvy (yet) with c# so I am having a hard time figuring out how to filter results. Here is what I am trying to do. I am pulling data from a database...
5
by: Mapi | last post by:
I need help. I am new to MS Access and have been assign this project. I want to be able to filter data from a form that will be displayed on a subform. I don't know if this is possible but...
1
by: munkee | last post by:
Hi all, Basically back on the data access pages again. I have a hyperlink on my main page to open up a second page which has a server filter linked. What I am basically asking to do is how...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.