Using Microsoft Access, we always need to know how to relate data together from one table to another. For example, you want to put sales data into the customer table. On the other hand, you want to relate the customer data first to the products table before relating it to the sales table.
To do this, we need to set up relationships between tables in Microsoft Access. What do I mean by setting up relationships?
- A relationship is a set of rules or rules that relate data in one table to data in another table. To create a relationship between tables, we must first make sure that the tables are related somehow; what do I mean by relating to?
- One thing to remember is that you can either create a single row in an existing table or create a new table and relate to the existing table by adding a row.
- The relating tables must be related so that the data from one table can be accessed directly in the other table.
- To relate two tables together, you must create a one-to-many relationship. For example, we want to relate the Customers table to the Sales table.
- The Sales table will allow you to enter a unique discount number for each customer.
Create the one to many relationships:
- Open the Sales table2) Ctrl + F3) Expand the Data group4) Expand the External tab5) Highlight the Data menu6) Click on the brink 7) The external tab appears to the right of the Close button
The external tab will allow you to relate the tables using specifying the relationship between the tables. To set up the relationship, you have to insert a row in the table referring to the field you wish to relate to.
In the following example, a new relationship is created between the Customers table and the Sales table.
- individuals Customer ID
- First Name
- Last Name
- Zip Code
act out the two borne games from each record in the Customer table select the entire database to remove the individuals from the tabletop the individuals to a new table Remove the Internationality and Numbering from the Customer table select the individuals again from the new tabletop the individuals to the new table Repeat steps 2 to 6 talked of in the previous paragraph drop the individuals to the new table Rearrange individuals into lower orders drop the individuals into the main orders table Repeat steps 2 to 6 again drop the individuals into the orders table gorithmatically Drop the Order table alphabetically and rebuild Order Record to reflect this change
The customer table now contains a unique identifier for each customer Insight order record we have created with the Order Manager.
- To utilize this information, we must display it within our Access forms.
- Making a form based on the customer records:-
- The benefit of using a form over a table is that:-
- To print your form, you need to:-
- So, now you see how to relate tables together using a form over a table.
- Order particulars in Access form to order detail in Microsoft word depending on which reporting tool you are using.
- Microsoft Access forms are usually controlled by the user and not the database engine.
- You can modify your form to allow users to write comments about their records and attach them to the record. This is quickly done by designating a macro.
- The macro allows you to set the HTML must be in quotes to true to display a form.
If you intend to store many sales figures, it is best to store them as a separate table. Excel will create separate ranges for each company when the form is run. Then it is easy to separate these apart when Reporting to the SQL Server.
You can link a macro to a table to call it from your report. In the case of the macro linked to the customer table, we would use the following code:
- Dim my array As Range
- Dim my LinkedList As Dynamic Range
- Dim release As Account
- Dim rec As AccountCDisker
- Dim sub As Spreadsheet objects
- Dim my_data As Variant, DB As Database
- Dim x As Integer, y As Integer
- misql = “select*from Account”, my_data
- msql = “del account_work into accounts”, my_links
- dentist1 = CurrentDb()
- set rec = CurrentDb.OpenRecordset(rec, dbOpenDynaset)
- cnt = rec.RecordCount
- x = 0
- For x = 1 To rec.RecordCount – 1
- For y = 1 To cnt
- For y_id = acFormizer(y_id).TargetLowerCase(‘.AutoCorrectMX’)
- If Not IsEmpty(rec(1)) Then
- dta(y_id).Duplicate(cnt(1))) = cnt(1)