Need help to create a query between 2 table

[Nushan]

Hi guys.



Please help me to create a query between 2 tables. I am new to Access.

Table 1 : CB_Data (from Branch)
Table 2 : CB_Payment_Info (Country Duty Paid Data)

I need to verify whether "code_2" product charges already paid by country or not. tried IIF function,it doesn't work

IIF( [CB_Data]![Code_2] = [CB_Payment_Info]![Code_2] ,"Paid","Pending") .


Thanks in Advance.

[isladogs]

Add both tables to your query. Add a join to link both tables by the Code_2 fields.
Add the fields you require to the query and filter the Code_2 field value = "Paid"

[Nushan]

Is it the same way to bring "Paid" & "Pending" like in excel if statement???

[isladogs]

Excel and Access are very different in terms of analysing data.
Do you want to see individual records showing whether Paid or Pending?
Or do you want a count of the number of Paid records and the number Pending?
Or something else?

[Nushan]

Yes, I need to know whether individual record was paid or not. If i include "status"(Paid/Pending) in CB_Payment_Data & link to CB_Data and use IIF function like " IIF( [CB_Data]![Code_2] ="Paid","Paid","Pending") ", will it work???

[isladogs]

Do what I wrote in post #2 without the filter.
You can certainly use an IIf function to display an extra field but of course that field won't be stored in your table (and probably shouldn't be)

However I'm not sure whether you need that extra field at all.
It would help me to help you if you posted a few records of example data (modify if anything is confidential) together with the expected output

[Nushan]

I have attached the file. Thank you..

[isladogs]

You also need to tell me what the expected output would be with that data
You have 6 records in all, 5 records in the PaymentInfo table and 3 in the other table (one not in the PaymentInfo table).
There are 2 records with the same data in both tables.
How many of the records and what results do you want in your query?

However, why are you duplicating data in the two tables?
Doing that goes against database design principles. Data should be stored once only.

Both your tables have the same structure but with an extra field in the PaymentInfo table.
Why not just use one table?

Also neither table has a primary key field. This is a basic table requirement.
Will ProductID be unique? If so make that the PK.
Otherwise add a new autonumber field as the primary key.

Before you go any further I strongly recommend that you spend some time researching the basics of database table design including the idea of normalising data. After that read up about queries.

[alansidman]

Here is your SQL statement for your current design. However, Colin makes very good sense.

SELECT CB_Data.[Product ID], CB_Data.Code2, CB_Data.[Duty Charges], CB_Payment_Info.[Duty Charges], CB_Payment_Info.BPV, IIf([CB_Data]![Duty Charges]=[CB_Payment_Info]![Duty Charges],"Paid","Pending") AS Status
FROM CB_Data INNER JOIN CB_Payment_Info ON CB_Data.[Product ID] = CB_Payment_Info.[Product ID];

Attached is a good read on data base design

[isladogs]

Hi Alan
Thanks for joining in to assist

For the benefit of the OP and referring to the records as mentioned earlier, using an INNER JOIN will give the 2 common records. Left or right outer joins will give 3 or 5 records and a union query (full outer join) will give all 6 records.

If all of that means nothing to you, it reinforces the point about learning database basics

[Nushan]

Thank you for your help and advice guys. I just started to learn Access, and i don't understand sql statement yet. I will close the thread.. again thanks for your help.