Access Database Programming Assistance
Discussion
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
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
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 will come down to data structure and whether it makes any sense as to how achievable your requirements are.
By the sounds of it you need to get someone in to help with this, but expect to be paying for their time to understand how the system is built and functions plus where the data is stored before they can be of any use.
Another option might be dump all the data into SQL Server Express and see if you can get what you want that way. But then you've got the issue of having the data in 2 places (unless you link Access to SQL).
It will come down to data structure and whether it makes any sense as to how achievable your requirements are.
By the sounds of it you need to get someone in to help with this, but expect to be paying for their time to understand how the system is built and functions plus where the data is stored before they can be of any use.
Another option might be dump all the data into SQL Server Express and see if you can get what you want that way. But then you've got the issue of having the data in 2 places (unless you link Access to SQL).
I'd think it's possible, but it's hard to say for sure without more information. My guess is that when you click "mailing contact 1" the query extracts users where "boxa1" (the tick box A for contact 1) is set to "on" or whatever the ticked value is, and if that's the case then your modified query would just check for "boxa1=true or boxa2=true or boxa3=true" and so on.
It would be easier to achieve if the contacts were in a separate table linked to the master record, but some VBA should allow you to present it in the way you want. It might even be possible directly from a query, but my recollection of Access is that the SQL is a little more limited.
It would be easier to achieve if the contacts were in a separate table linked to the master record, but some VBA should allow you to present it in the way you want. It might even be possible directly from a query, but my recollection of Access is that the SQL is a little more limited.
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.SystemParanoia said:
so whats the benefit of using this vs SQL server \ MySQL with a HTML5\Jvascript frontend ?
Learning curve, no requirement for servers.OP- Can send me a copy of it? (dummy data is fine). If not too time consuming happy to help.
Edited by hyphen on Friday 23 June 23:35
budgie smuggler said:
Do the six queries output the same columns? If so perhaps you could rewrite them as a union.
Powerful is not the right word I would say, flexible and quick to create yes.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.What happens is that management on the business side need something quick, it then gets made by a power user on the business side and grows and grows becoming a critical system. IT want no part of it, but sooner or later are forced to support it. No one wants to pay for a re-write.
MSAccess if fine for quick turnaround/temp works but not for strategic solutions.
After a quick scan of the above, it seems to me your biggest issue is the schema design (actually lack thereof).
If your contacts were in a contacts table, you would have a single, simple query, and not be limited on numbers of contacts per customer.
You could then easily include more information about the contact
As a side note addresses should have the same normalisation performed. i.e. an address table, to allow multiple addresses for a customer.
This would additionally allow you to have different address styles/field labels for other countries.
And then business and contact telephones...
And then business and contact email addresses...
I hope the monkey was only charging in peanuts
The redesign task, and data migration strategy is simple enough.
I should thinks that the work itself would be pretty straight forward as well.
budgie smuggler has probably given the easiest work around 'fix'
But in all fairness what you've got there is a three legged dog with arthritis in 2 knee joints.
I don't really suffer from OCD, but working with your current design would almost certainly induce a case.
Happy to offer design and migration advice if you want it.
If your contacts were in a contacts table, you would have a single, simple query, and not be limited on numbers of contacts per customer.
You could then easily include more information about the contact
As a side note addresses should have the same normalisation performed. i.e. an address table, to allow multiple addresses for a customer.
This would additionally allow you to have different address styles/field labels for other countries.
And then business and contact telephones...
And then business and contact email addresses...
I hope the monkey was only charging in peanuts
The redesign task, and data migration strategy is simple enough.
I should thinks that the work itself would be pretty straight forward as well.
budgie smuggler has probably given the easiest work around 'fix'
But in all fairness what you've got there is a three legged dog with arthritis in 2 knee joints.
I don't really suffer from OCD, but working with your current design would almost certainly induce a case.
Happy to offer design and migration advice if you want it.
hyphen said:
Powerful is not the right word I would say, flexible and quick to create yes.
What happens is that management on the business side need something quick, it then gets made by a power user on the business side and grows and grows becoming a critical system. IT want no part of it, but sooner or later are forced to support it. No one wants to pay for a re-write.
MSAccess if fine for quick turnaround/temp works but not for strategic solutions.
I don't know why you're disputing what I said with apparently no experience to back it up. What happens is that management on the business side need something quick, it then gets made by a power user on the business side and grows and grows becoming a critical system. IT want no part of it, but sooner or later are forced to support it. No one wants to pay for a re-write.
MSAccess if fine for quick turnaround/temp works but not for strategic solutions.
I have worked on these systems, they were written from scratch to do what they do. They are still as far as I know churning out complex financial reports and such every day.
It's a front end for running queries against a server. It is equally as powerful as PHP ASP ASP.NET or any of the myriad frameworks for performing this task, in some ways more so. Would I choose Access to create such a system now? Nope, but that doesn't take away from what I said above.
Edited by budgie smuggler on Tuesday 27th June 09:40
budgie smuggler said:
I don't know why you're disputing what I said with apparently no experience to back it up.
I have worked on these systems, they were written from scratch to do what they do. They are still as far as I know churning out complex financial reports and such every day.
Experience: I have created, enhanced and migrated 100+ of these for household name FTSE 100 companies and other global companies from Access 97 onwards. Standalone, Split,vba,odbc, oledb and so on.I have worked on these systems, they were written from scratch to do what they do. They are still as far as I know churning out complex financial reports and such every day.
Your definition of 'big and complex' differs from mine I think. All large institutions rely on the like of Oracle, SAP and other competing offerings for their true complex financial reports with Access serving department/project/programme level needs. If you don't get to the stage where you say 'we need to now move away from Access' then the job it is doing isn't complex enough.
You have to remember Access does not offer the more sophisticated testing abilities that modern software development techniques insist on. I'm not knocking Access as it serves an essential need for businesses and saves companies many hundreds of thousands a year as well as making me a fair bit of money over the years!! It is an excellent product for what it does.
hyphen said:
Experience: I have created, enhanced and migrated 100+ of these for household name FTSE 100 companies and other global companies from Access 97 onwards. Standalone, Split,vba,odbc, oledb and so on.
Your definition of 'big and complex' differs from mine I think. All large institutions rely on the like of Oracle, SAP and other competing offerings for their true complex financial reports with Access serving department/project/programme level needs. If you don't get to the stage where you say 'we need to now move away from Access' then the job it is doing isn't complex enough.
You have to remember Access does not offer the more sophisticated testing abilities that modern software development techniques insist on. I'm not knocking Access as it serves an essential need for businesses and saves companies many hundreds of thousands a year as well as making me a fair bit of money over the years!! It is an excellent product for what it does.
Okay hyphen, I agree with most of that but I think you're missing my point which is that for this specific case, the front end does very little. It kicks off a stored procedure (or, in the OP's case, a simple query) based on a bunch of parameters selected by the user , then it collects the results and either exports them to something else or presents them to the user. For this specific task it is equally as powerful as any other framework, platform etc since they do the exact same thing i.e. very little.Your definition of 'big and complex' differs from mine I think. All large institutions rely on the like of Oracle, SAP and other competing offerings for their true complex financial reports with Access serving department/project/programme level needs. If you don't get to the stage where you say 'we need to now move away from Access' then the job it is doing isn't complex enough.
You have to remember Access does not offer the more sophisticated testing abilities that modern software development techniques insist on. I'm not knocking Access as it serves an essential need for businesses and saves companies many hundreds of thousands a year as well as making me a fair bit of money over the years!! It is an excellent product for what it does.
As for scale, some of our reports took in excess of a day to run on very very expensive hardware, and they were very very finely tuned for performance. But as above, the serious number crunching was done in the backend and this is where 99.995% of the development and testing took place. The amount of data was in terabytes before anyone knew what that term meant.
Think of 5 big banks, almost certainly you will have named 3 or more who used/still use it.
anonymous said:
[redacted]
It's not meaningless, it's broadly the same as what's happening in the OPs example. It's just the backend in his case happens to be Access itself.Enter params -> process data -> display/export
Edited by budgie smuggler on Tuesday 27th June 11:01
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff