New person here AND with Access

[Coytee]

Hi, I'm not sure where to post this so if it's wrong to post here, perhaps move it or let me know and I'll copy it where directed.

I'm know VERY little about access. I've dabbled with it over the years but frankly, I'd say my level of sophisitication with it is about the same as you & I playing the card game "War" verses Bridge with each other.

Here's the gist of my situation.

I've downloaded 5,000 names/addresses for a mailing I'm going to do. I have them in a table. I have an additional field that I created (actually about 10) with "date 1" (and 2,3,4,5) as well as "postcard 1" (and 2,3,4,5) to give me 5 dates and 5 text boxes to write in what I sent.



Here's what I did to create the mailing list and then the problem I'm trying to over come.

The names/addresses were downloaded via CSV format. I wanted to add the date/postcard info so I tried to do a copy/paste after I filled one out. It seems I could only paste to a single field at a time.

Oh...I forgot to mention.... I split the 5,000 names into two 2,500 groups and have only processed one group for a mailing.


Here's what I now have:

One (contains ALL data) table with 5,000 names in it and my date/postcard fields are blank

One (Call this one 'mailed') table with 2,500 names in it (date/postcard fields also blank) and this table has been processed for the mailing

One (Call this one 'blank') more table with the OTHER 2,500 names in it and nothing has been done to this table.

What I'd like to do somehow: Put yesterday's date into ALL the fields on the "mailed" table and then copy that data into the MAIN table (the one containing all 5,000 names). I do not want to duplicate the data, I'd rather over-write the information such that, I'm actually simply updating the 'date' and 'postcard' fields to reflect what I've done.

Next month, when I process the other 2,500 names, I want to update the main table with that data as well so that the main table is always accurate and I don't have to look at the two smaller tables.


I did this split by copying into Excel and for mailing purposes, deleted the columns that the post card people did not need (county, country, phone number.....)

I know I have not been too efficient with what I've done but, I was finally able to hammer the data into the format needed for the problem at hand (the mailing). Now, I'm trying to revisit this problem so I can make the data better suited for my use here.

Anyone mind trying to guide me on how to copy data into that date field without having to enter it or hit paste 2,500 times?

Thanks for any thoughts.

Richard

[pbaldy]

I don't like having the same data in two places; it will be much harder to maintain that way. One solution is to have a "group" field in the one table. 2500 have "A" or 1 or whatever, 2500 have "B" or 2 or whatever.

I also don't like the idea of a field for each mailing. A normalized structure would be to have a "mailings" table of some sort, which would store the date of each mailing (one record per mailing, not one field).

In any case, the way to update the date field is with an update query:

UPDATE TableName
SET FieldName = Date()

Optionally adding a criteria

UPDATE TableName
SET FieldName = Date()
WHERE GroupFieldName = "A"

[Coytee]

Thanks for the comments. I'm trying to digest them, slowly!

I agree with you 100% on not wanting data in two places, that is what prompted me to ask this.

I'm not sure I understand this part "I also don't like the idea of a field for each mailing. A normalized structure would be to have a "mailings" table of some sort, which would store the date of each mailing (one record per mailing, not one field)."

I do not yet know if I described what I was doing very well or if I just don't understand your response (because of my ignorance)

I want to be able to have a form where I can try to organize each record and have it make sense. On this form will be their personal info (from the mailing) as well as phone number, spouse.... it's in this location that I want to ALSO have visible to me, a field where I can see the date that I might have sent them something and what it was that I sent.

This way, I will have all pieces of data, relative to this specific person, on the same page in the form.

Does that cause you to alter that comment? (if so then that infers I described it poorly the first time and if not means I am lost on your response)

The key for me is wanting the date/mailing info visible to me in the form. The only way I know how to do that is make a field for it in the table.

Evidently I'm missing something (which is no surprise! )

Pending a comment back, I'm going to look at your suggestions and see if I can make sense out of them.

Thanks!!

[pbaldy]

It doesn't really change anything for me (which may mean I'm just dense). Think of it like an invoice. A normalized structure for a sales database would include 2 tables for invoices. One would include all the basic data about the sale, such as invoice number, customer, date, etc. The other would contain the items sold, where each item was a separate record (row) in that table. Fields might include a product code, quantity and price. You would not want to have a field for each product because of the varying number that might be sold on a single invoice. Any time we see fields with numbers in them (Mailing1, Mailing2...), it's a warning sign of this type of issue.

I equate the invoice header with your names and addresses table, and the invoice details with the mailings. I would use a form and subform to view the data. The form would contain the person's data, and the subform any mailings related to that person. I would structure that mailings table differently, depending on whether you emailed people individually or only as part of the big groups.

[Coytee]

Wow, this program just blows me away. I'm not a programmer and I did not stay at a Holiday Inn last night.

I'll tell you another thing I want to do (so you can laugh at me ) and I'll reflect if I should even be using this program since I know so little about how to use it at all, which isn't to even mention, using it effectively!

I want to also have a text box, 255 spaces, so I can type in some notes if need be. Something about a phone conversation....

You know.... maybe it would make my use more clear if I said I was using it as a contact manager??

Perhaps it's the wrong tool for my job.

I just now thought I found a cute way to fix the 'enter date' problem.... I copied my 5,000 names to excel. Went to the date column and the 'item sent' column as well... copied the date and 'postcard' in those columns. I then saved that as a CSV. After deleting the 5,000 names in my main table, I was going to simply import this file again with the data added. Although this is NOT the efficient way to do it....I thought I was cute and could get by with it.

Until I got a "field 51 doesn't exist" error. Interestingly, I also got another error, I think it said "SYL" (not syql or sylq what ever that is, but something slightly different)

gack....

I know to you guys that this is easy stuff and I'm wallowing around, tripping over my own ignorance.

Many thanks to you, especially in light of the reality that I know so little about this program.

[Coytee]

I just reproduced that SYL error. It is actually happening in Excel (my apologies for thinking it was happening in Access)

I can save my Excel via CSV format but....when I go to open that same file in Excel, I get an error "SYLK: file format is not valid"

sigh.... if not one thing it's another.

[pbaldy]

LOL at the Holiday Inn bit. Why don't you just invest in a night there, then you'll be able to knock this project out in no time!

I think Access would be a good tool for the job, and I know others have created contact management applications with it. The thing about Access is that it really pays to invest time ahead of time in the table design. Table design for a relational database is fundamentally different than how you might lay it out in Excel.

For the second table, if you said you were only going to send out group emails, I might have fields for the group, the date, and your text "notes" field. So when you send out a bulk email to everyone in group "A", I put a record in that table with the appropriate data. When I'm viewing a person in group A, a subform displays all emails that have gone to group A.

If you said you wanted to track individual emails, I might design that table with a field for the contact instead of the group. Or you could even do both. In any case, the theory is to have a related table for this information rather than extra fields in the main table. Thus the term "relational" database.

The key first step is to identify all the information you're going to want to keep in the database, then lay out tables to hold that information based on what is called "normalization".

[Coytee]

I think Access would be a good tool for the job, and I know others have created contact management applications with it. The thing about Access is that it really pays to invest time ahead of time in the table design. Table design for a relational database is fundamentally different than how you might lay it out in Excel.

For the second table, if you said you were only going to send out group emails, I might have fields for the group, the date, and your text "notes" field. So when you send out a bulk email to everyone in group "A", I put a record in that table with the appropriate data. When I'm viewing a person in group A, a subform displays all emails that have gone to group A.

Ok... as a preface, please remember that in order for someone to ask a question intelligently, they must (in my opinion) have a grasp of at least, part of the answer. If in fact they know ZERO of the answer then they (me) run the risk of offending others with their brilliant display of ignorance.


So.... my light bulb is not going off. However, I'm wondering if you just helped tighten it into the socket another quarter turn.

Question: Regarding your comment of how important it is to design the table. All I've done is think "what all will I want to know" and put a field for that in the table. This includes information on the mailing.

I'm now wondering if a seperate table, perhaps with the individual mailings detailed or maybe a table for each mailing (??) is what I'm missing here.

Should my main contact name table ONLY consist of their critical information (name/address/phone/single daughters names) and I should create other tables for other information?

I remember the very first time I was exposed to Excel. (of which I'm self taught I might add). I had NO idea how to create a formula or anything like that. I just saw a bunch of squares in front of me and scratched my head thinking "now what".

Today, I can work just fine in Excel. Having never really worked with Access, (and believing it to be a more difficult program to learn) I'm really in the same boat of being overwelmed with "now what".

(we need a Homer Simpson scratching his butt type of smilie so I could insert it here)

[pbaldy]

That's what I've been saying, yes. You do NOT want a separate table for each mailing, though you may want one for each type (group vs individual). Attached is a very quick and dirty picture of what I'm thinking.