New to Access

[Capt Scott Cook]

Hello and good morning. When I say that I am new to Access I mean I am new to making changes in Access.

We have had an Access database for many years. We are a Newspaper business and we use Access for our subscriptions and billing and label printing.

The one thing we find difficult is that it seems like we take a long time finding entries to either delete or edit or create a new one. I have played around with Switchboards and have not been successful with that. Our tables section is broken down into our different towns, or town routes. and we have to go from table to table to find the right person. Obviously, we have a pretty good idea which table they are in, but that's not always the case.

Ideally, I've been trying to develop a templet of some sort. Not sure if that is what it is really called. Something that we can search all tables for a name to easily change it. Or an add entry with a drop-down menu to put it in a table. Is any of that possible?

[ranman256]

ALL customer names should be in 1 table, tSubscribers
all towns in tTown table
and tSubscriptions
tRoutes

is this the case?

[Capt Scott Cook]

No, I tried to upload a sample but it wouldn't let me, too big. Each route, for example, Ephrata RR#2, has everybody in that route name, address, expiration date info. I still to this day feel that it's totally set up wrong. It was set up by an Office supply store and they are no longer in business, and it was the only way they knew how to do it. One of the two guys that set it up literally read a book on access on his drive to us.

[CarlettoFed]

This shouldn't be difficult to accomplish but you need to have the file structure to work on, to attach a file there are two ways:
First method - you must first compress the file, using a special program (WinZip, WinRar, ...), and saving the file must always be of the zip type, then attach it using the button with the image of the attachment.
Second method - post the file on a file sharing site, such as OneDrive, DropBox, ... which will release the string with the address of the link to be copied, then insert it via the button with the first image in the world with the concatenate symbol bottom right of the same.

[Capt Scott Cook]

Hope this helps


gcj.zip

[mike60smart]

Hi Capt Scott Cook

Problem with the database is that you have no Normalisation applied.
You have multiple tables, all with the same number of fields.
This is completely wrong.

[orange]

Capt.,
I agree with Mike - the major issue with your current use of Access is it shows a lack of database principles.
Tables seem to be the same structure. Tables mostly have No Primary Key and No Indexing.

It isn't clear what the purpose of the database is. It appears that you take some subset of records and create or extend tables by means of queries.

Can you tell us a little more about

subscriptions and billing and label printing

.

Do you have a table of Clients or Subscribers/Customers?

Good luck.

[Capt Scott Cook]

Do you have a table of Clients or Subscribers/Customers?

Good luck.

We have a list of Subscribers seperated by where they live for mailing of the paper.

[davegri]

gcj-davegri-v01.zip
See attached db.
It combines all the subscriber tables into one new table, tblSubscribers.
tblSubscribers contains a foreign key pointing to a new table, tblRoutes. This serves to determine which old table each subscriber in tblSubscribers was in previously.
All the old tables are in tblSubscribers except for [Seattle Dist Mixed States] because it has a slightly different format from all the others. If this is resolved, it can also be added.

The new form allows you to lookup any subscriber, regardless of the route. There is also a filter dropdown to restrict the form to a specific route if desired.

This is just a start for converting the application to a more modern configuration. Additional custom switchboard forms and coding will be needed to accommodate all the necessary functionality.

[Capt Scott Cook]

That looks awesome. It gives me a good place to start. I think I should be able to add the missing parts and make things much easier. Our number one problem is that there are too many people that access it and they tend to screw around with it.

gcj-davegri-v01.zip
See attached db.
It combines all the subscriber tables into one new table, tblSubscribers.
tblSubscribers contains a foreign key pointing to a new table, tblRoutes. This serves to determine which old table each subscriber in tblSubscribers was in previously.
All the old tables are in tblSubscribers except for [Seattle Dist Mixed States] because it has a slightly different format from all the others. If this is resolved, it can also be added.

The new form allows you to lookup any subscriber, regardless of the route. There is also a filter dropdown to restrict the form to a specific route if desired.

This is just a start for converting the application to a more modern configuration. Additional custom switchboard forms and coding will be needed to accommodate all the necessary functionality.

[orange]

Capt,

Dave has posted a structure and description based on his interpretation of your requirements. It would be useful to readers if you could comment/revise/extend his description of what he did and his understanding of the "business involved".

In overview, you have Customers/Subscribers to whom you deliver Newspapers via a number of Carriers. The delivery is divided into Routes by some geographic feature (address, postal/zip, town..). There is also a Billing and Payment cycle associated with this Newspaper business. There may also be a need to identify current and past subscribers, current and past Carriers, current and pat Newspapers and all associated Billing and Payment data depending on your need for historical information.

Are you associated with the creation/editing/production of any newspaper? That is, is it correct that your organization receives and delivers newspapers?

Good luck with your project.

UPDATE:
I noticed that our posts have overlapped. If too many people can change things independently, then your key issue may be one of management. More precisely info/data management. A viable business needs to account for its customers and associated billing and receivables. It should be a process with some discipline and traceability ( read that as -Not willy-nilly).

The Database Planning and Design link in my signature has several articles that may be useful to you. Dave has offered a starting structure. I would recommend you layout all the processes involved and get a good understanding of the business. You need a dependable database and you need to remove the

there are too many people that access it and they tend to screw around with it.

Good luck. Forum members will offer advice and comments to assist if necessary.

[Capt Scott Cook]

Here is our business in a nut shell. We produce and print a newspaper, we are an 112 year old business, on average change happens about every 20 years. What we do with the Access is we keep a record of every subscriber, mailing address, expiration date. And with that we print mailing labels for the papers that we mail and not delivered by Newspaper carriers. For each person we have name, address, exp date, if they are an active subscriber or not, bill to address (as needed) and what mailing group they are in for billing purposes. For billing we send out a reminder card a month from expiration, and a second notice when their subscription is going to be cut off. We only have a circulation of 1800 subscribers or sales, so we are not always cutting people off the day the subscription gets cut off, not my choice but the choice of the managers above me.

And as things go in small businesses these days, everyone wears many hats. I for one am the Carrier/Circulation Manager/Lead Pressman/Delivery in-take Manager. We have reporters that do news stories and take phone calls for missed papers, new subscriptions, renewals, and talking to people off the street, we are still an open door business after all. We no longer have a secretary sitting at the front desk. If we had then they would be the only one who would be entering names into the data base.

What I need from the data base to do is this. Keep records of subscribers, keep them in the mailing groups. In reality it wouldn't matter where in the database they are as long as I can print out labels for them in a group, example, everyone in the zip code group SCF Pasco 911 need to be printed out in that group, everyone in Ephrata RR#2 needed to be printed in that group...so on and so on. Every one in the Carriers table don't get mailing labels printed, but they get a label for billing.

The more I read this the more I feel I am complicating it. I am trying to describe what I need in the terms I know. I was the person 19 years ago that sat down with the two guys that helped me set this up in the first place. There was not here is what we are doing and why we are doing it. Ive been able on my own to create a Switchboard when one click and the labels print out, one click it does the billing, one click it prints the billing labels, but that is it.

Oh one thing that is totally messed up is our billing. We have to manually change the date on our computer back one month to get the billing to do our second billing, and if it is done in the wrong order (like change the date and then open Access) then Access dont like it freaks out and shuts down, the computer goes crazy, I get a nasty pop up from Microsoft about wrong date wrong subscription or something like that. I always remember to change the date before starting the second billing cycle.

[orange]

Capt,

You are a very busy person (many hats). Thanks for providing the info.

I see this as your identified priority.
What I need from the data base to do is this. Keep records of subscribers, keep them in the mailing groups.

But on reading your story, I see this as critical.
Oh one thing that is totally messed up is our billing.

and this is a symptom of chaos that needs addressing
Our number one problem is that there are too many people that access it and they tend to screw around with it.

I'm not sure of your familiarity with database, Access or queries. But I can assure you that if you have a table of Subscribers with Address info and a flag/indicator to identify Active, then you can retrieve groups based on address/town/zipcode via a Query (no need for individual tables).

I'm not sure what a Carrier is in your set up. In my mind I think of the "paper-boy" of older times.
Every one in the Carriers table don't get mailing labels printed, but they get a label for billing.

The Database Planning and Design link in my signature has lots of articles/videos that may be useful to you. Within those articles are some tutorials by RogersAccessLibrary. If you work through 1 or 2 of those, you will get some understanding and experience of database design and related concepts. Access will not do anything you don't tell it to do.

Good luck.

[CarlettoFed]

You say you want to print labels by postal code, for example SCF Pasco 911 or Ephrata RR#2, but these codes are not in the database.
The ShipZip field, which should coincide with the Postal Code, contains only numeric values, such as 98942 or 98901.
You should be explaining yourself better.

[davegri]

Attachment 40842
See attached.
Since all the subscribers are in a single file now, all the update queries that operated on the route files are unnecessary. I deleted a couple dozen of them.
Same for the report label files. All had the same format. I deleted all of them and replaced it with one report label. I modify the criteria for each route to get the correct recordsource for that label.
Added forms for everything. No need to go to tables or queries for editing data. Added date range on frmBilling to specify the expiration date spread for billing.
I think this does about 95% if what you need, unless I missed the mark.