connecting tables in a database

[maligauss]

Hello everyone,

I have a problem with my database in Access

in my database I have 4 tables
in first one I have list of all records that I'm workin in my database, so all records that exist in 2. 3. and 4. table are in first one (connected with a key) with some basic info. I also have some additional tables that are connected with jst one table
in second table I have most of records from 1. table (and they are, as in other tables connected with first table with a key)
in third table I have less records than in second with lot of diffrent data, and have some records that don't exist in 2. table
in fourth table I have even less records (from which most of them are in second table, and some of then are both in second and third table)

because of my business, I divided my database in 3 different parts, but they are all connected with first table (and first table has all records that exist in database)

my problem is:
I want to make report or form that will show show ALL records from 1. table, and all data that exist from other tables (if they exist)
when I make that report I could see only data that exist in all four tables

first table has 250 records, second 150, third 120, and fourth 40
and when I make that kind of report form I can see less than 40 records
and I want to see all info in one report/form (even if they don't exist in other tables)

I hope that you understand me, and that you can help me

thank you very much in advance

[June7]

What are the relationship types - 1-to-1, 1-to-many, many-to-many? Depending on the relationships between tables, one query might serve but don't use INNER JOIN, use LEFT or RIGHT.

Otherwise, might have to use report/subreport arrangement.

[maligauss]

relationship is one to many
is it ok?
in join tipy I tried all 3 combinations, but I couldn't get what I want

do I need to change relationtip type? if yes, how to do it?

[June7]

Data relationships are what they are. That is not something that can be changed at will.

If including tables in one query with outer joins does not produce what you expect, might have to use report/subreport arrangement.

I don't know your data so can't be specific. If you want to provide db for analysis, follow instructions at bottom of my post.

[maligauss]

Thank's for your help

Now I realised something

when I make simple report that only include 3 tables (general, second and third) I can see all records in report (all 300 records)
if in that I include a table that is only connected with one of than, I can't see all data (120 from 300 records)
if I include 4 main tables (without extra data from other tables, so all of them are connected with first one) I can only see few records (40 from 300) :S

unfortunatelly I need second and third type of report

if you cannot help me,
I will prepare database and send it to you, so you can see what I'm talking about
thank you!!

[thebigthing313]

When you tried to apply June7's solution (which should work), are you looking at "Relationships" under Database Tools?
If not and you are in fact in the Query Builder, make sure that each OUTER JOINS (Tables 1 to 2, 1 to 3, 1 to 4) are connected in the direction of table 1 to the other tables. It should read something along the lines of "Include ALL records from 'tbl1' and only those records from 'tbl2' where the joined fields are equal." And that should be done 3 times (for each outer join)

[maligauss]

Yes I changed options in ''Relationships" under Database Tools
here I connect all tables and try to change options in 'Join type' (first it was 1. solution, than I tried 2. and 3.) but honistly, I can' see difference :S

I don't know which option is correct, so I could see all data

[June7]

Regardless of what the Relationships builder shows, what are the join types in the query?

[maligauss]

here is my database, I have deleted all confidential data, and change some parts
I hope that you will understand point, even if database in not in english (i translate some parts)

here is bit explanation:
'popis svih predmeta' is main table, where I have all entitets

parcelacije_detaljno
lokacijske detaljno and
imovinsko_osnovna
are main tables from each group (part of database), and all entitets that are in those tables are in 'popis svih predmeta' also

those tables have external tables (status_parcelacije, status_lokacijske and status_imovinsko') that are connected 'with popis svih predmeta'

in my report (final report - is report type that I want to use)
I want to see all entitets that exest in 'popis svih predmeta' with data from other 3 main tables (if some data does not exist in some table I want blank space, so I can see it) with data from (status_parcelacije, status_lokacijske and status_imovinsko')

I also made some forms that connect some of tables, and some other versions of reports

I have deleted all other reports, so it will be easier to manage.
If you don't understand something please contact me,

thanks in advance!!!

[June7]

What a spiderweb of relationships! I think I see circular reference, review http://www.codeproject.com/Articles/...atabase-Design

Table status_parcelacije is not in the database, there is parcelacije_status.

SELECT popis_svih_predmeta.[ime predmeta], popis_svih_predmeta.[gradska četvrt], popis_svih_predmeta.status, *
FROM status_imovinsko RIGHT JOIN (([status-lokacijske] RIGHT JOIN ((popis_svih_predmeta LEFT JOIN (parcelacije_status RIGHT JOIN parcelacije_detaljno ON parcelacije_status.ID = parcelacije_detaljno.status_elaborata) ON popis_svih_predmeta.ID = parcelacije_detaljno.id_općenita_tabl) LEFT JOIN lokacijske_detaljno ON popis_svih_predmeta.ID = lokacijske_detaljno.id_općenita_tablica) ON [status-lokacijske].ID = lokacijske_detaljno.status_lokacijske) LEFT JOIN [imovinsko - osnovna tablica] ON popis_svih_predmeta.ID = [imovinsko - osnovna tablica].ID_osnovna_tablica) ON status_imovinsko.ID = [imovinsko - osnovna tablica].status;

The relationship in the relationships builder between [imovinsko - osnovana] and status_imovinsko is backwards, same for lokacijske_detaljno and status-lokacijske. I had to reverse them after pulling into the query builder.

Table popis_svih_predmeta has 340 records. The above query returns 343 records. That means at least one of the 3 tables has more than one record associated with the same ID from popis_svih_predmeta.

BTW, advise no spaces or special characters/punctuation (underscore is exception) in naming convention. You have hyphens and spaces where should be underscore.

[maligauss]

Thank you very much for your help!!!

I tried as you said, and copy/paste what you have written
I could see all 343 records (atferwords, I'll delete duplicates)

From that querry, I made forms and reports and that is great, but it doesn't show all data that I need
I tried to combine some other data, but it said that it is not possible

I'm not quite familiar with SQL, so I don't know how to rearrange yout statement so I can also see dafa from tables:
gradske cetvrti:ime_cetvrti
Geodetska firma-nositelj elaborata: ime firme
izvođač_idejni_projekt: ime firme
zaposlenici: ime&prezime
referent prostorno:ime_prezime
procjenitelj: procjenitelj
referent imovinsko: ime_prezime

you mentioned circular reference, I suppose that you think on 'table zaposlenici', that is connectet with 2 tables (parcelacije and lokacijske) with different values
that is so, because it is list od workers that work, so thair job overlap.


once more, thank you very much for your help