okay, last question. I promise. You all have been so helpful, though...
I have been given the assignment of splitting down a table into two more tables(three in total). My question is this: Could I use the Table analyzer? If so, what are the shortcomings? I would like to have the other tables referenced by the key of the original table instead of a lookup field, whioch I'm not sure if I can do in the analyzer. What attracts me to the analyzer is that it would make the needed queries to join the new tables. If I don't use the analyzer, would I use a make table query to accomplish it? If so, would I have to change every existing query in the database that used that old table?
I know it's a lot, but any help in pointing me in the right direction would be much appreciated
I'm trying to split a table up because I now realize it won't be able to do what I need in the future. It wasn't designed properly at the outset, and I'm trying to correct it now.
This is a database of pregnancies and deliveries.
The single table does not cater well for multiple pregnancies (twins, triplets, etc), and also I foresee problems when mothers come back in future for another delivery.
Therefore I am trying to separate data into 3 tables: Mother, Delivery and Baby.
This is because each mother can deliver more than once, and each delivery can have more than one baby.
I have set up a trial database, with just a few fields in each table to see if this works. An screenshot of the table relationships is attached.
The primary key of the Mother table is linked to the Delivery table, and ditto the primary key of the Delivery table and the Baby table.
I'm not sure how to migrate the data over, in terms of the primary keys of each table, because in the new tables, these should be an AutoNumber field, so that they are unique numbers.
I have a table TO-det and another table DO-DET.The table DO-det will have details about all DO for each TOID record.Both have a common field name TOID The tables are related under ONE-MANY relationship.One TO-DET record can have many DO-DET record
Now I wanted to create a form where if i add a new record to TOID i must also be able to add data for DO-DET for that corresponding TOID.
One lists all the stock symbols and company names = SYMBOL The second table lists the purchase information for each stock = PURCHASE
I then created many queries, etc. using this data. Symbol is the key link between the various tables, queries, etc.
NOW that I understand the lookup wizard in the data type, I would like to change the symbol field in the purchase table to a Lookup field. I, of course, receive a message. I am told to delete the relationship with the other tables. If I remove the relationships and change the data type, can I then replace the relationship with out damaging all the queries and forms?
I created 2 table that are linked to a link table so to get a many to many relationship.I have a table for locations and a table for parts.One location can have many parts and One part can belong to many locations, that is way the many to many relationship.
how to get values from the parts table to the Location table using the linked Primary Key in the foreign key linked field.The values also have to be interactive, if I change the value in the part table it needs to update in the location table.
What can I put in the field of the location table to get a specific value from the parts table?
I tried setting the field to calculated field and put the Dlookup function but that didn't work. The lookup wizard seems to be the solution, but I can only create combobox or list box and I need to select the value manually. I just need a value to be put automatically by looking at the foreign key.
If I put the Dlookup function on the default value, those it update if a value is changed in the part table?
Hi, i have a table that has contact information in.
It has contact name, number etc and company.
Quite alot have the same company. I was hoping to split the table into two, a company and contact table which are linked. I've been thinking about this and not too sure how to go about it.
I'm on a project to build a database for the stores in our area. Things like managers, banks, and partners obviously should be in seperate tables. Most of the information though, seems to belong directly in a table of stores. Basically each record is a store and there will be very many attributes per record (about 30+). Some attributes only relate to certain departments and only someone from that department should see that info. If I broke up the big table into smaller tables it would be the same information but every table would have to have the store number to link it all back together. From a design standpoint this is bad because the store number is redundant.
So my question is, is it a good idea to split up the table for security purposes?
My main concern is security, I can take a hit on performance or storage.
I have one table with the following fieldsName, Address1, Address2, Postcodewhen data has been imported from Excel I have found error in the data entryie the excel spreadsheet contain Name, but the address has been entered into the Address1 field now I need to split into Address1 and Address 2. Please Help:confused:. I am beginner to access have never used VBA only some queries. So sorry but I will need to know exactly how to do it. Thanks so very much for your help. Sometimes a comma seperates the address but sometimes just a space
I am working on a project that requires to calculate interest on the amount. There are 2 tables, 1 with Interest rate for a product for different and periods and another is product table with amounts in different period.
Table 1 (Rates table)
Product From To Rate 1111 1/1/2012 1/20/2012 .75 1111 1/20/2012 1/28/2012 .50 1111 1/28/2012 4/6/2012 .40
Table 2 (Product table) Product From To Amount 1111 1/1/2012 1/17/2012 10000 1111 1/17/2012 1/24/2012 15000 1111 1/24/2012 2/25/2012 20000
What i need to do is to calculate interset based on the above 2 table and insert into another table (Interest). However, the problem is with identifying correct rates for the periods in Table 2.
Eg.
for period 1/1 to 1/17: applicable rate would be .75
for period 1/17 to 1/24: From 1/17 to 1/20 the rate would be .75 and from 1/20 to 1/24 it would be .50
this is what i want to achieve, basically to split the period between 1/17 to 1/24 into 2 so that appropriate rate can be applied.
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?
I have a database composed of personal statistics. (name, age, height, wt, etc). I have two attachment fields. Photos and Videos. Each of these fields can contain more that one file. The size of the video attachments is starting to get me up close to the 2 GB database limit. If each attachment field contained only one file, I would convert the fields over to a path link. I'm stumped on how to move the files out of the main database to control the size, but maintain the multi-file link to my forms. How to restructure this?
I am looking for a query that will return records from a table that have related records in another table. Opposite to the Unmatched Query Wizard.I have two tables: tblSupplier and tblSupplierProducts.The two tables are related by the field "SupplierId".I need the query to only return Suppliers that have Products.
I want to split a table into multiple sets based on rowcount. Suppose I have a table having 10,000 records. I want different sets which should have values based on rowcount. Suppose if I select set 1 then the table should populate records from 1-2500. If I select set 2 then the table should automatically give the records from 2501-5000. If i select set3 then the table should have values from 5001-7500 and so on.
I'm developing a ms database for the a "repair service" .
I created a table witch contains basic data from customer, including its unique ID number (key field), name and contacts ...
Now I need to store the "repair service" data from each costumer ( its time consumption, replaced materials...) in a separate table but, If I'm thinking well, I must have a table for each costumer so, I will need to link each individual "repair service" data table from each costumer to each costumer itself.
I have two databases, one containing the tables and one containing the forms and queries. The forms/query database is built from linked tables from the tables database. I would like to expand one of the fields from 7 characters to 9 characters. I planned on doing the in the table database, then updating the linked table. However, the table I need to alter has relationships with about 10 other tables and I get the (you cannot make changes to a related table error message). Is there a way I can make this change without deleting all the relationships?
For future reference, should the relationships have been created in the form/query database?
I'm building a database which will generate work orders, and those have a opening date and closing date, besides that it has a work order number that is of the following format:
YYYY/ NNN (being the NNN the work order number in that year)
to create the number of the work order I'm trying to fill the WOYear field with the year of the WODate field...but I'm not being able to do it... How should I do it?
Hi :confused: I am trying to set up a query. i have to work out the cost of a phone call with the use of three fields-- i have: lenght of call, country call code-e.g england is +44(these are fields in my query) and then the cost per minute values for each country call code(but these are in a table) e.g.Country CodeRegion Cost per minute +1 United States, Canada, Caribbean£0.20 +20Egypt £0.30 +212Morocco £0.30 How would i set up a query to solve this problem?#Can someone please help me???
I have a very simple database with two tables. One stores Schools that I work for and the other holds people that work at that school.
I have a form that displays information from the schools table and I have a button that I want have open a form that displays the people that are related to that school.
THe problem is I can't figure out how to limit the recordset to just the people from the school record I was just viewing.
For example. I am looking at a record with School ID 1. I want to run a query on the People table that will return just the people that work for school id 1. I just can't figure out the criteria part.
Hi, I'm a total newbie at Access, and know nothing about scripts. I've been operating at the level of using the Wizards and drop-down menus. I am trying to create a report that does two things: 1. Displays the results of queries that sum data from a table (I think I have this figured) and 2. Displays those sums as a percentage of a number that is input each time the report is run. (This is only one number that comes from a totally different place and has no prior relation to the data.) Any help/advice that anyone could offer would be much appreciated! Thanks!
Hello, I have two related table: table_Company and table_contact. Now i have: Table_Contact Table_Company James Bolton -----------> MacDonalds Hellen Kidman ----------> MacDonalds Now, i wanna have one "MacDonalds" in my Company table, and delete the others "MacDonalds" and still have the relationship between my contacts and their company.
Hello I need to add daily records to a related table using a form, from a button or subform displayed on a form updating the master table. This would enter the related key to the new form ready for insertion etc. Can this be done?
tblAcq and tblAcqDetail In tblAcq I have information about the delivery and in tblAcqDetail delivered articles, price and quantity. tblAcq [AcqID] ; [Contractor]; [TransportCosts]; [CustomDuties]; [SumWithoutVAT]; [LimSUM](this total cost of shipment Goods+other cost)
I'm not sure why a couple tables are not being updated after entering data. I think my relationships are correct. I've attached the database.
steps: open Main form add addres then try to add an owner. it doesn't work the first time but it does work the second time. add test data and that works. now if you look at the related tables (OwnerInfo, WellLocation, TestData) they all have p_id which is the wellLocation Id
now open gernalinfo form enter in info (tests requested doesn't work right now but you can click on them in the tbl GeneralInfo) Now if you go to the table OwnerInfo and TestData the key p_id has not been updated. But it's there in WellLocation (ID).
Heres some example data: 1 M24308/2-2F 2 M24308/4-2F 3 ORD9F0000 4 ORD9M0000 5 D90000VLO 6 D90000JOO
How would I design the table to allow me to define alternatepart numbers and also related parts? For example:
Record 1 is the primary part and record 3 is the alternatepart. Record 2 is the primary part and record 4 is the alternatepart. Records 5 and 6 are related parts to records 1, 2, 3 and 4.
I have a form that allows the user to select a part and displaythe part data. But I also want the alternate parts and related parts displayedon the form, maybe in a subform with tabs. I havent gotten there yet because Icant determine the table design.
