Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Large Database Dilema


  • Please log in to reply
4 replies to this topic

#1 Sannunziata

Sannunziata

  • Members
  • 7 posts
  • OFFLINE
  •  
  • Local time:11:38 AM

Posted 24 September 2006 - 11:21 PM

I have volunteered to work on an Access database. It is mostly names and addresses and six years of tracking donations made to this charity. It is one big glob. My job will be to separate it by the State in the US. I will therefore have 50 states, plus Canada and an International catagory. I will also be making up a section for the donations which have been made during the past six years, from these names and addresses.

After the database is separated by state, it will be distributed to a group of volunteers to manage their names and addresses in the states in their jurisdiction. Most of the volunteers can't use Access, so I am thinking that the names and addresses will have to be given to them in the form of a report where the names and addressses will be given to them in label form in alphabetical order.

I have begun to create queries from the main table [the one big glob], for each State in the US. What is happening here is that the database is getting very heavy. It started out at 6 MB and it has jumped to 31 MB already {I have only just begun!}. There are almost 16,000 names and addresses in this database.

Questions:
Am I going in the right direction with this or is there another way?
Should I be creating tables for each State in the US [instead of queries] thereby breaking up the main table that was originally given to me?
What about making 50+ separate databases, one for each state in the US and then linking them together?

My objective is to break up this big database since names and addresses are being continually added.

Any help will be truly appreciated. Thanks.

BC AdBot (Login to Remove)

 


#2 jgweed

jgweed

  • Members
  • 28,473 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Chicago, Il.
  • Local time:11:38 AM

Posted 25 September 2006 - 11:00 AM

I am not sure how adding one field (State ID) to the database could by itself increase the db that much, even with 16K records.

You may be able to transfer the individual state dbs to Excel, which presumably more people could use once they have it.

I would certanly attempt to maintain a single db, rather than over 50.

Regards,
John
Whereof one cannot speak, thereof one should be silent.

#3 Tsubota

Tsubota

  • Members
  • 3 posts
  • OFFLINE
  •  
  • Local time:09:38 AM

Posted 25 September 2006 - 11:41 AM

I would leave the database as is because it is not really that big. If you create excel spreadsheets and use MSQuery you can filter what that spreadsheet contains to the specific states needed by those volunteers. Or you can create select queries in the actual database. Now use Excel to create spreadsheets for each of the queries (selected by states of course). Use the Data, Import External Data, New Database Query to get the data from the database. Either way actually uses MSQuery but if you just link to an actual query in the database, modifications later are much easier because you can do them in Access instead of MSQuery.

#4 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,574 posts
  • ONLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:12:38 PM

Posted 25 September 2006 - 12:26 PM

I gather this is a one table deal, no relations. If so, the way I would approach it, is write a simple query right in Access, with State as a parameter and put out small, state-related, reports in text format. Whoever is running a report will be asked for what state. An even simpler solution would be to just sort the existing table by State and then by last name of the donors, right on the grid view of the table. Then filter by state, select the records, paste into text file, Excel, Word, whatever is convenient. Close to what Tsubota said. Things you can do in Excel so easily, can, for the most part be done right in Access as well.

But, if this is a real database, has links to other tables, you may need to make composite records containing fields from releated records, and then from the results of that query do the same steps as above with the exception of sort which can be done right on the query request.

An issue not addressed: is this a one-shot deal, in which case the above answers should do the job. Because if not, if a central database needs to be maintained while each of the 50 states do their own thing, then a major effort of unification of all the updates may need to take place at some point. The db is fairly small at this time, so planning now might be advisable.

Secondly, I'm thinking of donations. If there are multiple donations per person, is a single table going to work? I think not. I think a new table is required for donations, linked to the people table by some donorID you can invent now (sequential numbers automatically given by Access are the simplest to use).

#5 Sannunziata

Sannunziata
  • Topic Starter

  • Members
  • 7 posts
  • OFFLINE
  •  
  • Local time:11:38 AM

Posted 25 September 2006 - 04:20 PM

This database is a one-table-database right now.

I started making up queries for each state, just simply putting in the name and address info. After about 10 queries, I noticed that the size of the database got larger.

Perhaps, as you say, it would be better to sort the table by state and donor last name and then create a report for each state and distribute to each volunteer. Most of the volunteers don't have Access or Excel experience so a simple report would have to do for them. I will go from table to report rather than creating queries first.

I will also remove the donor info to another table.

I will be maintaining the central database and every few months, all of the volunteers will be getting new reports for the states that they have volunteered to take care of.

Thanks so much for your response.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users