Novice Re-organizing a database/query issues (1 Viewer)

[courageouscat]

Re-organizing a database/query issues

Hi there everyone. I am a novice working in Access 2007, with a lot of interest and a basic knowledge of how databases work. I work for an educational organization and I have an old database that has Customer Information (contact info such as name, address, etc.), Subscription Information (where the customer purchases written publications on an annual basis, and can subscribe to many publications) and Mailing Lists (Based on Commodities that the Customers have; they can be on many Mailing Lists and my original Mailing Lists table had sub-categories). I was restricted in my queries due to permissions granted by the original owner, who is no longer employed at the organization, to queries on queries because I could not get permission to access the actual Tables in the front-end of a split database. I have collected the data (via query) from the old database (into several excel worksheets, which was only option that worked based on it’s size and my permissions) and then imported the data into tables into a new database.

Here are two of the current tables I am having the most problem with:

Customer Table
Tbl_Customers
-PK CustomerID (When I imported I had access choose this)
-MasterID (which appears to be the PK in the original Customers table, and is used as a FK in the other two tables)
-Enroll Letter (Key for only me to use to distinguish similar names)
-Company Name
-Other contact information

Original Mailing List Table:
(There are about 60 mailing lists, and many of them have at least 3 subcategories.)
Tbl_MailingLists
-MailingListID (Original PK, but not designated as such when I imported
-MasterID
-MailingList (the name of the category of the commodity they have)
-MLSubCat (name of the commodity they have
-StartDate
-EndDate
-DateEntered
-MLNotes

I need to be able to make a query that I can run “on request” that lists specific Customers, their contact info, and the Mailing lists that they are on. I prefer to view this information in datasheet view for further sorting as necessary, and for exporting to excel or printing for others to review. I would like to have the query to show the customer info in columns, and the mailing lists they are on as columns with a true or false as their return. I tried a few different ways, using crosstab queries, but I am having serious problems. The categories (Mailing Lists) and commodities (MLSubCat) in the above table were not organized efficiently and not working for me, so I queried them, and did a Make Table queries.(The last four columns are in the above table did not seem important so I did not bring them through to the new tables, but the original table remains intact)

Here are some of the new tables:

Tbl_Residential
-MailingListID
-MasterID
-MailingList

Tbl_Commercial
-MailingListID
-MasterID
-MailingList (the name of the category of the commodity they have, such as “vegetables”)
-MLSubCat (name of the commodity they have, such as “lettuce”)

Tbl_Volunteer
-MailingListID
-MasterID
-MailingList (What area they volunteer in, such as “ABC Department”)
-MLSubCat (what they do when they volunteer, such as “support”, “events”, etc.)

I am still having problems (mostly with syntax errors and type mismatch in expression) and I think I may need to start over. Any suggestions will be greatly appreciated. I am mostly working on this project after work hours, so immediate responses to questions may be difficult and I apologize in advance. Thank you. Cathy

 

[DavidAtWork]

I think for your "query on demand" to pick up all customers and all the true/false's as to which mailing lists they're on, you need to create a base shell table with all the fields you require including a Yes/No for each mailing list type you'd like to query.
It may then be easier to run a series of queries, one for each type of mailing list:
run an update query to update the Yes/No for that mailing list if the customer already exists in the table and an append query to pick up any new customers that didn't exist up to that point, if you follow
Repeat this 2 step query for the other type of mailing list.
Depending on whether you want to create a fresh table of data each time, you may need to clear the base table before you start. You may want to tie all these queries together by including them either in a macro or some vba which can be run on a button click event.

For your other problems, you may need to check all your table data types as you may be trying to join on id's that have different data types

David