I have a database that I need to house data per employee per month. I have base records which represent January. One field holds the month, ie 01/02. I wish to copy and past all these records, but change the month field on the new records to 02/02. In future months I will need to specify to only copy, say 02/02 and then change the month field to 03/02. Can I set up a query to accomplish this?
Copy & Pasting Specific Records (1 Viewer)
- Thread starter Tuili63
- Start date
David R
I know a few things...
- Local time
- Yesterday, 22:43
- Joined
- Oct 23, 2001
- Messages
- 2,633
What information are you copying over? Many things can be done more efficiently by splitting up your data into separate tables and then rejoining it with queries and/or subforms when you need to view it as a whole.
Try not to think of your database as a spreadsheet. Are you familiar with database normalization rules? The first three are pretty standard and I think will help you get your data organized more effectively.
HTH,
David R
Try not to think of your database as a spreadsheet. Are you familiar with database normalization rules? The first three are pretty standard and I think will help you get your data organized more effectively.
HTH,
David R
David,
I do need to keep a record per month per "consultant". This is to track each months' invoice amount and also for what was received and when.
In the past I just copied, pasted and then retyped in myself.
I run parameter queries to only grap the records that I need at any particular time.
I would just like a better way to do this than what I've been doing.
Please forgive the "crude" way I'm sure I've been doing it, but lack of proper training has led me to work around problems the best that I can, which may wind up with the same results but probably not the easiest or best way.
Thanks
I do need to keep a record per month per "consultant". This is to track each months' invoice amount and also for what was received and when.
In the past I just copied, pasted and then retyped in myself.
I run parameter queries to only grap the records that I need at any particular time.
I would just like a better way to do this than what I've been doing.
Please forgive the "crude" way I'm sure I've been doing it, but lack of proper training has led me to work around problems the best that I can, which may wind up with the same results but probably not the easiest or best way.
Thanks
David R
I know a few things...
- Local time
- Yesterday, 22:43
- Joined
- Oct 23, 2001
- Messages
- 2,633
Don't think I'm blasting your skills, I'm not. If you want, try to find my original post back in October 2001 when I'd been working with Access for 2 months and ran into a database normalization problem. Pat Hartman straightened me out and I've been repaying the favor ever since.
It sounds like you could use two (possibly more) tables: call them tableEmployees and tableInvoices. tableEmployees holds all of the Employee-specific data, date hired, job title, supervisor, email, extension, etc. tableInvoices holds all of the Invoice-specific data, dates, amounts, supplier, etc. (Your invoices actually probably will require something like tableInvoiceDetails, if you have more than one item ordered per invoice.) Your Invoices table will also have a field of comparable type to the primary key of your Employee table. You can then store your EmployeeID (the primary key of your main table) here and not have to repeat all the details about that person every month. You can create a similar relationship (Tools>Relationships) between tableInvoices and tableInvoiceDetails if need be.
This is the structure that will let you stop storing duplicate data, if I understand what you're doing now correctly. If you create a query that holds both tables, it will reform what looks very similar to what you have now.
Try this topic for more on Database normalization.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q100139
You will probably have to write some Update queries and do some fiddling to get your current data into a normalized structure, but it is well worth it!
Good luck,
David R
[This message has been edited by David R (edited 02-12-2002).]
It sounds like you could use two (possibly more) tables: call them tableEmployees and tableInvoices. tableEmployees holds all of the Employee-specific data, date hired, job title, supervisor, email, extension, etc. tableInvoices holds all of the Invoice-specific data, dates, amounts, supplier, etc. (Your invoices actually probably will require something like tableInvoiceDetails, if you have more than one item ordered per invoice.) Your Invoices table will also have a field of comparable type to the primary key of your Employee table. You can then store your EmployeeID (the primary key of your main table) here and not have to repeat all the details about that person every month. You can create a similar relationship (Tools>Relationships) between tableInvoices and tableInvoiceDetails if need be.
This is the structure that will let you stop storing duplicate data, if I understand what you're doing now correctly. If you create a query that holds both tables, it will reform what looks very similar to what you have now.
Try this topic for more on Database normalization.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q100139
You will probably have to write some Update queries and do some fiddling to get your current data into a normalized structure, but it is well worth it!
Good luck,
David R
[This message has been edited by David R (edited 02-12-2002).]
