appending records using start date end date

[mrrayj60]

Let me see if I can explain my need. I've been adding a recording to a fines table one record at a time. I enter the fine date (txtfinedate) on the form and an amount (txtfine1) and the append qry copies this data to the fines table. It works ok, what I would like to do is add each record if I use a date range. This way if fines should have been posted each day but they didnt enter them I can catch up with a bunch. The fines say are from 1/1/10 to 1/6/10 so hopefully it will add 7 recorded fines. I would need a date prompt but not sure how to get it to append multi records with the seperate dates.

This does one record ok, any thoughts would be helpfull. access 2003.
Thanks Ray

INSERT INTO vrcfines ( finedate, ubl, Name, [vrccase#], complaintid, complaint, fineamnt, posted )
SELECT forms!vrcedit!txtfinedate AS Expr1, forms!vrcedit!txtubl AS Expr2, Forms!vrcedit!NAME AS Expr3, Forms!vrcedit!txtvrccase AS Expr4, Forms!vrcedit!txtcomplaintid AS Expr5, Forms!vrcedit!txtcomplaint AS Expr6, Forms!vrcedit!txtfine AS Expr7, False AS Expr8;

 

[vbaInet]

Use the WHERE clause in the INSERT statement specifying the date paramters.

 

[mrrayj60]

Use the WHERE clause in the INSERT statement specifying the date paramters.

Hi,
I have not had any luck geting it to append more than one record.
I want to add a record between a date prompt, if I prompt 1/1/10 - 1/7/10 it would add 7 records to vrc fines. You said where clause but I cannot seem to get that to work. Thanks. Ray


INSERT INTO vrcfines ( complaintid, ubl, Name, [vrccase#], complaintid, complaint, fineamnt )
SELECT vrc.Complaintid, vrc.UBL, vrc.NAME, vrc.[VRCCase#], vrc.Complaintid, vrc.COMPLAINT, vrc.Fine
FROM vrc
WHERE (((vrc.Complaintid)=132));

 

[Brianwarnock]

I don't get this, if you are manually adding new records to your table then just use the input form for the Table.

Brian

 

[mrrayj60]

Hi Brian, Thanks
I am using a command button to add record(s) from vrc to vrcfine. I can get it to add a fine by date but only one date at a time. I want to append several records from the form to vrcfines over the past week where no fines we posted yet. When the command append qry button is pressed first prompt is [Begin Date] then the [End Date]...This would add say 3 or 6 or ten records based on the date range.

Ray

 

[vbaInet]

I think you want to append to certain IDs correct?

 

[mrrayj60]

I appending from a specific complaintid, the vrcfines is an accumlated table of all fines issued, linked to the resident by complainid & UBL(resident).

INSERT INTO vrcfines ( ubl, Name, [vrccase#], complaintid, complaint, fineamnt, finedate )
SELECT vrc.UBL, vrc.NAME, vrc.[VRCCase#], vrc.Complaintid, vrc.COMPLAINT, vrc.Fine, Date() AS Expr1
FROM vrc
WHERE (((vrc.Complaintid)=132) AND ((Date())>=[date]));

 

[mrrayj60]

Here is the table the second tab at the bottom is where the user runs the append qry...I hope this uploads, its small..Ray

 

[vbaInet]

Here is the table the second tab at the bottom is where the user runs the append qry...I hope this uploads, its small..Ray

Didn't upload. Browse for the file, click Upload and wait until you see the attachment, close the window, then Submit Reply.

 

[mrrayj60]

its 660 kb, so its small enough....still trying, get error

Database errorThe Access World Forums database has encountered a problem.Please try the following:

• Load the page again by clicking the Refresh button in your web browser.
• Open the www.access-programmers.co.uk home page, then try to open another page.
• Click the Back button to try another link.

The www.access-programmers.co.uk forum technical staff have been notified of the error, though you may contact them if the problem persists.
We apologise for any inconvenience.

 

[vbaInet]

Try zipping your file first before uploading.

 

[mrrayj60]

ok, zip worked...thanks

 

[Brianwarnock]

Can you talk me through the Logic here

AND ((Date())>=[date]));

[date] is a prompt so this appears to have no connection with the data in your database, or am I missing something.

Brian

 

[mrrayj60]

and just to note, I would not want to post a dupliate date, duplicate complaint id to vrcfines. In the process the user would see that fines are posted thru say 12/31/09 and would be making a decission to use 1/1/10 as his start date and say 1/7/10 as his end date, issuing 7 days of this fine.

 

[mrrayj60]

Can you talk me through the Logic here

AND ((Date())>=[date]));

[date] is a prompt so this appears to have no connection with the data in your database, or am I missing something.

Brian

That date will be the Finedate, if you enter 2/2/10 you should see a fine in vrcfines for $25. dated 2/2/10

 

[mrrayj60]

Can you talk me through the Logic here

AND ((Date())>=[date]));

[date] is a prompt so this appears to have no connection with the data in your database, or am I missing something.

Brian

**correction, thats me playing trying to get it to work...

 

[Brianwarnock]

You only have 1 record for id 132 in the vrc table

Brian

 

[mrrayj60]

This gets it to use the user input date as the finedate...sorry, been experimenting...

INSERT INTO vrcfines ( ubl, Name, [vrccase#], complaintid, complaint, fineamnt, finedate )
SELECT vrc.UBL, vrc.NAME, vrc.[VRCCase#], vrc.Complaintid, vrc.COMPLAINT, vrc.Fine, [date] AS Expr1
FROM vrc
WHERE (((vrc.Complaintid)=132));

 

[mrrayj60]

correct, removed all other records to shrink my table. That record would get 7 fines for the previous week if they didnt fix the complaint. So ignore for complaintid for clause, that would actually read the forms complaintid when all is said and done.

 

[vbaInet]

Just had a quick look. I'm finding it hard to understand how things function. By the way, your database is lacking normalization. Maybe you want to read up on that and sort that out first?