Web Development

Ask something
Discussion Locked

Problem in designing a database

0 helpful
8 replies

by d_irvine

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am new to Access and I am trying to develop a database that will track job specifications for custom sign orders. One table will have the customer information, no problem there. The issue is when I want to link the other tables by a catagory (typeof sign) some how. For example: there are four basic sign types. Engraved sign, name badge sign, sublimation sign, and metal sign. Each specific sign type (table) has specific data that only relates to that kind of sign. How can I link the customer table to the many different sign catagories when each one is a seperate table? The customer could have many different types of signs, even some from each catagory. Is it possible to have a form, that I call up the customer and based on the type of sign catagory I select, only the specifications for that type of sign show up on the form? Any help would be appreciated.
Regards
D. Irvine

8 replies

by rolie

Read more on relational database.

Basically, its a relation between table in the database. If you have a Customer table chose a field that would connect to the Sign table.

Example

Customer Table
Name Sign
Person1 2
Person2 1
Person3 4
Person4 2

Sign Table
Sign Type
1 Engraved
2 Name Badge
3 Sublimation
4 Metal

The Customer table would relate to the Sign table through the SIGN field. In Access, their is a tool that you can chose which field to use.

by bob sellman

You indicate that you have a customer table and a separate table for each type of sign. In the table for each type of sign you have specific details about the customer's order(s) for signs of that type. Obviously, each record in each sign's table will contain the customer ID (and an order number?).

Build a form with tabs. Put the customer information on the main tab (or in the heading area).

Build subforms for each of the other tabs. Each of the other tabs will be a specific type of sign and the subform for that tab will display the records from the table for that sign. Link each subform to the main form's table (the customer table) by the customer ID#.

To look at a customer's orders for each type of sign, just click on the appropriate tab.

by bob sellman

You might want to go further than I mention by adding a table that is basically for orders. It would contain the customer ID, and order number, and "header" info (date of order, date paid, etc.). Then the sign tables could contain the order number. Think of this like an expanding purchase order, where the first table is the header info for the purchase order and the detail rows (what was ordered) are from the signs tables. You could still use the tabbed form with a tab for each type of sign,but the "header" info from the orders table could be displayed under the first tab (as part of the form, not as a subform).

If you add this table then you could track pricing and payment data and separate orders for the same customer. And a customer could order multiple types of signs on one order.

If you then go to Tools, Relationships, you can define the relationships among the different tables based on the customer ID and order # so that a customer number (and order number) must be valid and the if a customer is deleted, all orders (including the signs) related to that customer are deleted, and if just an order is deleted, the sign(s) related to that order are automatically deleted (cascade delete).

by khall3

You need to create relationships based on the information from your tables. Basically go to Tools and then click on relationships. You can drag over the fields that are related and create relationships between them. Next, you will want to create a queary based on the tables. Simply select the fields from the different tables that you want to query. Then, from your query you can create a form. In the form, make sure there are combo boxes or drop down boxes to select the customer and the sign category. Afterwards, the other information will be read in based on your selection.

This discussion is closed to new replies.