Updating the date of an event in a table 1

[Kallen]

Hi all, I will try and explain this as best I can so bear with me.

I have a database I am creating. I have to create this database that shows monthly acitivity on our clients. Each month I get an Excel spreadsheet data dump that shows activity of our clients. It does not keep any other prior history just that prior month. I created this database so I can keep a history of all activity for all months for our clients. I am doing well with the design but have a dilema I need help with.

What I am thinking of doing is creating an append query that will append all of the new activity for that particular month into an activity table I created. My dilema is that in my activity table, I have put in a date field. That is the date the activity occured. Since the date field is not on the monthly spreadsheet, I need to tell Access the date that this activity occured. It would always be the end of a month so I don't have to worry if something happened Feb 2 or Feb 3, it would always be Feb 28th. or the end of another month.

Is there anything I can do in a query or other method that would automatically put in the month for me.

If I have not made myself clear and you need more info please let me know.

Thanks!

 

[beetee]

you surely can

simply say something like

SELECT ActivityTable.*, #3/31/2003# AS PerEnd
FROM ActivityTable;

You can easily make an append query from this.

 

[Kallen]

I am still confused. I tried to build this into my query. Under Properties, Build

SELECT [CritOblgBal], #1/31/03# AS Date
FROM [CritOblgBal]

I keep getting an error telling me that the syntax of the subquery was incorrect. It then says to check the subquery's syntax and and enclose the subquery in parantheses.

What am I doing wrong?

Thanks

 

[beetee]

It's possible the 'as date' statement may be giving you trouble; 'date' is a reserved word.

Additionally, you seem to have the same name in your query for a table and a field, which seems odd.

You may want to use the query design tool to make your query.

Then, paste this into the first empty column:
PerEnd:#03/31/03# (or whatever date you need)

HTH

 

[Kallen]

I made my own query this morning. I just did Filedate: #01/31/2003#. The only thing I need to do is make sure I change the date field the end of each month and then append it to my table. If only everything else in Access could be that simple. )

 

[beetee]

To make it a bit more generic, you can do FileDate:[Enter a date:] and the query will prompt you for an ending date.

 

[Kallen]

I didn't even think of that. That will work as a perfect reminder to me to make sure I change the date! I always have a backup of the table just in case something goes wrong, but I would rather be safe than sorry. One of the things I did was make my customer number a primary key and my file date. I am somewhat new to two primary keys in a table, but thought that this would prevent me from putting in duplicate info for the same customer number and same month. Does anyone know if this method works. I will be doing this type of stuff in many tables.

Thanks.

 

[beetee]

I think what you mean is two fields in the primary key, which is perfectly acceptable (there can only be one 'primary' key in a table [although you can specify multiple 'unique' keys]).

Using a unique index is definitely the way to ensure you won't create duplicate records.