Access reports

[Duncan]

I have a simple completed working database in which clients are given a unique 5 digit record number. For the clients we record when they use our services etc which forms part of the monthly reports. However some of the clients are coming back and as they use the same record number they are being recorded on a separate table for primary key and relational database purposes. All this side of the data base is working well.


For the reports I want to be able to combine certain aspects of the two tables like service dates. I’m really struggling with what query I can use to join the data that might be tiggered to be run when the report is requested or on database starts-up. The data will need to be updated (or output table replaced) each time the report is run to capture any new info from each table.

[R_Badger]

Actually, you are simply over thinking it!

You create a new select query, and add both tables, if your relationships have been setup beforehand, you should see a relationship line between the fields, this defaults to show all records where both fields are equal (if i recall correctly).

If thats the case you should be able to just add all the fields you need to the query.

No need to worry about updating it, as it automatically gets the latest data each time you open it.

Use the query for the recordsource on your report and jobs a good'un

If you have problems post back and I'll see if i can throw an example together.

[Duncan]

Thank you for the reply.
I have given it a go and I know I’m missing something small. When I run the query I only get records that match (say 4 records). I have attached a database with the two refined tables in question. I would like to add the txtDOA dates from the master table ‘tblclientprofilesINPUT’ with txtrDOA dates from the secondary table (data is only dumped into this table when a client uses our services for a second or third time etc) ‘tblclientprofils2’ in the one column. This column then forms the monthly report data tracking numbers of clients that use our service each month.

[R_Badger]

Having had a look your data just needs some normalisation.

Remove the txtDOA and txtfinishdate fields from the tblclientprofilesINPUT, and instead have those fields directly input onto the second table tblclientprofils2
This way all your data will be stored in the one table.

When I get a chance later I will upload a normalised example.

[Duncan]

Thanks I have sorced the answer to my problem as below;

SELECT [Field1],[Field2],[Field3] FROM [BAXSaver Canada]
UNION ALL SELECT [Field1],[Field2],[Field3] FROM [BAXSaver Hawaii]
ORDER BY [Field1], [Field2];Source(s):

http://office.microsoft.com/en-us/assistance/HP051880521033.aspx