Creating Reports

This is my first database. I have created 2 tables and forms to enter data into these tables. Now I am trying to create reports to view the data the way I want. I am hoping someone can help.

My first table consists of the client list. Second table contains records of the clients Dr. visits, dates and issue. This table will have several records for each client and will be added to as time goes. Tables are linked by there SSAN's.

I want to create a report where I can select any client I want and it will show all the records just for that client on the printout. It would also be great if I could select a particular date range to view and print for an individual client.

The report I created shows each seperate record in table 2 on a different page. I want the clients name at the top and then all dates and issues for that client under that.

It would be great if someone could explain how to do this. I would greatly appreciate it. After I get the report creating figured out, I should be able to do it all(tables, forms, and reports).

Thank You,

Chad

Create a form with a combo box and two text box.
Form combo box
row source type - your first table name
row source = clientlist field

set two textbox data type to date.

put one command button for previewing a report.
Create a query with details of second table something like this

Select * from secondtable
where clientID = forms!yourformname!combox and datefield between
forms!yourformname!BeginDatefieldtextboxname and
DateAdd("S",86399,forms!yourformname!EndDatefieldtextboxname)

Create a tabular report using wizard using above saved query as record
source and save it.

now put the following command in your forms preview command button.
docmd.openreport yourreportnameinquotes, acViewPreview.

Now it will work. I hope i am clear. All the best.

I am going to give that a try and see how it turns out. I may be back for more.

Thanks,

Chad

Can someone please explain this in better terms that a new Access user will be able to understand? I sure would appreciate it.

Thank You,

Chad

If you can send your MDB zipped with the tables you have defined with some data I will finish it and send it to you. Please also mention whether it is Access97 or Access2000.

you could use a much easier method of just creating diffirent queries... eg a queary where you have Date()-7 criteria in date field, all it does shows all the records for last week, and so on...

I really wish I could, but there are peoples SSAN's in the database and I cannot make those public.

Chad

you can post the mdb with dummy data and dummy table structures. You need not post the original one.

supersubra:

The original is 1.5MB in size. I made a copy of it, then deleted all the data and added couple dummy records. The copy is the same size as the original with all the data gone. To me, this does not make sense. I used Winzip to bring it down to 400kb, so I am not able to upload over 100kb. Why is the copy the same size?

Thanks,

Chad

You need to compact your DB,

Tools, Database Utilities, Compact

Access likes to hold on to empty space

S-

I have it compacted and zipped down to 104kb. Is there a way to get rid of that empty space?

Thanks,

Chad

Anyway to upload a 108KB file?

Thanks,

Chad

Create yourself a free FTP site with Yahoo Breifcase and upload it there. The send a private message to supersubra telling him you username and password so he can get it and look at it.


S-

I have made some modifications in table structure, primary key etc and created a data entry form and report selector form which will serve all your requirements and will work as a full fledged application. If you think I have overdone it just ignore.

Thanks a million!!! I am going to study this and try to figure out how you did that. I appreciate you taking the time to do this, not many people would offer such a thing!!!

Thanks,

Chad