Hello,

My current task using Microsoft Access 2007, a program that I have never used, is to create 2 tables, (currently named MQL and DNA (will stay as acronyms for sensitivity reasons)), and then once certain criteria is met, (criteria being a certain situation, nothing to actually do with numbers or orders placed), is to move a record, (name and other information), from 'MQL' to 'DNA' while deleting that record from 'MQL, whilst generating an object that appears as a letter that will be filled in with the information found in the moved record. I know it sounds complicated, but it looks really simple in my head, I just don't know what I am really doing, so it may be much more difficult than it sounds. The only reason I am using Access and trying to accomplish this is because my supervisor wants it done this way, and to make it easy for any user to generate this task of moving someone from one roster to another while generating a letter with a click of a button; I am guessing it is out of pure laziness, but I have learned not to ask too many questions in my line of work.

Basically the process I have in my head is this:

1. MQL Roster has 200 names, it is pretty much a constant with a little editing here and there.

2. Something happens, and an individual needs to be moved from the MQL Roster to the DNA Roster, (which contains less than 5 names usually).

3. I bring up the Database and the Switchboard greets me. I click on the 'Start DNA procedure' button.

4. That button brings me to a SOMETHING where I can search for the individual's name.

5. Once I find that individual's record, I click another button labeled 'DNA.'

6. When I click it, the record is moved, (as in copies, then deletes the original), from the 'MQL Roster' to the 'DNA Roster', and it generates an object that appears to be a letter, where there is a main document already stored, and this process fills out the specific information. (I have found that the 'Mail Merge' function used with Microsoft Word works well, but I need it to generate only one letter that is printable. It doesn't have to be an actual letter, just something containing the main document information, that fills out the specific fields labeled and makes it printable.)

Again, my supervisor wants all of 'step 6' to happen with virtually one click of a button, or at least something that is incredibly easy to use to anyone without any knowledge of Access.

Sorry for the length, but I have been trying for days and days to figure out how to make this work, but I do not have any knowledge of programming, which may be the only way to make this work... if it IS even possible. Thank you for your time and I appreciate any help you can offer!

Hi,

(IMG:style_emoticons/default/welcome2UA.gif)

If you want to use a database for whatever it is you're tracking, I would like to let you know that it would be better if you follow proper database design principles. Database applications are a little different than flat file systems like Word documents or Excel spreadsheets, and it is always better in the long run if you use a database as a "database."

For example, there is rarely a need to have two separate tables containing the same types of information. The customer doesn't need to know that you're not using two tables to separate the names, you can just make it look like that.

Here's how:

1. Create just one table to store both MQL and DNA names and other data fields
2. Add one additional field/column to store whether that name/record belongs to MQL or DNA

You can then use queries to separate the two types of records. You can use your query for your forms or reports.

And speaking of reports, you can create one based on your query that you can use as your "Letter."

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Thank you for the input, I will definitely take that into account!

Now that brings up another question of how to have the buttons on the switchboard bring up said information, and then generate a report that looks good when printed. Also, the names DEFINITELY need to be on seperate reports for logistics reasons. We print out hard copies of the information as it is not that lengthy; we do not want the 'MQL Roster' to contain the names on the 'DNA Roster' on the hard copies. Does this make sense? You have been a great help, but I still don't really know how to do any of what you're saying beyond making the table.

I am using the Switchboard as it appears to be the most 'intuitive' design for any user to be able to use with ease without any prior knowledge of Access.

Hi,

Reports can display any set of data you want. Like I said, even if all the data in the database is in one table, you can print 100 reports all containing different things if you were able to identify something in the table data what makes them different from each other. In your case, that would be the field I suggested to indicate if the name in the table belongs to the MQL or DNA group.

The Switchboard is a good and simple way to provide a user interface but you will need supporting objects such as forms and reports first.

We recommend using forms for all data entries and reports for any "hard-copy" requirements. Queries are used to dissect the data that will be used in your forms and report. It is good practice to hide the tables and queries from the user. Let them do all their work in forms (or reports).

Take a look at Crystal's Access Basics tutorial to give you some fundamental backgrounds on the Access objects I mentioned above.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Understood, so then, having all of the information on one table is the best way to accomplish this.



I put a new field labeled 'DNA' and it is a yes/no field, if they are checked, it would be YES they are DNA.



How would I make a query and then a report to sort the records that are MQL and the ones that are DNA into two different reports that are printable? Also, where would I put the command button that would make a seperate report for each individual DNA that looks like the letter I need printed?



I don't know how to implement any of these things, by the way, whether it be SQL or VBA, so I don't know where to copy and paste the code at, what to replace with my particular table/query names.. I really am a beginner. =/

Create a Query, using Query Wizard.
Just follow the Wizard.

For all MQL put a Criteria on DNA field (Criteria: False)
For all DNA put a Criteria on DNA field (Criteria: True)

How do I define the criteria?

Hi,


That would work. But, if you want to go a step further, what happens if later on your company changes its business rules? What if they decide to add another group or category?

Having a Yes/No field will not work if you end up dealing with more than two categories.

What I would recommend is use a "Number" field (Long Integer). For now, a value of 1 could mean "MQL," and a value of 2 could mean "DNA." Set the Default Value property to 1.

Later on, you could create a separate table for the groups or categories. If you do, make sure the first record is MQL, and the second one is DNA.

If users are supposed to be able to select the group, then you can use a Combobox on your forms.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Update: I have dumbed down the system to only one table, and instead of using the yes/no check box field, I'm using general text field labeling each record as either a 'YES' or 'NO', (mostly No), under the 'DNA?' Field. I also figured out the queries enough to make 2 seperate ones that will display only the MQL names and DNA names respectively. (I couldn't have done it without you guys, thanks a lot.)

The task I have now is to generate a report that looks like a stock letter, except the only things that change from person to person is the actual person's name and title. I want to do something similar to the Mail Merge system, except I am only generating one 'letter' instead of hundreds.

The idea is that I use the switchboard to open a Form made for the DNA Roster; upon the opening of that, the user will search through the Roster, (only a handful of names, so I decided to take out the combo box search thing), and once they find the record they want, they click a command button labeled "Generate DNA Letter." That command button will then open a report that looks like an actual letter, and automatically fill in the fields of 'Name (Last, First)' and 'Title,' in their respective places. Upon completion of that, the report should be ready for printing; however, I ran into a problem as I tried to make a report by taking a screen shot of the letter itself, make it into an image, then impose the image on the report. It turned out okay... but it printed like 30 copies, so I definitely don't want that, (I would catch so much heat as it isn't my paper haha).

I found a button called "Add Existing Fields," and it seemed to be just what I needed, except that I can't figure out how to link the fields to the record's fields I am looking at on the current form when the "Generate DNA Letter" command button is clicked, it always links it to the first name on my table.

Do you think there is a better way to make the report look like the letter I need, if so, how? And is there any advice you can give on how to make the specific information appear in the right places as with the Mail Merge system?

You can make the report look like your letter by just adding labels and textboxes in the right places in your report. To add the names from the table, you can base the report to the table or query and just add the fields from the table to the report.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)