New Access project - (Importing data and reports)

[kacey8]

Hi guys,

Thanks to all your help on my previous project. that has now come to the final stages and is being rolled out.

Now I have another porject coming up I could use some advice on, first one I am unsure about but I need to make sure.

Question 1:
I will be importing data from Excel into the access DB. (easily done, done it before) However one of the columns on Excel is called "Letter 1". On the current Excel sheet the column has an entry on it eg "25/03/2013 (letter)" on the access DB I want to use a Yes/No tick box instead.

However as I will be importing around 12,000 entries I don't want to go through and mark what was originally a date as a tick. Is there anyway when I import the data that if the field has a data in it from excel it will mark the yes/no as a tick but if it is empty it is unticked? I will be doing this for around 8 different fields (Letter 1, 2, 3 and so on)

Question 2:
This is about reporting. I want to create a report which has three key features I have never used

1st feature the report will need to return entires of todays date - 7 days or 21 days or 3 months etc (and so on so basically (=26/06/2014 - 3 months)

2nd feature I want to do is once the report is run, is to create a mail merge for the entries on the report so we can print mailing labels.

3rd feature I wish to impliment that when the report is run that it marks a devined "Yes/No" box as true.

I haven't designed any of this yet so I can't provide field names but I really just wish to know if each of my options are possible to be done, then I can begin creating a proof of concept to test it.

 

[plog]

Question 1: When using imported data that isn't exactly what you want, its best to just straight import it and then build queries off of it to give you want you want. So, for this I would import your data and then use a query to determine if that field contains data (check out the IsNull function http://www.techonthenet.com/access/functions/advanced/isnull.php)

Question 2:
Feature 1: This too can be done with a query. You can use Date functions (http://www.techonthenet.com/access/functions/) in the criteria section of a query to limit your data to just those in the timeframe you want.

Feature 2: You should be able to use the same query in Feature 1 to feed a mail merge. Google "access query to mail merge" for info on that.

Feature 3: This will require another table. You won't mark every record in your main table, you would make an entry in this table with the data ranges you have used. Then you can use it to determine which main records have been processed.

Everything you want is possible with Access, its just going to be a learning experience.

 

[kacey8]

Thank you very much,

Question 1 I was thinking about and I was thinking the way to do it would be have a new field for the YES/NO which then marked it as "Yes" if the original field "is not null"

Thanks. I will start to investigate, to be honest I know it's a big learning curve but I have been given a good amount of time to do this.

 

[plog]

No, you don't store redundant data. You should use a query and the IsNull function to determine if a field is Null. That's not something you store in a field in a table.

 

[kacey8]

Okay, So I am kind of lost.

So Here is an example of the data I imported. What I want to do is change the field to a Yes/No (instead of the short text) and keep the fields that are null as no and the ones with data as yes.

So what sort of query would I need to use then?

 

[plog]

The type of query would be a SELECT query. Inside that query you would have a calculated field using the IsNull function. Try this query:

SELECT [Letter 1] AS Letter1, IsNull([Letter 1]) AS Letter1Null
FROM YourTableNameHere;

Replace 'YourTableNameHere' with the name of your table.

 

[kacey8]

Cheers, so that works.... except it is the wrong way round, the Nulls are marked as -1 (which is Yes I believe) and the Not Null is marked as 0 (which is No I believe)

Now, it produces the results in a new column which isn't on my table, How do I save that column into the table? (if that makes sense)

What I am thinking is, I can save the field it created with the -1 and 0's then it's changed over, delete the query and the original field (as I'll never need to run it again) as Data will never be imported again)

I don't want the old data from Letter1 left, it is old data which is no longer needed, I just need to mark it as ticked so we know we sent the letter.

 

[plog]

Now, it produces the results in a new column which isn't on my table, How do I save that column into the table? (if that makes sense)

That questions makes sense from an English standpoint, but not in the conversation we have been having. In my first post I responded to all your issues and in it I explained why you shouldn't do that. The second post I made dealt exclusively with that. My 3rd post explained how you achieve what you want without storing that data. And now here we are on my fourth post where I am explaining how I explained that to you.

No, you don't store redundant data. You should use a query and the IsNull function to determine if a field is Null. That's not something you store in a field in a table.

Now for your last sentence about marking when you sent the letter. I explained that in my first post as well. Since you will essentially be processing them by date, you should create a new table where you track the dates you have used. You don't mark each individual record as sent, you create a record in another table to show which dates you have processed.

 

[kacey8]

Plog, I appreciate your paitence in the matter, and I will try to clear this up as best as possible.

I don't wish to keep the data in letter1, the dates or anything, if I could I'd replace all records with data in Letter 1 as -1 and all empty records with 0, then change the field to yes/no then they would appear as a tick.

I do not wish to keep the original data, I want it gone.

 

[kacey8]

EDIT: Question 1 is now fixed/sorted. Thank you.

Moving on to my second issue. This I hope is more simple. so it is a Time delayed query to run a report returning records with a date of so many days less than todays date.

I am looking at the link you provided. I have an idea of the query but have no idea on the syntax I would need.

I am thinking the DateAdd would work..

something like this example

DateAdd ("m", -3, #Date()#)

I have no clue but would that work to display todays date - 3 months?

 

[plog]

The idea is sound, I don't know if the syntax is correct (I don't think you need the #'s). Test it and see what you get.

 

[kacey8]

Thanks. Will try and see what I get. the #'s were part of the code from the suggestion of the website you posted for "DateAdd".

For Feature 3, you said you I would need a different table, could I not use a query to do the following in one go.

• Select records using the Date Add above DateAdd ("m", -3, #Date()#)
• Make mail merge with said records
• update field "Letter 3" for the selected records from 0 to -1

This would save using a seperate table no?

 

[plog]

You would need both a select an an update query. Proper structure would be that you use a seperate table to hold the dates of the records you process.

 

[kacey8]

Do you mean use a seperate table to process them, then move them back to the original table?

The reason I say this, because if you mean having a "Main" table, then having a table for "Letter 1" (once letter one has been sent) I'd have to do the same for all the other letters (up to around 50) if it is a case of selecting the data, moving it to a seperate table, doing the work in the seperate table and then moving it back to the original table, well that is different.

I am also wondering if it would be simpler than to rather update the records once the report is run, but to have a seperate update button next to the report button?

So I'd have

"Letter 1 Report" (Which loads all the letter 1's (which are sent out to people who were entered 7 days prior)
"Update Letter 1" (This one would be used to just update the files and mark letter 1 as "Yes")

Would that be easier than merging the two together into one?

 

[kacey8]

EDIT TO DELETE = ME BEING A MORON.

Still trying to work out the update formula

 

[kacey8]

I got the update one working though. thanks.

Okay so I am going to stop using this thread as it is asking for different things (some reports, some queries etc) so I will create a thread for each item.

Thanks