Query single user defined paramter against multiple columns

[longer73]

I’m self-taught in MS Access and haven’t done much with 2010 since I learned a long time ago in 2003. So please don’t hesitate to be explicit I have thick skin and can use the direct guidance.

I have a table that contains all the data for the dates in which a person is to be paid out there contract bonus. The basic table structure has SSN defined as the primary key other pertinent identifying info sex, race, paygrade etc. (you can see below). There are no other tables.

Once a person signs the contract they are entered into the database and all values are calculated for the payment of the contract which spans the next several years (8 years). The values of the payments change at certain points in time, based on the number of years into the contract. Payment dates are based on your anniversary date of commissioning.

Here is my problem how do I search for anyone how is to receive a payment within the month of Jan 2018 for example (between 1/1/18 and 1/31/18)? This date could be in any of the YCS#_Pay_Date columns.

In the end I have to output a spreadsheet that has SSN, Name, Rank, DESIG, YCS#_Payment, YCS#_Pay_Date (all of these ‘Pay_Dates’ need to be within the month/year specified by the user).

Table structure Data Type
SSN Text 111111111
OAIS_Name Text TESTCASE
Rank Text
DESIG Text 1110
FLAG Yes/No FALSE
YearGroup Text 20120
Sex Text M
Ethnic Text
Race Text 5
CSR Text 6FAAZ
ADSD Date/Time 31-Dec-11
ACBD Date/Time 22-Mar-12
MSR Date/Time 01-Mar-16
Look Text
Notes Memo
AppDate Date/Time
Bonus Text DHRB
Total_Amt_Paid Currency $105,000.00
YCS3_Pay_Date_10K Date/Time 30-Nov-16
YCS3_Payment Currency $10,000.00
YCS3_Pay_Notes Text
YCS3_Paid Yes/No TRUE
YCS4_Pay_Date_10K Date/Time 22-Mar-16
YCS4_Payment Currency $10,000.00
YCS4_Pay_Notes Text
YCS4_Paid Yes/No TRUE
YCS5_Pay_Date_10K Date/Time 22-Mar-17
YCS5_Payment Currency $10,000.00
YCS5_Pay_Notes Text
YCS5_Paid Yes/No FALSE
YCS6_Pay_Date_15K Date/Time 22-Mar-18
YCS6_Payment Currency $15,000.00
YCS6_Pay_Notes Text
YCS6_Paid Yes/No FALSE
YCS7_Pay_Date_15K Date/Time 22-Mar-19
YCS7_Payment Currency $15,000.00
YCS7_Pay_Notes Text
YCS7_Paid Yes/No FALSE
YCS8_Pay_Date_15K Date/Time 22-Mar-20
YCS8_Payment Currency $15,000.00
YCS8_Pay_Notes Text
YCS8_Paid Yes/No FALSE
YCS9_Pay_Date_15K Date/Time 22-Mar-21
YCS9_Payment Currency $15,000.00
YCS9_Pay_Notes Text
YCS9_Paid Yes/No FALSE
YCS10_Pay_Date_15K Date/Time 22-Mar-22
YCS10_Payment Currency $15,000.00
YCS10_Pay_Notes Text
YCS10_Paid Yes/No FALSE
YOS_25 Date/Time
YCS_25 Date/Time

 

[james_lankford]

will be slow, but try

select 
  * 
from 
(
  select 
    SSN, 
    YCS3_Pay_Date_10K as PayDate 
  from 
    your_table 
    
  union all 
    
  select 
    SSN, 
    CS4_Pay_Date_10K as PayDate 
  from 
    your_table 
    
  union all 
    
  select 
    SSN, 
    CS4_Pay_Date_10K as PayDate 
  from 
    your_table 
    
  union all 
    
  select 
    SSN, 
    CS5_Pay_Date_10K as PayDate 
  from 
    your_table 
) as tbl 
where 
(
  (
    month(PayDate) = [param_month] 
  )
)

[param_month] wiil be the month number

so in your example it would be 1

 

[longer73]

will be slow, but try

select 
  * 
from 
(select SSN, YCS3_Pay_Date_10K as PayDate 
  from your_table 
    
  union all 
    
  select SSN, YCS4_Pay_Date_10K as PayDate 
  from your_table 
    
  union all 
    
  select SSN, YCS5_Pay_Date_10K as PayDate 
  from your_table 
    
  union all 
    
  select SSN, YCS6_Pay_Date_15K as PayDate 
  from your_table ) as tbl 

where 
(  (    month(PayDate) = [param_month]   ))

[param_month] wiil be the month number

so in your example it would be 1

This works! Thank you!

Additional question [param_month] is there a function/method or property to month and year for "PayDate" i.e. Jan-18.
So user enters 1/1/18 and PayDate is set to JAN18?

I assume, I can simply add more fields in the select statement to finish gathering necessary data.

Again thank you.

 

[Micron]

try MonthName(number,True) and concatenate Day(yourdate)
You may have to use a function to get the month number as in MonthName(Month(yourDate),True)
I would have tried to write it for you, but I have no idea as to what you want to use it against (e.g. form controls, this union query, or what).
Lots of useful functions here