473,327 Members | 2,071 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Ms Access Report

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
Dec 29 '07 #1
1 1617
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
Dec 29 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Nicola | last post by:
Hi Everyone, I am new to programming and would like to know how to open an access Report from within vb 6. I am trying to write a program to organise cross stitch threads. I have found out how...
1
by: Joris Kempen | last post by:
Hi people, I know that the question has come around sometimes: How to open an Access Report using ASP and export it to for example RTF. I'm trying to implement the first method of David...
20
by: John | last post by:
Hi, I've recently upgraded from Access 97 to Access 2002 & the performance basically stinks. I have tried the following items listed below but it has only had a minor impact: 1) Upgraded Jet...
8
by: Mike MacSween | last post by:
tblCourses one to many to tblEvents. A course may have an intro workshop (a type of event), a mid course workshop, a final exam. Or any combination. Or something different in the future. At...
7
by: dog | last post by:
I've seen plenty of articles on this topic but none of them have been able to solve my problem. I am working with an Access 97 database on an NT4.0 machine, which has many Access reports. I...
11
by: Grasshopper | last post by:
Hi, I am automating Access reports to PDF using PDF Writer 6.0. I've created a DTS package to run the reports and schedule a job to run this DTS package. If I PC Anywhere into the server on...
2
by: Dean Slindee | last post by:
Anybody written code in VB.NET to: 1) show a print preview window of reports already written and stored in an Access 2002 database; or 2) execute the print of a report stored in an Access 2002...
6
by: Bob Alston | last post by:
I am looking for Access reporting add-in that would be easy to use by end users. My key focus is on selection criteria. I am very happy with the Access report writer capabilities. As far as...
2
by: amith.srinivas | last post by:
Hi all, From a word macro in VBA, I am trying to create a report in access DB. The report is based on a query with a parameter. I am using Set rpt = Application.CreateReport rpt.RecordSource =...
9
by: prakashwadhwani | last post by:
Hi !! I'm about to develop a new project for a client. Should I go about it in Access 2003 or 2007 ? Purchasing it either for me or for my client is not a major consideration here ... what I'd...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.