Link to home
Create AccountLog in
Avatar of darantares
darantaresFlag for United States of America

asked on

Ideas about copying one table to another in Access 2010

I know there is quite a bit of stuff out there on copying data from one table to another in access, but I'm asking here to see if there's a better solution.

I have a database that's normalized, and I know the rule about storing data in more than one place, hence the reason for the question.

I have a patient tracking database (attached) and when I create a Progress report, I want to import the data from the Initial_Eval table and have that editable (but not to overwrite what's already on that Initial_Eval table under that date), as well as have two other fields in the Progress_note table for Progress notes. I haven't generated the Progress report yet, but I'll be building a form and I wanted to get the table thing figured out before I make that form. It'll look a lot like the Initial Eval form.

My first thought was to have a button on the Progress note page that would copy the data from the Initial_eval table on the selected date into the Progress_note table, let the provider change what they want and then print. Then that progress note is saved and can be printed later, while the data in the Initial_eval table remains unchanged. Is this the right way to do it?

I think I can figure out how to copy the data from one table to another in VBA (but if anyone wants to share code, feel free). My other question is on the Progress page, how would I allow the provider to select which  Initial_eval to select (for example, if they came in one year for a hip, but now they are here for a shoulder). Is it possible to have a drop down box with just the dates the Initial Evals were done, the provider selects that then hits 'Create Progress note'? Ideas here would be awesome too.
Avatar of Anthony Berenguel
Anthony Berenguel
Flag of United States of America image

I'm looking at the database and the forms and I have to ask: who are the users of this application? How savvy are they with Access? How worried are you that they're going to get into the tables, or queries, or other objects and alter them?
Avatar of darantares


I've been working directly with the three folks who will be using it, and I'm not worried at all about them getting in there and doing things they shouldn't be. To be honest, they aren't savvy at all with Access, but right now they're using an entirely paper based system and want to push a button or fill in a blank rather than having to copy stuff they've already written down countless times before.

Hope that answers your question.
Thanks! That does answer my question. The reason I asked was because I was trying to gage how much control you want to put on the forms in regards to creating, updating, and deleting records.

As I said earlier I took a peek at your database. I am going to (as fast as I can in my spare time) give you an example as to how I would approach you questions.
Is there or would you like there to be a dashboard? Or would the user always start at the [Student List] form.

Also, i would recommend reading this article. Leszynski Naming Convention This naming convention is widely used by Access developers.
To be honest, I'm not sure what you're talking about when it comes to a dashboard.

And I'll be sure to check out the article tonight.
dashboard/switchboard/front page (a form that is opened for the user as a starting point for the application).

I also have another question for you. How many users would be using the database/application at once?
I was jut thinking it'd start with the Student List form (I know I need to work on the form names, as I'm modifying this one from a template).

I'd like to have say, half a dozen folks possibly editing it at a time, but they'd never be on the same patient (realistically, though, it'd be 3-4).

This is basically a starting point for me. Eventually, I'd like to teach myself SQL and whatever else I would need to step it up a notch and have it web based. That's probably a year down the road, though, at least.
Any more input/ideas on this? I've been tinkering on my end but haven't gotten it to do exactly what I want yet, unfortunately.
Avatar of Ryan
Sorry about the delay in responses.

To get to your initial question, I don't think what you're asking is very difficult, I just can't fully understand what you're asking, there's a lot in that file, and a lot of words in the question.

I wouldn't suggest creating a table that is a copy of another unless it's absolutely necessary. Duplicated data wastes storage, and creates 2 copies of things which complicates managing the data a lot.

I can't tell what you're asking explicitly, but I'd imagine you'd have a form/report for the patient, that has a subreport/form of the evals/visits. Each new eval/visit would be a new line, sorted by date, defaulting to show the most recent.

Another possibility is an auxillery table, that is basically a 1to1 relationship to a table and just extends the columns to give you more information.
In a nutshell, it's copying the table into another, and I think it's necessary because I want to be able to change the info in the new table (the Progress note) while leaving the integrity of the data in the first table (the Initial Evaluation) intact.

These are the steps the provider would do:
1. Go to the Progress note tab
2. There is a drop down menu with the Inital Evaluations of the patient (could just have the dates listed on the tab).
3. The provider selects the Inital Eval they want, clicks a button and it imports the data into the form and the new table for editing into the newly created Progress note.
4. Once done editing a report is created (which I haven't done yet) and printed out, and the Progress note is saved.

Does that make sense?
Avatar of Ryan
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
So, after reading up on the Insert Into command here (thanks for that, btw) I came up with this:

INSERT INTO Progress_Note (Prog_note_date) VALUES (Eval_date)

I'm just trying to get one field down for now, figured if I get one figured out I could do the rest.

When I do the above syntax in VBA I get a compile error: Expected end of statement. Any ideas on that?

Also, how does the above statement know which table (the Initial_eval table) to pull the data from?

Would this be a multiple-record append query, using SELECT or a single-record append query?