I need to modify my customer table to cater for multiple names at one address.
Should I move all the addresses into their own separate table or leave them as is (name-Address-postcode- email etc in one table) and put the additional addressees into a separate table or is there a better way I have not thought of.
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...
I have create 6 tables for library books (which are differentiated by categories). Each table has different category and unique ID name e.g. F1, F2, F3..(for table 1), G1, G2, G3..(for table 2) and so on. How do I combine all those tables into 1 table for easy search for a book rather than open up each table? Tried append query but its ID run as 1, 2, 3... , not F1, F2, F3 and it only append 1 table, not the rest.
View 3 Replies View RelatedI have a table that has a field that holds a total. Through historic reasons the total in a (relatively) small number of cases is wrong. I have analysed and worked out what the difference should be and have created a table with the value that is missing. I basically want to add the 2 together.
So I have
Table1 Field1 Value1
Table2 Field2 Value2
I want table 1 to be
Table1 Field1 (Value1+Value2)
Programming wise I would just have
Value1 = Value1 + Value2
How do I go about doing this in a query or some other whizzy way?
I want to add 3 new fields to the structure of around 80 tables (all with identical structures). Is there a way to automate this process?
Paul.
Is it possible to direct the placement/order new of fields when modifying an Ms Access database in code?
I need to modify the schema of an MS Access database via code - but I want to be able to direct the order or placement of the fields within the tables.
For example - if TableOne has 3 fields - Field10, Field20 and Field30.
I would like to be able to add say Field15 between Field10 and Field20 - not just append it to the end of existing fields.
I believe it is possible as you can do it within Ms Access itself. I can use ADO, DAO, ADOX or SQL for that matter - but it seems all of these offer no placement of the field within the table.
Hello all:
Posted earlier in General, this form might be more appropriate.
Every week I download new information into a table. The download has all the information, but the table will spill out any information from the download that is already existing in the table. The downloaded data does not have a date criteria which would have been easy to keep track of the last data download.
How do I create a query right after the download, that will provide a list of the new information just downloaded.
Thanks for all your help!
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.
View 13 Replies View RelatedHi. Please do not lecture me on database normalization, as this truly is not in my control.
I have 2 dozen tables, each with 13 fields. All of the field names exist in at least 12 of the tables. And all tables share a ssn field where values are common.(Confused yet? Sorry, if you are...)
If I design a query showing all 2 dozen tables and their fields...
Can I set up a query criteria where I enter the requested ssn and then the required field name and have the result show the values of all the fields with that name, among the ones of the 2 dozen tables where that field name exists? (for that specified ssn)
If I am only as clear as mud, please let me know, and I will try again.
Or, by asking if I can search for field values querying by field name, am I any clearer?
Russ
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?
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?
View 2 Replies View RelatedI need a table structure that will allow me to have a repair log data entry form with 3 cascading combo boxes on the repair log data entry form. There can be many repairs for a specific job but most of the time there will be one repair per job and 1% of the time two or more repairs for a specific job.
I am pulling a report based on a query that will show the repaired location, facility type, repaired item, repaired component on that item and other details related to the repair.I uploaded an empty database with the structure.
Cascade levels
I. Facility Type
II. Item by [I]
III. Component by [II]
Im constructing a form with check boxes next to names. Id like to have the checked names input serially into a single table field like:
IssuedTo
Name1, Name2, Name3
There are 8 possible Names. Is there way to do this without a long complicated If-Else-Then function?
Is it possible to have the field names in a table be set by the values in another table? The desire being that for a database used in various locations, the local variations could be changed in one table which would then propagate that change throughout all the forms, reports, tables etc.
View 14 Replies View RelatedLooking for efficient table structure for this. Let's say I have a list of contractors who will get paid a certain amount of money each week. The amount of money changes each week by measuring the amount of work done, giving it a price and calculating it. The fields are these:
[pk]WeeklyWorkID
WorkID
Quantity
UnitPrice
Total=Qty*UnitPrice (not stored in this table)
EmployeeID
WeekID
So far, it's all good, but we wish to pay the contractors in an unusual way. Let's say he makes $16,000 of work this week, and we want to pay him using cash AND/OR either one or multiple debit cards. For example:
Cash: $1,500
Debit card 45: $7,500
Debit card 71: $4,000
Debit card 13: $3,000
Total = $ 16,000
We can only deposit $7,500 max in each card. But it's even worse... two or more contractors can SHARE the card and it's not always the same card. Sounds pretty funky, but it's what the company wants to do. So, what would you recommend me in this case?
I'm guessing I'm gonna need these tables: tbWeeks to hold the week number, tbCards to store the cards' info, and an extra table to make a many to many relationship between the cards and the employees.
I have an Access 10 DB that includes 299 names and other associated data relevant to these names. I have a need to drop 249 of these names that are no longer needed in the DB, and just keep the 50 names that would remain in the table.
If I am in the table is there any way to somehow "designate" or select the 50 names I want to keep and then just mass delete the other 249 in one fell swoop? If I can somehow sort the 50 names so they would appear as the first 50 names in the table, then I could simply delete all the names below.
But not sure how to make this happen. It would seem to be the simplest solution. Unless I can physically drag and drop each of the 50 names I want to keep to the top of the table, but I don't think this is possible.
My employer wants me to update and revise a form with almost a hundred controls. All of the information is stored in a single table.
Is there a way that I can (easily) sort the order of columns in the table to alphabetize them? I know how to sort and filter records, is there a way to sort the columns other than the manual click and drag solution?
Note: I know that the database I'm working on for this project is not normalized. My employer isn't concerned with normalization. I've made as many corrections to the table as possible, already, but some things just won't be fixed.
I need to update the names of my ODBC linked tables in my Access database, how can I do this without causing issues with my queries/reports?The current linked tables are to a SQL View on a database called mcsrm_live, and called e.g. vwDamagesReportNew
The new SQL views that I need to link to are identical in structure and content and on the same SQL server but different database - forkdw and are called e.g vw_R_Damages
Is there a straightforward process to do this without affecting the queries and reports in my Access db?
I want to populate a table with the database's reports.
And somehow be able to select a row and open a report in design view.
I need to delete all the field names from my table so I can import a new excel file with different field name headings,
currently I run:
DoCmd.RunSQL "DELETE * from table1"
this deletes the data in the table, but not field names.
What Vba command will allow me to do this.
I have created a database for 2/3 users (will progress to more once the system has been smoothed over) who will have the db.file open from 9-5 and will be consistently adding and changing data within 2/3 forms. Most of the time but not always a message comes up saying,
"You do not have exclusive access to this database at this time. Any changes you make may not be allowed to be saved later"
I can understand that it can create data corruption if multiple users are allowed to edit and modify the same data at the same time but surely a database can't be that limited. I thought that splitting the database into a front end on a local server and then a back end on my computer would solve the problem but everyone who is using the front-end still experiences the same problem.
How to be able to overcome issues with multiple users access the same forms and tables? Even when another user is editing one form and another is editing a different form the message will still appear.
I would like to import excel sheet that contains about 45 fields and the fields names are not well defined(not obey the Access rules). I would like to import the sheet into ms access without changing the excel sheet because i have to provide only the interface to users where they have to import sheets only. (All the time In Excel fields name are arranged and in specific order but invalid names of fields).
After 1 week the person has new excel sheet with same format and the process of importing data into ms access will be continue for whole year or more.
Have 2 tables:
...TableA: 1 indexed field "Name" and many data fields in ~ 1000 records
...TableB: 1 indexed field "Name" and 2 data fields (DataB1 and DataB2) in about
~ 50 records
Tables currently have NO relationship set.
Want to add TableB data fields to TableA if Name are identical.
DataB1 and DataB2 fields are Not currently in TableA but could be added very easily.
Update (or whatever) will be done weekly where all previous data for DataB1 and DataB2 will be cleared from TableA before job is run.
I have the following script which updates fields in a table.What I am after is a msgbox script to list the ID field value once it is updated.
Code:
Set rec = dbs.OpenRecordset("Select * from Stk_Merge_Delete_Register")
rec.AddNew
rec("StkItemID") = [Forms]![MergeItem]![txtStkFrom]
rec("RackID") = [Forms]![MergeItem]![txtRackFrom]
[code]....