Report thats way complex for me.... 2

[herbal]

I have a membership application that uses an access database. The DB has two tables, members and expiredMembers. When a member has expired the DB moves the record to the expiredMembers table and creates a new record in the members table.

I need to make a report that will print both members added, as well as expired members for any given three day period.

I'd like this report to print both on the same page provided that neither of the two require too much space. If for example there is an entire page of added members then it needs to continue on the second page and then put expired members under that.

Example....

Members Added:
J. Blow
J. Doe
T. Aikman
L. Allen

Members Expired:
E. Williams
E. Smith
M. Irvin

End of Report

Thanks in advance for your time!
Herbal

 

[nabiS2K]

I am going to assume that you are having trouble with how to combine the tables... try this...

SELECT Members.*, 'Current Members' AS Source FROM Members
UNION
SELECT ExpiredMembers.*, 'Expired Members' AS Source FROM ExpiredMembers

Paste this into the SQL section of a query.

Let me know if this starts you in the right direction or if you need something else.

 

[herbal]

nabi, thanks for your reply! Let me be a little more clear. I currently have a query that takes members that have expired from the expired table in a date range. I have a second query that takes members added from the members table in a date range.

SQL for expired:

SELECT ExpiredMembers.FirstName, ExpiredMembers.MiddleName, ExpiredMembers.LastName, ExpiredMembers.DateUpdated, ExpiredMembers.MemberID, ExpiredMembers.DL
FROM ExpiredMembers
WHERE (((ExpiredMembers.DateUpdated)>=[Retrieve deleted members since what date?] And (ExpiredMembers.DateUpdated)<=[date?]));

SQL for added:

SELECT Members.FirstName, Members.MiddleName, Members.LastName, Members.MemberID, Members.DateJoined, Members.DateExpired, Members.TDL
FROM Members
WHERE (((Members.DateJoined)>=[Retrieve new members since what date?] And (Members.DateJoined)<=[date?]))
ORDER BY Members.MemberID;

I need to be able to make a report that first lists the expired members and then on the same page list the added members(Provided that it will all fit).

I've tried making a report from the two separate queries to no avail. It gives an error about you've selected records from a form or something or other....I can post the exact error if need be.

Thanks again!
Herbal

 

[formerTexan]

Sub reports, Herbal. Create two reports (to use as subreports), set the recordsourecs to your respective queries and then place the subreports onto a third (main report) report.

Cheers,
Bill

 

[herbal]

Thanks Bill. Perhaps I should have expected the solution from a fellow Texan....Go Astros!

 

[herbal]

Ok...got the report working. One more question though...how can I make it dynamic so that if the number of members added took up the entire page, that the remainder of members added would start again on page 2 and then be followed by members deleted?

Thanks,
Herbal

 

[nabiS2K]

The only herbal that you might you second requirement is by combining the two queries into one by a union query as in my example.

Here is what I come with from your SQL. Paste this into a query to see what happens.

SELECT 'Expired' As [Type], ExpiredMembers.FirstName, ExpiredMembers.MiddleName, ExpiredMembers.LastName, ExpiredMembers.DateUpdated, ExpiredMembers.MemberID, NULL As DateJoined, Null AS DateExpired, ExpiredMembers.DL AS [TDL\DL]
FROM ExpiredMembers
WHERE (((ExpiredMembers.DateUpdated)>=[Retrieve deleted members since what date?] And (ExpiredMembers.DateUpdated)<=[date?]))
UNION
SELECT 'Added' AS [Type], Members.FirstName, Members.MiddleName, Members.LastName, NULL As DateUpdate, Members.MemberID, Members.DateJoined, Members.DateExpired, Members.TDL AS [TDL\DL]
FROM Members
WHERE (((Members.DateJoined)>=[Retrieve new members since what date?] And (Members.DateJoined)<=[date?]))
ORDER BY Type DESC, MemberID;

 

[herbal]

Thanks Nabi...that looks great...my only question now is how to configure my report to separate the expired members from the added members as in the example above?

Thank God for all of you folks! I'd be lost without you!

Herbal

I'm sorry if these questions are very basic, but Access is without a doubt my biggest shortcoming.

 

[nabiS2K]

You need to create a group (with a group header) on the field 'Type' (which I created in the SQL Statement to return 'Expired' or 'Added'). Once you create the group header, you need to put the textbox for the field 'Type' in that header.

You create a grouping by going to the View menu and choosing Sorting and Grouping... You can also click on the Sorting and Grouping... icon which should be in the report toolbar.

 

[herbal]

thanks everyone! I really really appreciate it.

 

[herbal]

nabi...I just noticed that the SQL statement you gave me earlier works great except that for some reason the added members DateJoined and DateExpired are NULL just like the expired. I looked over the SQL statement and I don't really see any problems. You have any suggestion as to why this is the case?

BTW, the rest works great!

Herbal

 

[nabiS2K]

It's possible the query is having a data type issue since the first query does not have a single value for the each record in those fields. Union queries will use the data types from the first SELECT statement as its data types.

Would it be possible for you to change the NULLs to something like #1/1/1900# which is often used in place of NULL values?

 

[herbal]

Nabi....your knowledge of Access is unfair! Great suggestion! On to the next problem.

Thanks a million!
Herbal

 

[herbal]

Nabi,

Any idea why the original SQL statement you gave me on this would ask for first date and last date of added members, but then only ask for the first date of expired?

Honestly, I'd like to make them be the same dates so as to only enter the date range one time.

Thanks again...

 

[nabiS2K]

Yes I do know why...

In the first select statement, you have two separate parameters in the WHERE statements: WHERE(((ExpiredMembers.DateUpdated)>=[Retrieve deleted members since what date?]... and in the second you have WHERE (((Members.DateJoined)>=[Retrieve new members since what date?]. To only have one parameter, you need to make [Retrieve deleted members since what date?] and [Retrieve new members since what date?] the same parameter, such as [Retrieve since what date?].

;-)

 

[herbal]

you're the bomb! Ok....god...I know I'm gonna make you crazy....but...I setup a calculation in my report to count expired and added members.

If I put the textboxes with calculations in type header...they both get the same total which is incorrect. It is the first group found that is counted for both figures. If I move the textboxes to the report header then it counts both expired and added together for both calculations.

So, how do I make count the added members as one calculation and the expired as another?

Thanks!

 

[nabiS2K]

Go back to where you added a Group Header and also add a Group Footer. This will sum each group separately.

 

[herbal]

What you said makes perfect sense. I don't think I was clear enough though. I'm trying to make it so that at the end of the report it will list the number of Expired members followed by the number of Added members, and then the number of active members.

Sorry for my poor communication.