Tabs and Relationships

[martamor]

Hi.

Maybe what I'm trying is impossible, but Access should be able to do it because the idea is pretty simple:

I have several clients, so there's a table called "Clients" which contains data for each one.

And I have a lot of orders, so there's a table called "Orders" which contains data for every order.

Each client has some orders.

What I want is a form where the customer's data appears at the top and below, in a number of tabs, the orders assigned to the client.

A sketch attached.

So there are as many tabs as orders from the same client, and the tab shows data for one order.

Does anybody know how to do something like this?

 

[MStef]

Look at "DemoClientOrdersA2002.mdb" (attachment, zip).
Look at Tables, Forms, Relationships. Look at LINK CHILD
FIELDS and LINK MASTER FIELDS properties.
Open MainFormClient and try.
Try to enter a new Clinet record, and a new Order Record.

 

[martamor]

Hi.

I already had tried to make it with a subform, but it couldn't do it for me.

The problem here is that each order has a lot of data assigned to it, so a list of orders showing a few fields won't do it. A simple list of orders in a subform isn't enough.

Maybe an alternative to the tabs would be a third subform linked to the second subform.

As a concept, please see attached image.

I don't know if that's possible, and even if it is, I don't have the required Access skills to do it.

 

[MStef]

I don't know your task, I can't to say anything.
Maybe you need mor tables, (and more subforms).
Learn something about DATABASE NORMALIZATION.

 

[martamor]

No, I don't think database normalization will help.

The kind of task I do doesn't matter. I mean, it's pretty simple but I just don't know how to link the third subform to the second subform.

What I want to do is this: when clicking one of the orders in the subform, another subform shows the data for that order (or, when the order is clicked, a new form opens in a new window - maybe that's easier) as shown in the previous sketch.

 

[khalakmu]

Hi martamor,
I am also nubee learning Access 2007 Database. But reading from books
and different sites.
Data normalization will definitely help . Concept of Database specially
relational is to have normalized data do there are no duplicate data entries.

Anyway If you want your Database has three tables..
Clients (ClientID pk auto, other fields in Client table)
Orders (OrderId pk Auto, other fields in Order table, fkClientID)
Clients 1:M Orders
fkClientID is foreign key
Items (ItemId pk auto, other fields in Item table, fkOrderID)
fkOrderID is foreign key Orders
again Orders 1:M Items
you have to create these relationships in relationship window.
create form based on table Clients
other two forms each based on Orders and Items respectively
which you will use as subform on Tabs
In design mode of your Main Form which is based on Table clients
create two tabs..
than focus on one tab and than drag and drop Order Form (you can call subform )
again choose other table and than drop your Item Form (you can call it subform )
Now create two text field on Main form e.g textOrders, textItems
in textOrder relate this textOrders with Form Item
as Master and child link
you will be able to see in this Text field related IDs of Order form and Item form.

But any way if you use transition table to resolve many to many relations
it would be easy to use queries for different tasks.
my two cents

Khalak

 

[gemma-the-husky]

an array of tabs isnt the best idea, even if you could do it easily. what if you have thousands of orders?

you do need a form/subform

what i tend to do, is have a continuous sub-form to show the importnat details, and an expand button to expand the current item on the sub form, and show all the data.

you can only see one order at a time anyway!