Copying The Structure Of Forms And Tables And Their Associations?
Sep 8, 2011
I have a psychology project where we soon use a questionnaire on a daily basis (maybe 80 variables per day per subject). Currently, there is no data in the database.
I've managed to create a table called day1 (with 80 day1 variables) and a form that looks like the questionnaire we want to use (and linked apropriately to the variables in the table for day1).
What is an easy way of duplicating the table and form for successive days? That is, i want the structure of the day1 table copied to another table (call it day2). It has exactly the same variables except they are slightly different to differentiate them from day1. I would like the form for day2 to have exactly the same layout as the form for day1, but link to the variables in table 2.
I know i could just couple the day1 table, change the variable names slightly, and then copy the form for day1 and change the links to point to table2, but there are many assessments (18 days).
Is there a way to create a new independent table from the structure of an existing table? I want to create the same table with no records and place it in a separate folder
I'm building a database for my company who refurbish computers. I'm wanting to build in functionality for if a hard drive is damaged and it has to be replaced then for us to be able to trace (based on asset and tracking numbers) what hard drive has been removed from a PC and if it has been put into stock or destroyed and if a stock drive has been put into a PC.
Any item that is brought in whether it be a PC, laptop, server or hard drive etc is given it's own unique tracking number. Same is true for any stock we buy in for refurb purposes.
I'm thinking that this will be done by having a field for the original asset number and a field for the 'current' asset I.E. the one it's been put into.
I need to combine two tables that have identical structure to one table. Different people have used them to insert data from different parts of our project. I have tried to find information about how to do this but so far with little luck.
I have an equipment database, within which I am attempting to create associations between various items. The items are distinguished as level 1, 2 or 3 depending upon where they sit in the equipment hierarchy, level 2 being sub-equipments of level 1, and level 3 being sub-levels of 2.
So for example the level 1 item could be PC, level 2 items would be keyboard, mouse, monitor, base unit etc.
What is the best way to structure this relationship? I had considered generating a sub form, and have set up queries to generate lists of potential items with each level description - so that users can select the items they want to add to the associated list of equipment, but I'm not sure how/where to generate that associated equipment list.
has anyone had a problem with copying tables from one database to another -- it used to be so easy by just dragging from one database to another but in 2007 this isnt possible -- it seems to want to delete records rather than copy over - am i missing a setting to allow this?
I don’t understand how to structure tables and relationships.
I want to build a form that allows the user to search for a postal service from a group of carriers.
User must be able to input into a form:
Weight in g, Kg or lbs Insurance level required Whether signature is required (Yes/No from drop down list) Collection/drop-off options (tick boxes)
Each service has a different insurance level, some need signature; some services collect, and all have different rates.
I just want to know how to structure the database.
The main part of my question is what would be my main table be and what fields would be contained in it, and what foreign keys would be introduced to pull in info.
Would you have a different table for each service?
There is one main policy table, and a collection of related tables that store information associated with each of the policies (coverage amounts, agents, etc). What I'm struggling with though is where they renew every so often (most times it's each year, but some go three or more years). Because I need the database to show everything EXACTLY as it was on each policy term, even expired ones, I don't want to just edit the existing records when policies renew; I need to create a copy of the ENTIRE record (across all related tables), but with a new PK, and the ability for users to change a few items, such as the policy number and effective dates.
There are two keys that I use to identify policies: one is the PK, which is an auto# that will need to change, and the other is a small text ID set when the policy is first issued, in order to make sure that even when policy numbers change from year to year, we can always track the history, so of course that one will need to remain the same. If I have the user click a "Renew" button on the policy view form that goes to a small form that takes what changes I want to allow and converts those into variables, could I then use those variables in a query, and have it copy all of these related records, with the other tables taking the new PK from the main table to keep the overall policy record linked?
I have a field that has a description of the project I'm working on, which has client information on it. I want to take all 600 of those records and copy them to fields in the same table, so that I can remove the client information, which will allow me to pull whichever of those two I need when I run a query.
I am looking to copy one exceptionally large table to another. I can use VBA to copy the entire Report2013.accdb to Report.accdb while compacting at the same time. This is very fast and works well. However once it's copied I need to rename the table tblReport2013 to tblReport. None of these table are in the accdb that I am running the scripts from FrontEnd.accdb. I am working very hard to keep the data tables under the 2 GB limit, which is why linking and keeping the data separate is necessary. There is the chance that Report2013 will be on the network while tblReport will be local to the user, and may or may not be a subset of the Report2013.
I have considered a number of options: DoCmd.Rename - I can't seem to tell it a database location
I could try docmd.copyobject - and see if it keeps the file size the same or smaller then the original.
I could just write the sql statement and copy the data that way, and compact the table afterwards. The trouble with doing the transfer that way is hitting the 2GB limit, before I can compact/compress the newly imported data.It just seems odd to have to find another solution when all I really want to to rename a table and ensure the new new is still linked as a separate table.
I want to be able to copy the data within a selected field and then place it into a fresh database is this easily done?
my second questions is i have several fields which need to be merged into one 'Address' field as currently i have data with Building Name, Number, Line1, Line2, Line3, County, Town, Country. This is required to be merged into one field how would i do this?
Not all fields have data is there a way to put a space between each of the current fields when merged as well?
I am using access 2007 and cannot seem to find in the field properties.I need to input data in one field in a table and it appears in a field within a different table.
If i make minor changes in the backend db structure (eg add a feild, change properties of a feild in a table,). These changes are not reflected in the frontend. how can thses changes be reflected in the frontend usig the simpest method.
I am creating a database where the user can import data from a number of spreadsheets (one spreadsheet for each company). Currently I have one table for each company with the year being the primary key. Each year when a rolled over spreadsheet needs to be generated for a certain company I have Access import the data to a blank template in excel which will display the prior 4 years of data (for informational purposes) and leave a column blank for the end user to fill in the current year data. When returned the current year data is imported into the database.
My problem is that I cannot see a way to do this without having a seperate table for each company. If I have one table with all company data I cannot use the year as the primary key. Any suggestions on how I can reduce the amount of tables I need. Setting up the rest of the tables wont be a problem, but future users of the database will have to go through the process of creating tables for new companies and creating all the queries and macros associated with that table.
I have a table where in 286 different fields have to be updated by the users. ( Nature of project And i have 10 fields which are coming as a input from another table, so i use Append and Update query to add the new datas and update the datas in case of any changes in the input from another table. ( to avoid manual corrections)
Now coming to my real problem, all the 286 fields have to be updated by the user's are check box type, now what is the solution since access will not accept more than 255 fields?
How can I add an additional field to a table based on the output of a query? For example, I have table A with 2 types of records and table B with only 1 type of record. Based on this, I can determine which records in table A are the same type as those in table B. I would however like to be able to flag within table A, those records in table A that are the same as those in table B.
Firstly, I want to admit that my knowledge about data base construction is fairly basic. I am trying to build a data base for humanitarian projects that do not at all resemple of the usual examples including customers, invoices, suppliers and what have you. My problem in constructing the DB is that most of the projects will address more than one subject and also include more than one target group and even operate in more than one country. In my first attempt I filled the relevant fields (subjects, target groups and country) with more than one value using a form with multi select lists. The problem was that it was very difficult to query these multi-value fields and kind people in this forum strongly advised my to reconstruct the DB avoiding such fields. What I need is to be able to view/print various selections based on precise criteria that include a region (or country), a subject and a target group in all kind of combinations. The problem is to decide on what tables to establish and their interrelation. I guess that when entering a project record (using a form) I will have to store somewhere what subjects (could be more than five) and target groups the project include. It is, of course, not a problem to establish special tables or value lists for the subjects and target groups and countries, but where/how do I store the basic project information for a project and the multi-values that are related to the individual project? I hope that this is not too confusing and I would appreciate any suggestion for a simple table structure and the interrelation between the tables. Many thanks in advance. Niels
I have been working on a a table design, but I am failing to create the correct relationships.My main table is the tblIncident with the three remaining tables being linked via a one to many relationship. I am using an auto number as my primary key, then linking that number to a FK in each table.
The end goal is to create one form with a varying sample of combo boxes, check boxes and data entry fields from each table. I did not plan to create a subform for each table, but maybe that is required?
I am starting out creating a training database to track training needs and expiring training etc.I'm just looking for some tips as to how to structure the tables and relationships..I have an employee table, department table and training type table.However im wondering how i can set up requirments and then match these to check if the person is trained up to date on all required skills?
I'm creating a timesheet/check in system (log in when the person starts work so it logs it and when they log out it logs that also for the manager or someone with high power to see.) for a fictitious scenario for an IT major project but I'm not sure where to start!
At the moment I have a few tables that are like this.
Table: Employees Fields: EmployeeID, First Name, Last Name, DOB
I have a form that is used for recording test scores. The first selection that is made is a type of test. After the test is selected, some tests have a second sub-type of test that needs to be recorded. My quetion lies in how to hide or grey out this second combo box until a test is selected that requires it.
I don't want to "hard-code" it in the sense that I create an If Else statement that requires looking at values from the 1st test type combo box. I want to make sure that the DB is scalable and when additional tests are added, additional sub tests can be added if necessary.
If this makes sense and anyone has any ideas, I would appreciate it.
I haven't been on Access for a while so im still in the process of getting the cogs turning....... i am trying to design a database structure for controllers and heating.
each controller can have many heating units each heating unit can use many controllers
rather than code everything, how could i set this up?
Example: Controller01 can be used with Boiler01, Boiler04, Boiler12, Boiler 20 Controller02 can be used with Boiler01, Boiler05, Boiler15 Controller03 can be used with Boiler02, Boiler03, Boiler04, Boiler20
I have a table with a ControllerID and a table with a BoilerID. In the boiler table, i have the ControllerID and a one to many relationship.
