Having a hard time with 2003

livingNcolorado

Registered User.
Local time
Today, 09:36
Joined
Aug 10, 2004
Messages
10
Brand New to databases, I purchased Access 2003 and a Microsoft Book. Im doing okay with creating tables, then forms, but I cant seem to figure out how to link them together. Im trying to use a "serial number" in both a table I created with owner information and also a "serial number" in a work order table to link the two together..

Thanks for any help.

-Tracy
 
Tracy,

Since you have tables down, go to the forms tab on the database window
and create a new form with the wizard. It is a fully functional form, not
what most of us would choose, but it is functional.

Then experiment with the form in design view. Right-click on various items
and look at their properties.

Experiment and ask questions!

Wayne
 
You haven't really asked a question that we can answer. Maybe you should examine one of the sample databases that comes with Access. Or, create one of the template databases. You should be able to view the relationships window and see relationships between tables.

Keep in mind that the data type needs to be the same in both tables. So the SerialNumber can be either number or text but it must be the same in both places.
 
Thanks Wayne

Yes, I have created both tables and forms. Using Design, the wizard, and entering data. Have also experimented with the sample database of "Northwind",

I own a small construction company that receives work orders for home repairs. So, for some ungodly reason I decided to make life simple and create a database that would allow me to pull up work orders. Some homes get serviced multiple times.

I started with a table and form design that holds all the customer information. Name, address, serial number etc... Each home has a unique identy and work orders are issued and tracked by serial number. "Manufactured Homes"

I also created a table and form called "work orders", so each time I am sent another work order I can enter the data.
The goal is to simply see any multiple work orders issued, but I cant for the life of me figure out how "clicking a tab, or button" will show me all the work orders issued to me on the same home.

Thats my story in a nutshell!... And I think my answer is somewhere in "Primary Keys".. Correct??

Thanks for all the help everyone!

-Tracy
 
You need a table for homes and a table for workorders. In the workorder table, you need the HomeID so you can link the workorder to a particular home. In terms of Northwinds, this is like having the CustomerNumber in each Order.
 
livingNcolorado said:
Thats my story in a nutshell!... And I think my answer is somewhere in "Primary Keys".. Correct??

Thats right...

1) Create a home table with all the info you want to store about the home.

2)Make sure in this table you have a field called HomeID (autonumber is probably best for what you want here). Set that field to be the "Primary Key" (unique identifier) by right clicking the grey square to the left of the HomeID field and choosing primary key.

3) Create a work order table (with WorkOrderID as a primary key- again an autonumber) and create fields for all the work order details there.

4) in a new field in the work order table choose "lookup wizard" from the data type drop down box. This will start the lookup wizard. Choose "Look Up data from table or query" then point the wizard to the HomeID field in the Home table.

5) Create forms for both tables using the wizard.

6) Open the Home form in design view.

7) create space at the bottom of the form and then click on the "subform/Subreport" tool and add a subform. The wizard should start.

8) Choose "Use an existing form" and point it to your Work Order form.

9) Choose "Link forms with HomeID" (access should automatically pick this up)

10)Finish the wizard and you should have the basis of what you want.

The subform will only show Work Orders that relate to the Home displayed in the main form.

HTH,

Tom
 
Thank you all!!

Thanks all for the help. I have designed both forms and they work GREAT!

In my business, I receive work orders from the factory with 1-50 items that have to be addressed and repaired based on the customer calling the factory and giving them "punch item" problems. This is what I am tracking. Is there a way to only enter each item ( one work order with 2 items, another with 17 items) without having to create a table with 100 items to be safe? I noticed that building a form with 25 "items", it prints all 25 of them even if you only use 5.

Thanks
 
You need to create an "Items" table and link it to the "Orders" table using the "ItemID" - same process as I described before.
If you create a form based on the Items table. You can link it as a sub form to the Orders form.
Look up "Continuous Forms" in the help (or on this site) sounds like you'll want one of them (basically displays the records in a list type format).
Structuring it that way, if you have 5 Items the "Items" form will display 5, and if you have 50 it will display 50.

Let me know if you have any problems,

Tom
 

Users who are viewing this thread

Back
Top Bottom