Is it possible to use a form to view a table, and then post that information to another table?

[Access_Novice]

Suppose the first table is called ITDActivity (ITD = Inception to Date). This table holds timesheet information, e.g. employee names, timesheet dates, hours worked, project and task names, etc.


The second table is called Billed. This table has the same fields as the ITDActivity table, plus one additional field called PostedOn (a date/time field).

Here is what I want to do.

1. An Excel file would be imported into the ITDActivity table.
2. I would then open a form which is bound to the ITDActivity table. The form would have a feature where I can filter for dates (about 2 weeks at a time). Only timesheets from the selected date range would now be displayed on the form.
3. The form would have a button called "Post."
4. When I click the "Post" button I want the filtered timesheet records to be uploaded to the Billed table.
5. When the form information is posted to the Billed table, the field called PostedOn would update with the date and time the records were posted/added to the Billed table.
6. After posting the form records to the Billed table, the data in the ITDActivity table should not be altered in anyway, although the form is still bound to the ITDActivity table.


Is it possible to do all of this?

[orange]

What is the ultimate purpose of the database? Are you going to work in both Excel and Access?
Somewhere you must have a list (or table) of Employees, Projects, Tasks, along with your ITDActivity table.

It all seems possible, but would need real context to determine what options might be available for longer term.

[June7]

Are ITDActivity records actually imported or just linked? If imported, why would the Billed table have the same fields? This is duplicating data. Why even have Billed table and not just have the Posted field in ITDActivity?

[Access_Novice]

What is the ultimate purpose of the database? Are you going to work in both Excel and Access?
Somewhere you must have a list (or table) of Employees, Projects, Tasks, along with your ITDActivity table.

It all seems possible, but would need real context to determine what options might be available for longer term.

To all,

Here is what I had in mind. I would run a timesheet history and dump that into Excel. That Excel sheet would be imported to the Access table called ITD. Then the user would select the most recent two weeks to bill and pull that up in a form. Then mark that as billed and send those records to the billed table with a timestamp to record when the billing took place.

Now you might think why send the ITD data to the Billed table? Because two weeks later, I would run another ITD (inception to date report) which includes from the beginning hence ITD. This ITD file would be uploaded and replace the one currently in Access. It would overwrite it. So the ITD file has the last two weeks of timesheet data, plus the prior two weeks (which was billed). The reason why I would query a timesheet history from inception to date is because I have to take into account the possibility of timesheet corrections to previously billed weeks.

After finding out how to use a form to pull data from the ITD table, then upload that to the Billing table, the next step would be trying to figure out a way to take all the records in the Billed table, and compare them to the records in the ITD tab to check for any discrepancies. Discrepancies would indicate timesheet corrections to previous periods there were made since the prior two weeks were billed. Doing this comparison is another challenge. If there are any discrepancies, I wanted to append those to the records in the form that extracted the most recent two weeks from ITD, so that the form would have timesheet data for the last two weeks, plus any corrections to previous periods which have already been billed. Then take all of these records in the form, and upload those to the billed table.

In short, I am trying to design a system which would import timesheet data and bill that data. I would have to keep track of what has already been billed. I would also have to consider the possibility that since a prior billing, somebody might have a timesheet correction to a date that had already been billed. I would have to check for those retroactive corrections so that they can be included in the current billing.