Help with My Query

[safril]

Hi, I'm a newbie here and having trouble with a query. So I have some tables and queries. When i want to display data from tbl_Kasasi in query qry_PK, the data is not showing. is the relationship wrong? or I make some mistake?

please help ASAP

 

[arnelgp]

do not use "Inner Join". it will not show any record if there is No matching record to the
related table.

use your relationship on your first post, but on the Left table of tbl_Kasasi (that is tbl_Terdakwa),
make the relationship Arrow (edit the relationship) to point to tbl_Terdakwa.

also from tbl_terdakwa (related to those 3 small tables), make the relationship arrow (edit relationship) to point
to those 3 tables.

 

[safril]

do not use "Inner Join". it will not show any record if there is No matching record to the
related table.

use your relationship on your first post, but on the Left table of tbl_Kasasi (that is tbl_Terdakwa),
make the relationship Arrow (edit the relationship) to point to tbl_Terdakwa.

also from tbl_terdakwa (related to those 3 small tables), make the relationship arrow (edit relationship) to point
to those 3 tables.

I'm so confuse about the relationship. Can you repair my relationship design and with the query, please. I've been tried it but it show empty record when i click a button to open frm_PK in frm_Terdakwa.

 

[arnelgp]

you have Correct query there. Only you do not have id_terdakwa = 2 on tbl_Kasasi that is
the reason it is coming blank in the query.

 

[safril]

you have Correct query there. Only you do not have id_terdakwa = 2 on tbl_Kasasi that is
the reason it is coming blank in the query.

I've changed the relationship Arrow in both relationship design and qry_PK. But when I run frm_Terdakwa and click PK button to open up frm_PK, the query doesn't show any result.

or like this ?

 

[arnelgp]

see post #4, you Already has the correct query in post #1.

 

[The_Doc_Man]

I broke one of my own rules to look at your DB. I noticed that in your qry_PK, a lot of other fields are missing from the query besides the three you called out. The missing fields (in the design grid) are to the right of the fields missing from tbl_Kasasi. A lot of them are based on converting information about one or the other of your stored dates.

I agree with arnelgp regarding the direction of the relationships as a possible contributor, but something else isn't quite right either. For instance, in qry_PK, you have a "serial LEFT JOIN" - i.e. a LEFT JOIN of a LEFT JOIN. This is LEGAL in Access. You are missing fields from tbl_Kasasi in your qry_PK, which is a case that comes through a LEFT JOIN. Normally, you would say the fields are missing because having no matching fields in tbl_Kasasi. However, in the same record of qry_PK, you are NOT missing fields from tbl_PK, which you can only have if there ARE matching records in tbl_Kasasi. At the moment, I don't see what is going on, because that is an apparently self-contradictory situation.

 

[mike60smart]

Hi

The Relationships should be as shown:-

Which produces the Form View as shown.

 

[mike60smart]

You should also NOT be using Lookup Fields in your tables.

 

[MajP]

Exercices to practice relationship

i dont have no relationship inside. ok have some query with relationship but this is not what i see on other databases. That may be fine, not to have any relationships established. Having relationships established in your relationship window is not always required. It is also not the same as...

www.access-programmers.co.uk

Read @isladogs posts on relationships.

 

[Pat Hartman]

You have bad records in the tables because they are missing foreign key values. This looks like a hierarchical relationship. For that type of relationship, the FK needs to be required and RI needs to be enforced. Otherwise, you end up with orphans as you have.

Also, you have duplicated data. For example, the id_Terdakwa in tbl_Kasasi is inferred by its relationship to tbl_Banding. It should NOT be duplicated in a lower level table.

 

[safril]

Hi

The Relationships should be as shown:-

Which produces the Form View as shown.

Awesome, it works fine on the form 'frm_PK', but what about the relationship settings for 'qry_Banding', 'qry_Kasasi', and 'qry_Terdakwa'? can you show me the correct relationship?

 

[safril]

The database I want to create is simple because it only has to be connected to Microsoft Word via Mail Merge.

So I want to create a table 'tbl_Terdakwa' where later (in the form) we can choose 'Nama_PP', 'Nama_Oditur', and 'Nama_PH' from the existing table.

Each 'Nama_Terdakwa' can file an appeal (in 'tbl_Banding'), cassation (tbl_Kasasi), and judicial review (tbl_PK).
The order: tbl_Terdakwa -> tbl_Banding -> tbl_Kasasi -> tbl_PK

In short, here's the table design for my database:

tbl_Terdakwa​

id_Terdakwa	AutoNumber
Nama_Terdakwa	Short Text
Pangkat_Terdakwa	Short Text
NRP_Terdakwa	Short Text
id_PP	Number
id_Oditur	Number
id_PH	Number

tbl_PP​

id_PP	AutoNumber
Nama_PP	Short Text
Pangkat_PP	Short Text
NRP_PP	Short Text

tbl_Oditur​

id_Oditur	AutoNumber
Nama_Oditur	Short Text
Pangkat_Oditur	Short Text
NRP_Oditur	Short Text

tbl_PH​

id_PH	AutoNumber
Nama_PH	Short Text
Pangkat_PH	Short Text
NRP_PH	Short Text

tbl_Banding​

id_Banding	AutoNumber (?)
id_Terdakwa	Number (?)
tgl_Banding	Date/Time

tbl_Kasasi​

id_Kasasi	AutoNumber (?)
id_Terdakwa	Number (?)
id_Banding	Number (?)
Tgl_Kasasi	Date/Time

tbl_PK​

id_PK	AutoNumber (?)
id_Terdakwa	Number (?)
id_Banding	Number (?)
id_Kasasi	Number (?)
Tgl_PK	Date/Time

For example, in mail merge needs, I need a judicial review (from tbl_PK) query with details :

Nama_Terdakwa

Nama_PP

Nama_Oditur

Nama_PH

tgl_Banding

tgl_Kasasi

tgl_PK

I'm really newbie in Access and no one can teach me from basic. I'm really confuse about the relationship design for those tables.

 

[mike60smart]

See if this now does what you need.

 

[safril]

See if this now does what you need.

Thanks for your reply. But when I try to run frm_Terdakwa, I'm still can't select Nama_PP from tbl_PP, Nama_Oditur from tbl_Oditur, and Nama_PH from tbl_PH. What should I do?

And,, in frm_PK, it doesn't work when I try to input judicial review's data for the accused (tbl_Terdakwa)

 

[mike60smart]

Thanks for your reply. But when I try to run frm_Terdakwa, I'm still can't select Nama_PP from tbl_PP, Nama_Oditur from tbl_Oditur, and Nama_PH from tbl_PH. What should I do?

And,, in frm_PK, it doesn't work when I try to input judicial review's data for the accused (tbl_Terdakwa)

Hi
Your frm_Terdakwa was based on a query that was made up of multiple tables.
It only needed to be based on tbl_Terdakwa.

I have modified the frm_Terdakwa so that you are now able to select the values required.

See the attached.

 

[safril]

Hi
Your frm_Terdakwa was based on a query that was made up of multiple tables.
It only needed to be based on tbl_Terdakwa.

I have modified the frm_Terdakwa so that you are now able to select the values required.

See the attached.

Yes, all of the tables based on tbl_Terdakwa. In frm_Terdakwa it works fine.
But I'm still unable to record any data in frm_PK.
I try to edit the relationship design to this :

and the query design of qry_PK like this :

The frm_PK will display what should appear when I try to input some records from tbl_Terdakwa.
But nothing happened, I'm still unable to fill some records in frm_PK with error notification :
Record(s) cannot be added; no corresponding record on the 'one' side.

In my database, each record in tbl_Terdakwa can submit/propose appeal (in frm_Banding), cassation (in frm_Kasasi), and judicial review (in frm_PK). Each record in tbl_Terdakwa can input an appeal (frm_Banding). But when input a cassation (frm_Kasasi), it will required some record from tbl_Banding. As well as frm_PK, it will require some record from tbl_Banding and tbl_Kasasi.

 

[mike60smart]

Hi

Your frm_PK now allows you to enter data

 

[Pat Hartman]

It is always best to tell what you did to fix the problem rather than make the OP guess.

 

[mike60smart]

Yes, all of the tables based on tbl_Terdakwa. In frm_Terdakwa it works fine.
But I'm still unable to record any data in frm_PK.
I try to edit the relationship design to this :
View attachment 97679

and the query design of qry_PK like this :
View attachment 97680

The frm_PK will display what should appear when I try to input some records from tbl_Terdakwa.
But nothing happened, I'm still unable to fill some records in frm_PK with error notification :
Record(s) cannot be added; no corresponding record on the 'one' side.

In my database, each record in tbl_Terdakwa can submit/propose appeal (in frm_Banding), cassation (in frm_Kasasi), and judicial review (in frm_PK). Each record in tbl_Terdakwa can input an appeal (frm_Banding). But when input a cassation (frm_Kasasi), it will required some record from tbl_Banding. As well as frm_PK, it will require some record from tbl_Banding and tbl_Kasasi.

Hi
My apologies, Pat is correct I should have shown how this was fixed.

I say Fixed but in my opinion the Form is not constructed correctly.

I just modified the relationships in the Forms Record Source - "qry_PK to that shown in the attached.