Access Database Programming Assistance

[MikeOxhard]

Morning all,

Can anybody please point me in the right direction of tidying up an Access database I've inherited.

I am trying to make life easier for all involved in using it for the purposes of mailshots - both electronic and postal.

Currently I have created queries to export the relevant information to Excel spreadsheets that can then be merged in Word and labels etc produced.



The above image shows the system as it is currently.

The current query works by clicking on Mailing Contact 1 button which queries if contact 1 has the 'A' field ticked and also the relevant Mail Week - the results are exported to a Excel spreadsheet.

There are 6 queries matching each of the relevant contacts - up to 6 per customer record.

These have to be run as separate queries and exported separately into separate spreadsheets and then merged into a final Excel spreadsheet before creating the mailmerge.

What I wish to achieve is one button for postal mailing that queries the relevant mail week and then queries the 6 contacts in one go, exporting only the ones with the 'A' field ticked - they obviously all have the same postal address so I need to export the name and address as a separate record for mailmerging.

The main customer table behind the scenes has all this information in one row - client address info and up to 6 contacts as separate fields.

Is this something that is relatively easy to do as I can't see how to create the query to give me what I'm looking for.

I'm thinking along the lines of a number of sub queries run by a master query that accumulates the data all into one table for export but can't seem to understand how to progress.

Can it be done with what I have already or am I into a major database redesign ?

Simon

[MikeOxhard]

Thanks for all the feedback.

It is an old database that has been tweaked and amended by so many people over the years it's going to be a pain whichever way we turn !

I'll review the above and make contact - thanks for assistance :-)

Simon

[MikeOxhard]

budgie smuggler said:

Do the six queries output the same columns? If so perhaps you could rewrite them as a union.

grumbas said:

I used to do a lot of work in Access about 15 years ago, what you've got there looks to be stretching the abilities of Access to the limits.

It's not, you'd be surprised how powerful it is. I used to work as a programmer for a place which had some very very big and complex systems running with Access as their front ends. Large financial institutions etc. You can use SQL Server (and possibly other ODBC sources, I forget now) as the backend with Access running as a frontend.

Thanks very much - I've created a union query and all working fine.

I think this will be a short term fix whilst we decide the way forward with CRM etc.