Create/Link Smaller Tables To One Large Table
Jun 22, 2005
I am a newbie when it comes to Access and ASP but I am trying. I am in the need for some help.
I have a large table in Access 2003 and need to break it into smaller tables (not using a query) where the fields are the same except each of the smaller, new tables will hold info for a specific person. These smaller tables would need tro be linked to the larger.
Is there an easy way to do this? I need to keep it in tables due to the ASP software I use to generate the asp pages.
Is this possible?
My goal is for each user to be able to only view/edit their data and for the large table to reflect any changes made to the smaller table.
Thanks,
Dan
View Replies
ADVERTISEMENT
Mar 17, 2005
Hi,
To avoid the mind-numbing tedium of have to use make-table queries loads of times, is there a quick (probably VBA-related) way to split a large Access table, of about 350000 records, down into 93 smaller tables, based on a key code field that identifies each group of records e.g. GBW102, GBE999, etc?
Any help much appreciated.
thanks,
Alex
View 7 Replies
View Related
Mar 4, 2008
I currently have 8 tables in my Database.
How can i extract all the information in those tables and put all the data into one large table? I want to extract everything apart from one table?
and can I format the large table once the data have been put in i.e. insert new Columns at the start, and populate fields based on the value of other fields values?
Any ideas or help? thanks
Kind Regards
Richard
View 6 Replies
View Related
Jul 30, 2013
I have a table with 3 fields. The fields are down1, down2 and down3. . I would like to use this table to create a new table (downtime). What I need too do is loop through each record in the table and place the three fields independently in my new table. For example, I would like to go to the first record in my original table, than place down1 as my first record in my new table, down2 as my second record and down3 as my third. Than I will go to the second record in my original table and place down1 as my fourth record, down2 as my fifth record, down3 and my sixth record and so on.
View 5 Replies
View Related
Jul 17, 2006
Hi!
I am a newbie in Access. I am running Access 2003 and would like to be able to create links to Visual Foxpro9 tables using the VFP OleDB provider and not the ODBC drivers.
A few of my clients want to use the VFP9 tables in their reports but so far I have not been able to find a way to do it using OLEDB (except in a module with VBA code which I don't want).
I can do it using the ODBC but the problem is that ODBC driver does not recognise the VarChar fields in the tables.
I tried creating an Access project and then 'File --> Connections' but it does not display the 'Provider' tab to select which provider to use and by default assumes that SQL Server is the source database.
How can I get Access 2003 to allow me to select the OLEDB Provider?
Any help will be appreciated. Thanks.
View 1 Replies
View Related
Nov 23, 2004
What I need is a table that will hold a primary key field and 16 text fields at 255 characters per field. (NO memo fields) This will total about 4,080 characters but Access 2000 limits the record size to 2,000 characters.
I have two tables with 9 fields in each table in a one to one relationship as follows:
Table1: Primary Key = MyKey = autonumber field.
Table1: 8 fields = text at 255 characters each field.
(i.e. Data1, Data2, Data3, Data4, Data5, Data6, Data7, Data8)
Table2: Primary Key = MyKey = number field.
Table2: 8 fields = text at 255 characters each field
(i.e. Data9, Data10, Data11, Data12, Data13, Data14, Data15, Data16)
The problem is I want all the fields to act as though they are from a single table and to be on a single form. I don’t know how to go about the LinkChildFields and LinkMasterFields without using a SubForm. A SubForm don’t work because it expects the second table to have many records.
Thanks Stan
View 3 Replies
View Related
Aug 28, 2013
I am testing the security of my DB X on Acccess 2007. I could create a new database Y and linked to the database X. Unfortunately i could change the records on the tables. I don't want other DB that make connections to my DB to change my tables!
View 1 Replies
View Related
Mar 14, 2013
I have a table "Product" and in this table I have two fields "StoragePlaceID1" and "StoragePlaceID2". Both these fields link to the look-up table "StoragePlace"
*Attached Image "Product_StoragePlace" from the Access Relationship Window"*
When I want to add a new Product from my inter face i get the error you can see in the Attached Image "Save_Error".I think the problem is that the Relationship is defined as One-to-Many,there a way to define the relationship as Zero-to-Many.
View 5 Replies
View Related
Sep 17, 2013
I want to make a database of diseases (need to learn them for school and would like a serchable database on my smart phone for future reference).
Unfortunately spent a lot of time making hundreds of pages of word tables before i realized a database would be better. See attached image or pdf. Is there a way to import the tables to Access?
I need to preserve the hierarchic info in the nested bullet point lists. E.g. under treatment i might have a point called Acute treatment, with sub-levels, Step 1, Step 2, etc. with their own sub-levels. I need to maintain this relational hierarchic info.
View 3 Replies
View Related
Jun 21, 2013
I'm not very familiar with linking data from Access to Excel but I think it's what I'm going to want to do for my operation. I have a huge spreadsheet that my boss handed me and he wanted to see if it were possible to only present a part of it in access. I already have a form to a table that I created however, it came to my attention that creating a link also creates a new table so I guess my question is: Is there an easy way to have the form always correspond to the linked table? Or another question: is this the right way of thinking about linking the data?
View 1 Replies
View Related
Apr 22, 2013
I have just updated a big mdb database to accdb. But we have two associated databases that cannot be updated - they are for UPS and Fedex data, which presently does not support accdb. Can I link to mdb tables in another database from my main accdb database?
View 1 Replies
View Related
Aug 18, 2014
So, I have two Primary Keys in my table, how then do I link another tables to it?
tblAdmissions
PatientID Number Primary Key
Visits Number Primary Key
tblRoomPhoneNumbers
PhoneID AutoNumber Primary Key
View 3 Replies
View Related
Apr 6, 2015
I have two tables (A) and (B).
Table (A) contains key-field name "position1".
table (B) contains key-filed name "position2".
How to make a link between key-field "position1" and part of key-field "position2".
View 5 Replies
View Related
Feb 25, 2015
I've nearly cracked where I want to be. I have essentially had to do the core of the back-end in SQL in order to organize the data in a way that actually makes sense to ACCESS.
Following this I now have a table with the following fields
Stock No
SK Code
Free Stock Qty
Stk Qty Pd
Actual Min
Actual Max
Forecast EoM
Forecast +1Month
Forecast +2Month
Essentially this is a stock forecast sheet. It takes into consideration incoming stock and estimated usage to provide an output as a "Forecast" stock level at the end of the forthcoming months.
The next phase of developing this is to allow users to manually change the front end by Group and Part Number with Quantity entry so that it auto updates the Estimated EoM, +1Month or +2Month figures depending on which month figure they are looking at. In addition, this information would need to be retained and ideally stored as individual tables or other .
View 2 Replies
View Related
Jul 25, 2014
We have an MS Access 2010 Database that uses Local Tables, External MS Access Tables, and ODBC Linked Oracle Tables (Accessed for Read Only). The unsual issue occurs with the ODBC Linked Oracle Tables.
One of the more important aspects of the project is to modify the ODBC Links to point to upgraded Oracle Database Tables. Up until today, all of the Links had been able to be remediated with a simple refresh and test.
This morning, however, it was determined that one of the Database Tables did not exist in the Schema. Instead, it was an Oracle Synonym for a Table that existed in a different Schema. We believe that we have the proper authorization for access to all of the Schemas involved, and despite this fact, MS Access was unable to link to the Table properly.
View 1 Replies
View Related
Jul 30, 2013
I have a risk table containing risks and risk owners (many owners for one risk), meaning that I have two risk owners columns. What I would like to do is to connect both risk owner columns (containing the name IDs) to the Names table. But Access does not allow to connect more than 1 column to another.
See the attached file...
View 6 Replies
View Related
Aug 19, 2013
I was wondering if it was possible to have a master table where say 10 of my regulatory tables and their data once inputted from the forms will appear there? Is this possible? Because right now, each of my regulatory tables has a form where the data is inputted and appears within said table.
All these tables are separate, obviously. I want to consolidate all the information into one spot, is this a possibility within access?
View 1 Replies
View Related
Mar 10, 2013
I have been asked to maintain a directory for our local Scout district. It's currently in a spreadsheet, but is crying out to be a database.I have created a "group" table, this lists all the groups in the district.I have a 2nd table called "people" this holds details of all the people in the district.As some people can have a role in multiple groups, I have created a 3rd table called "link", this is to link the people to groups.
Having read various different posts on sub forms, I am still at a loss on how to create a form / subform to populate the link table.My initial thoughts were to have a combo box on the main form, listing the people and then have a list of groups on the sub form (the are only 24). I would have a check box next to each group and if checked, it mean that the person selected in the combo is associated with that group.However my issue is that the tick box would be a "yes/no" field and the entry in the link table needs to be a number.
View 8 Replies
View Related
Jun 27, 2013
I simply want to link a table from one database to another but am getting an Error -1002 with the error description: "'|' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long."
I have tried just creating a basic table with one field and linking it to another database and still getting the error. It happens when I compact and repair on close.
View 5 Replies
View Related
Jan 1, 2014
So I have two tables,
table1: (company_name, company_code, year_month, rating)
table2: (company_name, company_code, year_month, asset, debt, equity,...)
What I would like to do is to call up all the data in table1, and then call up (asset, debt, equity,...) from table2 where the company_code and year-month are equal. What do you think is the best way to do this? Keep in mind a few things:
1. I'm dealing with hundreds of thousands of data lines
2. I may need to get more data similar to table2 in the future and call it up in the same way
I thought I should create a primary key "company_code-year_month"for both tables, but that method doesn't seem like a good one, and if I were to have another table, will have to create the same primary key for that table as well?
View 3 Replies
View Related
Feb 3, 2013
I have a database split into a frontend and backend.
The front end has several make-table queries that create tables of data on which pivot tables and other analyses queries are based. The make-table queries get re-run every now and then as new data get entered.
The resulting tables are huge - 500K records in some cases. It seems like a bad idea to store them in either the front and back end.
Should I create a second "backend" that holds just these temporary tables? If so, what's the best way to design the make-table queries so they make the table in this "backend"?
View 4 Replies
View Related
Nov 4, 2014
I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).
Currently, I setup the query to pull info from the form field like this:
DateField: [Forms]![frmmain]![DateField]
However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).
View 6 Replies
View Related
Jun 4, 2015
I have three tables that contain different columns but linked by a primary column call Name. I want to create a table where all these different columns in the three tables join to form a master table which can be updated regularly either through the master table or the smaller tables. The master table also has the primary column as Name.
If I update the master table with records, it should update the respective linked table and vice versa. I also want to link these tables to my SharePoint site.
Note: except the Name column, none of these tables have any other columns in common How do I go about this?
View 7 Replies
View Related
Oct 17, 2005
Hi All
I am trying to create a database for estimating manhours on projects. There is a setup table with two columns: "Project" and "Time Period". The database is supposed to allocate manhours to tasks on projects, which is used in estimating man hour costs and also forecasting labour resource requirements.
For example, say the database is used for "Project X" and "Project Y" projects, with Project X running from Jan to March and Project Y running from Aug to Dec. The setup table would look like this:
PROJECT TIME PERIOD
Project X Jan
Project X Feb
Project X March
Project Y Aug
Project Y Sept
Project Y Oct
Project Y Nov
Project Y Dec
From this table, I need to automatically create a new table for each unique project that allows man power to be allocated to each time period. Following on with the example, there are two unique projects, so two tables
need to be automatically created with column headings as per below:
Project X table:
FUNCTION PERSON JAN FEB MARCH
Project Y table:
FUNCTION PERSON AUG SEPT OCT NOV DEC
"Function" is a description of a role, for example engineer. "Person" is the individual undertaking that role, for example Bob Jane. Then in each time period a number between 0 and 1 would be entered corresponding to how much time (0 is no time, 1 is full time) that person would be spending on that function for that time period. Then the table would be populated with many functions and people in this manner.
So the number of tables automatically created depends on how many unique projects there are in the "Project" field of the setup table. The number of columns in these tables will be atleast two: "Function" and "Person",
plus another column for each time period defined for that project.
Perhaps something can be done with a make-table query, but all I can get that to do is copy data from one table straight into another table.
Any help would be greatly appreciated.
Thankyou in advance.
Joey
View 14 Replies
View Related
Jul 17, 2007
Hi, i currently have three tables.
Policy table: Policy number(key),date and fund(4 possible strings of four letters)
Last Price: date, fund, last price
Next price: date, fund, next price
What would i have to do to make a table that has a policy number, date, fund, last price and next price?
Thanks
View 1 Replies
View Related
Apr 26, 2015
I am very new with access database. Want to create table with Dates & DayName. For example, tblYear2015 with all dates from 1-Jan-2015 to 31-Dec-2015 in FirstColumn and DayName in second column as per date of First Column. DayName Should be entered automatically as per date entered in first column.
View 6 Replies
View Related
