Hi ssl
I see a few different approaches that you may want to examine, depending on how much flexibility you have with this and your requirements...
1) Restructure your table(s)
Personally I probably would have normalized this table differently - either stored these 2 things in 2 different tables (if you were tracking different attributes from credit card/visa vs passport) or, more likely, in 1 table with a "Type" field of some sort to identify type as "VISA", "PPORT", "MC", "AMEX", etc - if you tracked the same attributes for all of them. That way you could simply create your report and group by Format(ExpiryDate, "mmmm") and then sort by Type within each group.
2) Change your layout requirements
To do the layout you want below, is difficult the way you have the table structured now. You can either show all the Visa expiries by month followed by the Passport expiries by month, but because you are grouping by exipiry date and those dates are in 2 different fields you can't do it. As it exists now, you will still want to group by Month, but it's a question of getting the expiry dates from the Visa and Passports into a single field to group by.
3) Use a month table
If you are wanting to show things expiring by month (where you are planning to show all months in a year on the report or a particular month that has been selected via some sort of criteria selection form), then I might try creating a tblMonths table (lists all the months in a year - name, abbrev, numeric month #) and using that along with my empl table as my report's query record source.
Something perhaps like this:
SELECT tblMonth.MonthName, zztblTest.emplname, zztblTest.VisaNo, zztblTest.VisaExpiry, zztblTest.PPortNo, zztblTest.PPortExpiry, IIf(Month([VisaExpiry])=tblMonth.MonthNo,True) Or IIf(Month([PPortExpiry])=tblMonth.MonthNo,True) AS SomethingIsExpiring
FROM zstblMonth, zztblTest;
The SomethingIsExpiring field will contain True if either the Visa Or Passport expiry field's months match the listed month. Then you can group by the tblMonth.MonthName field in your report. If SomethingIsExpiring, test which one.
I can expand more on this, but I'd go with Option#1 above if I could.
Hope this helps,
RandomElle
Hi,
I need help in ms access report. I have table which contain following fields
employee name, passport no, passport issue date, passport expire date,visa no, visa issue date, visa expire date.
I want to generate single report showing passport & visa expiring (monthwise) on given month. ex as below
-----------------------------------------------------
December
Passport
emplname passport no pp expire date
Visa
emplname visa no visa expire date
---------------------------------------------------
thanks
ssl