Convert Subquery into Parameter Query

Question

Hi,

I would be glad if you could please explain how I would  be able to create a parameter query to enable me input the values

m1=m2=m3=m4 = 5 and

y1=y2=y3=y4 = 2016, rather than hard coding the values into the sub-query design grid. 

The SQL is shown below:

SELECT master.id AS RegNo, master.City AS City, master.Region AS Region, master.FN AS FirstName, master.LN AS LastName, master.Fon AS PhoneNo, master.Dob AS BirthDate

FROM master

WHERE ((([master].[id]) Not In (SELECT id_1 From tbl_1 where m1 = 5 and y1 = 2016) And ([master].[id]) Not In (SELECT id_2 From tbl_2 where m2 = 5 and y2 = 2016) And ([master].[id]) Not In (SELECT id3 From tbl_3 where m3 = 5 and y3 = 2016) And ([master].[id]) Not In (SELECT id_4 From tbl_4 where m4 = 5 and y4 = 2016)));

Basically, there are five tables in the database tbl_1, tbl_2, tbl_3, tbl_4, and master.

m1 and y1 represents month and year respectively in each tbl_1.

m2 and y2 represents month and year respectively in each tbl_2.

m3 and y3 represents month and year respectively in each tbl_3.

m4 and y4 represents month and year respectively in each tbl_4.

I  would be glad to get your help.

Thanks for your time.

BenGik.

Answer 1

Like this, assuming that you want the same month and year in each subquery:

SELECT master.id AS RegNo, master.City AS City, master.Region AS Region, master.FN AS FirstName, master.LN AS LastName, master.Fon AS PhoneNo, master.Dob AS BirthDate

 FROM master

 WHERE ((([master].[id]) Not In (SELECT id_1 FROM tbl_1 WHERE m1 = [Enter Month] AND y1 = [Enter Year]) AND ([master].[id]) Not In (SELECT id_2 FROM tbl_2 WHERE m2 = [Enter Month] AND y2 = [Enter Year]) AND ([master].[id]) Not In (SELECT id3 FROM tbl_3 WHERE m3 = [Enter Month] AND y3 = [Enter Year]) AND ([master].[id]) Not In (SELECT id_4 FROM tbl_4 WHERE m4 = [Enter Month] AND y4 = [Enter Year])));

In Query Design view, click Parameters on the Design tab of the ribbon.

In the Parameter column, enter [Enter Month], and select Long Integer from the Data Type column.

In the next row, enter [Enter Year] and select Long Integer.

Then click OK, and save the query.

Answer 2

Hi,

Your suggestion did a nice job and the query is working good and fast.

BenGik

Answer 3

Hi,

Thanks for taking the trouble to look at the problem.

You asked "Why are you using separate tables instead one table with a field (DateTime) to designate the month and year."

The reason is that I have a master table that contains registered members who should make payments in one of four ways. The four ways are implemented in the four other tables each way is done in one table. A member should make at least one payment type. 

We want to know those who did not make any payment for a particular month.

We could have used a combo to link the payment types so as to select particular payment for a member but the designer of the database did it like that before I was employed to generate this report.

I have informed them I have to redesign the tables to simplify things. 

Thanks for your observation. I would go ahead and implement it.

HansV's suggestion provided a solution and I'm glad to you all.

Bennet.

Answer 4

Basically, there are five tables in the database tbl_1, tbl_2, tbl_3, tbl_4, and master.

m1 and y1 represents month and year respectively in each tbl_1.

m2 and y2 represents month and year respectively in each tbl_2.

m3 and y3 represents month and year respectively in each tbl_3.

m4 and y4 represents month and year respectively in each tbl_4.

Why are you using separate tables instead one table with a field (DateTime) to designate the month and year?