MS Access03 design/form/query question

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

UHSRich

Thread Starter
Joined
Jan 11, 2006
Messages
1
I'm in the process of creating a db and appear to have thought I knew more than I really do. The db currently has one table:

Table1.DptKey
Table1.DptCode
Table1.Employee
Table1.Title
Table1.Location

I need a second table (yet to be correctly designed!) which will contain equipment info:

Table2.InvKey
Table2.????? - this should match Table1.DptKey
Table2.Make
Table2.Model
Table2.SerioalNo
Table2.Tracking
Table2.MACAdd
Table2.Notes

How I foresaw this working for me was to have a form where the enduser could enter in the equipment information, the employee info is already entered. I tried getting fancy and creating a query to allow for a drop down box that only showed all employees in Table1 that were NOT already in Table2. Within this form the I want(ed) to show the employee name and location, which are informational and not part of Table2, as this will be more intuitive to the end user than the Table1/Table2.DptKey.

I've tried to get this working a few different ways and can't seem to get it to function correctly. I don't know if it's because want I want to do can't be done or if it can and I'm doing some piece of it incorrectly? And I've now tried so many different things I don't know what I'm doing anymore.

My SQL is stronger than my VBA and I've been trying to solve this via queries/forms/web resources/chicken bones etc.

Can anyone tell me if what I want to do is doable or not? if so, how? I know that I want to stay away from bad design like lookups, but they were the only thing that's almost worked for me. I'm early enough into this project that if my initial design is flawed I'm willing to make changes. And I just want to make this as easy on the end user as possible.

Thanx,
 
Joined
Aug 5, 2005
Messages
3,086
I didn't read your entire post yet (welcome to the site (y) ), but I wanted to mention that the foreign key in your second table should have the same name as the primary key in the first table. In other words,

Table2.?????

Should be

Table2.DptKey​

You'd then join the two tables in a 1-to-many relationship on that field by opening the Relationships window for the database, displaying both tables, dragging the foreign key from the second table onto the primary key of the first, and clicking Enforce Referential Integrity in the Edit Relationships window.

I'm going to say, never put spaces in any object names (not that you have). You should adopt a naming convention. Here's a pretty common convention.

Also check out The Ten Commandments of Access.

Some good resources:

The Access Web
AccessVBA (in particular, check out the FAQ forum)​

I feel like I ran right over that. If you have more questions, post back please.

HTH

chris.
 

OBP

Joined
Mar 8, 2005
Messages
19,896
UHSrich, what you want to do is doable, take on board what Chris says about table relationships as it is good practice. Can you post a zipped copy of the DB on here and I or Chris will sort it for you.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

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.

Join over 807,865 other people just like you!

Latest posts

Members online

No members online now.
Top