form with TAB CONTROL and 4 tables with relationship

[lamarse]

I have 4 tables:


TB1_Client
TB2_Address
TB3_Invoice
TB4_Service

The relationship among tables is:
ONE Client can have MANY addresses.
ONE address can have MANY invoice.
ONE invoice can have ONE service.

Then I want to create a form using a TAB CONTROL where in
the first TAB I see the TB1_Client fields
the 2nd TAB I see the TB2_Address fields
the 3rd TAB I see the TB3_invoice fields
the 4rd TAB I see the TB4_service fields.

And I want it working like if I have in 1st TAB the client nº 3, in the 2nd TAB I see the addresses for that client, and if I choose address nº 2 from client 3 in TAB 3 I want to see the related invoices and in TAB 4 the related services.

I don't know how to do that!
If I click the first Table, I can see the related addresses in each client, and at the same time (within each address) I can see the client's invoices for each address and the service related as well.

Please, help.

I know that doing a form for each TB and another mother form with subforms for each level I can get that, but I prefer the order of a TAB CONTROL instead.

Thank you in advanced.

Victor

[June7]

Address is associated with invoice not client?

Try a query that joins Address and Client and Service to the Invoice table with join type 'Include all records from Invoice ...'). This will allow display of related info from each table but could not add new records to Address, Client, Service tables. Still need to use comboboxes to select address, client, service into the invoice record.

[lamarse]

Hello.
Thank you for answering.

Yes, address is associated with invoice and not with client... some how. The client has 1 or more addresses, and in each addresses has invoices. I don't know how to associated it otherwise 'cause I need to put the correct address in each invoice and if the client has more than one address then I donno... (maybe is there a better way of doing it).

Let me give you more real and good information. (So far it was figurative)

MS ACCESS 2013 / WINDOWS 8 64

Of course I do need to enter new data and edit the one I already have in all the tables.

Look at this foto (this is the relationships I have):



You are telling me to create a query with all the table related in and adding all the fields, right? But, doing this I do not get shown with the full and complex structure I need.

Lets look at this next picture:



Has you can see the data structure is seen correctly, so the system works (the relationship between tables with all its data in them). If I want to delete data in there or add new one, I can, so it works.
But of course this way of seen data is not confortable, that's why I want to create a form and even more I want to create into the form a TAB CONTROL (to put in order all the data), but I do not know how to create the links among tables under TAB CONTROL 'cause as far as I can see I ONLY can do 2 conections between the master table [clientes] and its child [Cli_direc] (look at the next picture):


Thanks

[June7]

None of that alters my previous advice.

[lamarse]

Ok,

But I now remember that you said: try with a query with all 4 tables (with its full fields), but you said that I would not be able to edit the table's data.

So? If that's correct it does not help me.

Well, maybe is that I do not understand properlly how to use comboboxes or maybe there is something that I do not understand on your advice (or how to implement it properlly).

Please, some more help? Maybe an example?

Thank you

NOTE: By the way, when I create the query with all fields from all the tables, I do not get the full information in such query. Actually, it is not working at all. What could I do wrong? If I create a new client the Query does not show it. Mmhh! it seems something is not going good in here.

[June7]

Sorry, took another look at your table relationships and it does revise my understanding. These are not 'lookup' tables related to a primary data table. There is a linear relationship starting with clientes and ending with contratos. Not sure this is correct. I don't read Spanish? well enough and don't know what the tables are for, except maybe clientes (Clients).

[lamarse]

First at all, thank you for taking your time on helping me.
I appreciate it!

Tb1[Clientes] = clients
Tb2[Cli_direc] = address
Tb3[facturasB] = invoices
Tb4[contratos] = services


The main idea is:

Under Tb1[Clientes] I have many clients. Each client can have several address,
under Tb2[Cli_direc] I have the address that a client can have (one to many). At the same time, each address can have many invoices
under Tb3[facturasB] I have the invoices an address can have (one to many, or none, at least at first, but I understand at least one, otherwise u do not enter an address). At the same time, each invoice has one (just one) service,
under Tb4[contratos] I have the service related to the invoice (one invoice = one service. There is no invoice without its service).


So, as I already said, I want to have all these data under a form and using a TAB CONTROL looks to me easier to play with all the data, choosing at first TAB the client, 2nd TAB the address, 3rd TAB the invoice and 4rd TAB the service, and making all sense (of course).


Thank you once again.

Victor

[June7]

I don't think tables are properly related. Consider:

tblClients
ClientID (primary key)

tlblAddresses
AddressID (primary key)
ClientID (foreign key)

tblServices
ServiceID (primary key)

tblInvoices
InvoiceID (primary key)
AddressID (foreign key)
ServiceID (foreign key)
InvoiceDate

Now try the suggested query.

[lamarse]

By the way, I've been playing around with the query suggestion, and I've realiced that if I add all the 4 tables fields under the query it just show me the data based on the full filled last table (services). Well, not sure this is correct or not, if I want to get the data of the client with a address but with and without a services (yet), I need to make a query with Tb1 & Tb2 and then create another query adding the data of this first query plus the Tb3 and Tb4 fields.
Anyway, just to mess around and try something else.
Actually, in case I would do a form based on subform and subSubform (with the 4 levels been shown at the same time under the screen) then I can (and I know how) make this work, but the main problem on whyNOTdoingIT is 'cause there are more than 150 fields in total and it is a total mess doing it this way. If , at least, I would find the way of having a form with all the 4 forms (each with each table) and working everything, I guess I could move each form under each TAB and that would work (I've seen a example of this type of form BUT only with two table, and I have no idea on how they did it 'cause I see NO the way on how to add a thrid table on it).

In case you would like to download my DataBase, here you have it.
Unrar it, and run "Dase Datos" file. You need to make the link to SERVER file.
Then click the GREEN big BUTTON [Ficha Clientes] and there you will see the form with the TAB CONTROL (of course, not working yet).
There are many other things you will see in there. Basically you can check for relationships under SERVER and the query,forms and so on at the BASE DE DATOS.

Thanks for everything.

[June7]

What did you think of the suggested table relationships? I made a slight edit after reading your post because you said want to associate client and address when there is no service and earlier you said each client can have multiple addresses so I am assuming each address is associated with only one client.

[lamarse]

Well, that is the point I am right now.
What happends to me is that so far a client is telling me that it has more than one bussiness (lets say: a restaurant and a bar, diferents locals in diferents address).
So, I have a client ID and I find out that I need to make some invoices/services for client ID X but bussiness 1 or 2. That's why I wanted (I don't care if I have to change the way I see things, I need the dataBase work properly) client/address/invoice-service.
How do I have to do it? Well, at first I thought on making 1 client / many address / many invoices/ 1 service(for each invoice).
Should I have better 1 client (without client ID, 'cause it will repeat it) / many services/invoices? Where would I put the client ID? (that's very important here, 'cause a bussiness has an exclusive ID number for the goverment)

Well, I keep thinking it shoudl be the way I was thinking at first (but maybe I am wrong).

Now, I donno how to proceed with the suggested table relationship.

[June7]

I can't open rar files.

I am not understanding what the service table is. What are the services? Services provided to each specific address? Do you want invoice to bill for single service at single address? Or do you want multiple services for multiple addresses on same invoice? If the latter, then need another table for invoice details that will be related to Invoices table.

[lamarse]

OK.
Now it is zip file instead (and this time has the suggested structure already).


Service table is the work you do to the client with details, not the cost of the payment.

Onces again:

TblClient with the client details (there is only one client at time, at least that is what I think), such ID, company name, type of company
TblAddress with the addresses (one to many for each client), such Street name, city... even some phone numbers...
TblService with the service details, here the fields are different from invoice tbl actually.
TblInvoice with the invoice details.

As well, I would like to tell you that in your suggested relationship I would have to mane ono2one between invoice/service and it does not.
Although, I guess that first we need to solve the address thing.

Thank you

[June7]

Sorry, can't help further, the language is too much of a barrier to analyse database.

[lamarse]

Hey, don't give up. Please.

Let's do something easier for you (and equal helpfull for me).
I am going to make a clean database all in english, with all my needs (and nothing else).

I will have it in 24h more or less.

Can this be a solution?

Thank you for all your time (anyway).

Victor