Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Access07: "Filtering" Tables?


  • Please log in to reply
1 reply to this topic

#1 DnDer

DnDer

  • Members
  • 646 posts
  • OFFLINE
  •  
  • Local time:03:43 PM

Posted 15 September 2011 - 11:27 AM

In my core table, I want to create a lookup table to refer to. How complicated can I get this?

I'm trying to create a column for printers in our office. It has 4 required fields: location, name, ip, model. At each location, we have similar printer names because we have similar departments. I want to know how to make the core database table I'm using pick the right printer by narrowing down the options.

EXAMPLE

Printer Table
Site 1 - Receivables 1 - 1.2.3.4 - Fricken Laser Jet
Site 1 - Receivables 2 - 1.2.3.5 - Old Ink Jet
Site 2 - Receivables 1 - 2.3.4.5 - Fricken Laser Jet
Site 2 - Receivables 2 - 2.3.4.6 - Another Fricken Laser Jet, with sharks attached to it

Core Database Table
[open the drop-down for using a lookup to Printer Table]
[choose site] -> [name appears in drop down after picking site]
[choose name] -> [location-name-ip-model displays in Cored Database Table column called Printer Table Lookup]

We can't have someone just write "Receivables 1" in our printer column, because there are so many. But I want to make it simple for people entering it. Pick the site, pick the name, everything fills in. Can this be done with Access 07? I'm not quite sharp enough to know if I can, and I'm slowly making my way through tutorials, trying to find if it exists.

BC AdBot (Login to Remove)

 


#2 DM27

DM27

  • Members
  • 8 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Tennessee
  • Local time:04:43 PM

Posted 23 September 2011 - 09:31 PM

Hello DnDer,

What you want to accomplish can be done in Access. In general, the look up lists are not created directly in your main data table. Each field you want to create a look up for gets its own table and they are tied together via a Form. In your example it looks like you would have three separate tables in your database.

A general set up:
Main table: primary key, site field and printer field
Printer table: primary key and printer specific fields
Site table: primary key and site specific fields
Form: uses the Printer and Site table info above to populate your main table via combo boxes.
Query: used to join your tables and give your descriptive data instead of just the primary keys.

To narrow down your printer choices based on the Site, you would likely use a cascading combo box set up, but depending on the amount of options, it may not be needed.

Hope this helps and best of luck with your project.

Sincerely,

dm




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users