Hi everyone. Over the last couple months, this site has been a real lifesaver for me, just reading about similar problems to mine. This new problem, however, I have no idea how to search for.
I have a table in the following layout:
ID, FieldName, Value
10001, ID, 10001
10001, FIRSTNAME, Jonathan
10001, SURNAME, Reid
10001, ADDRESS, 10 Abbotsford Road
I need to be able to flip it around to be in the following layout
I've spent most of the afternoon trying to find an answer to my question so please forgive me if I just didn't have the right keywords to find it via search.
I have several clients that use my database which is split into two parts, one of which is the data back end. At times I release upgrades to the whole package and must currently go through the process of having them get me the BE file and I manually make changes to tables and update data.
What I was hoping to do was create a seperate database that could link to the BE file, check for structure items and if they are missing add them. For instance if I have a new field I want to add to a table it will look for it, add it, and then if necessary add data. This way I could have one master file that I could send my clients and have them run it and not have to worry about the timing issues of transfering files back and forth.
Any guidance would be great. Not necessarily needing lengthy examples that would take a lot of your time to type out, simply want to know if it's possible and the basics that I can research and read up on.
I need help with my table structures. Attached is a Access 97 sample of my existing tables.
Questions
1) I need a new table that shows which BankRepID’s are assigned to each ClientID. Currently the relationship between the Banks and their BankReps is normalized.
But every time I create a table to assign BankReps to Clients I have noticed it becomes possible for the user to assign BankReps to Banks where they do not work.
How do I prevent this through normalization?
2) Because one Client can have many banks and one bank can have many clients I created a many to many relationship. Is this correct?
I'm trying to create an archiving routine as my database is becoming very large. For about 10 tables I want to shift certain records to an external database which would have the required 10 tables with the same table names and structure.
So far so good. I now want to automate everything using vba. I can see how to use the INSERT INTO statement but I don't want to have to name every field as there are hundreds. I just can't see how to do this.
If the table structures are identical how do I neatly shift a bunch of records from one to the other using code.
A few years ago I upgraded a little software package that I had written to Access 97, without updating some of the old-fashioned methods and structures (eg. it still uses things like : Set workdynaset = db.Createdynaset("Client") ).
Due to problems that some potential customers have had loading the A97 runtime version of this software onto PCs with Access 2000 or Access 2000 runtimes, I have been trying to upgrade to an Access 2000 runtime version. I have read all the various threads on DAO stuff and the need to select the appropriate references on the Tools menu.
My question is this. If I select " Microsoft DAO 3.6 Object Library ", will the VBA code compile without my having to convert all of the dynaset-type code to a more modern form ?
I have an access application that I've developed and given to people and now i'd like to update it. I have a "master" database that I use for development and I would like to compare the users databases to this one and apply any changes that I've made to the database schema without having to remember all the new fields, tables and everything else I've added.
I wanted to know if it is possible to change the name of the Table which is going to be created using a Make-Table Query via code (VBA).
For example if my Make-Table query currently creates a table with the name "Table1" I want to change it to name "Table2" and then change it Back to "Table1" or "Table3" etc.... depending on the users selection.
I have a training Database in Access 2010 that I am creating. I have "courseindex" which is the table that has course titles and the course required frequency. I also have "trainingcompleted" which is the table that contains all training my employees have completed. The form "training" contains employee data and all training each employee has completed.
On the form "training", the training completed is selected from a combo box, which is pulled from the table "courseindex". When I update the combo box, I would like the training frequency field that corresponds to the selected training to auto fill in my "trainingcompleted" table.
Once completed I should be able to generate a report, where the user enters a date range and it will tell me who is due for training within that time frame. I have the forms, tables, and reports set up. I can enter the next due data in manually each time I update a record, but I would rather that data be updated automatically, since the frequency for each training will never change. I have over forty courses that employees must take. Some courses are required annually, some bi-annually, some every five years, etc...
sddd i need some help. about my problem i cant figure it out how i can change some values in the table by using a form or vb script. the problem goes like this.
"If one of the entry in the table is blank then the script will change it to zero." i wish you could help me guys. thanks!!
I did some BAD planning before I started creating my database and I set a text field ("field1") of a table ("table1") as primary key, then I linked this field to a field in another table ("table2", one-to-many relationship). Now I wan't to change some values in table1 in "field1" defined as primary, but I can't (Access refuses to do so...).
Because I have a lot of data entered allready I would like to know if there is any way for me to change values in "table1"/"field1" and that all the linked fields in other tables ("table2") would change correspondingly??????
I'm struggling with a small issue that has to be quite simple.
I have a form called "Project Main" with a label called "Check". There is a sub form called "PM Main" with a button "Button1".
What I want to do is to have the label "Check" change color when the button "Button1" is clicked. The color change is record specific, so here is what I have done:
-Created a table [Updated] that contains the project number [ID#] and a yes/no field [PM Updated]
-Now I need to be able to change that value (yes/no) when the button "Button1" is clicked.
-Then I need to have the main form "Project Main" check to see what the value of the [updated] table is.
-Then I need to have code to update the color of label "Check"
Does this make sense or am I making more complicated than it is?
I have this code that a member helped me create in one of my forms. However, the date that is being input into the table is 30 Dec 99. It won't change no matter what the code is changed to.
The date is showing up correctly in the subform, it is just not inputting right into the table.
Private Sub Form_Open(Cancel As Integer) Dim intNQ intNQ = Nz(DMax("QuizNo", "tblQuizzes", "logon='" & Environ("Username") & "'"), 0) + 1
I have a query that I want to run for multiple tables of the same structure. Instead of creating a query for each table, is there any way that I can write some code or SQL statement that will change the table that the query is using? Thanks!
Hi, I have an Access database that I would like to do a mass Table Name change. Reason is, all tables start with Data.<table name> so one table is called Data.Names and I would like it to be called just Names. I can manually do this but there are over 600 tables. Would take some time.
Please let me know if there is a script or how I can approach this. Maybe I could loop through each table name where it finds "Data." and strip that part out.
Ive got 4 tables with on field name MO_ID that is formatted as a number. I then have a make table/append query that takes these 4 tables and puts them all into one table. At this step, I need to convert the field MO_ID to being a text field so that I can use an update query to change some special scan codes into word equivalents. For example, 100066 is getting updated to "Admin".
The simple solution would seem to be to change the the original 4 table fields to a text, but that wont work in this case. I have several other queries that need to have relational properties with other tables that comparing numbers to numbers so that wont work.
Is there a way to make that field change to a text field? But only when I run a certain query and only in that one table?
I have a table that contains all of our company pricing for services. You can see a screen shot of the table here:
http://www.x-raysband.com/table1.jpg
All of this data is locked and cannot be changed by the user. However, the seasons keep getting mixed up. What I mean by this is that suddenly there will be a record missing from one of the seasons and there is a duplicate record in one of the other seasons. See the spot-shadowed fields here:
http://www.x-raysband.com/table2.jpg
Off season and Spring are now missing entries for CDJ, INC but the records that belong in those places are now showing as duplicate entries in Prime and Regular. Simply put, the season name somehow got changed within the record, but there is nothing in the system that allows a user to do that.
This has been happening ever since I added the pricing feature to the DB and I am completely stumped. Our work DB has the tables stored in a SQL server back end.
If anyone can shed some light on why this might be happening, any help would be appreciated.
Hello everyone, and thanks in advance for any help you can provide.
I am currently working on a large project to track, issue out, and re supply an operational stock of various tools and parts we use in our shop on a normal basis. This program is in it's beginning steps at the moment, and already I need some help.
If anyone has any tips on how I can change the raw numerical data in my table, via a textbox and a command button on a form, I would be greatful. I am trying to avoid allowing users to directly modify table data. Although this method is what I am aiming for, I will gladly accept any suggestions. Thanks
My scenario is that a user enters how many parts of 'Part No.' are going in or out of Inventory. I also would like fields automatically entered on other tables based on the data the user enters. All I need is some understanding of how to code something like this or a pointing in the direction of a resource that can tell me how to code this VBA stuff and the syntax required. Hopefully you get what I mean.
I have a database with 8 tables. When I create a replica, one of the tables (only one) in the replica gets modified such that Data Type for all the fields is Calculated, with the effect that the data cannot be changed by the user. The table design is not changed in the Master. What the heck? Of course the table design cannot be modified in the Replica, and there's no reason to modify it in the Master. A second replica on a different server had the same result.
My database consists of one large table of approx 3.9m rows and about 12 different columns. One of my columns is of a data type "date/time" but i need the format of this column to be "text"
The problem is that when I right click the table/go to design view/ and try to change the data type, I get the error "Microsoft Access can't change the data type. There isn't enough disk space or memory."
Which means i can't take the easy way out. Is there a way I can just create a new identical table and change the data type of my one column? I was thinking there might be something in SQL I could do, or some how in creating an identical table where only the field type has changed. I should note, I looked up this using the access in file, and it appears the ALTER TABLE SQL will not work on my machine.
the dataset was created from a txt file which as been lost to the ether, and I can't import the database into excel using a macro I didn't write unless the format of my column is text.
I have made a table and quered that table. I have a form that I designed and is working well. It updates the table like it is supposed to. The problem I have is when I go in and change the master table. I decided that instead of havinga field titled "Unlisted/Listed" I just made it Listed and you put a yes or no. But when I do that and go back to the form a box pops up asking for some value. How do I fix this? Thank you.
First post in this forum. I am using office 2003 pro. I have an Access database that I would like to change the title of. I would ilke this change to be applied to forms, reports and tables. I have done this in the past but can't recall how to do it.
I am trying to change a field that currently has a data type of number and I want to change it to a date/time data type. When I try to do this, I get a message that says there isn't enough disk space or memory. I am working with a database that has about 100,000 records in it.
I have two Tables TA and TB. I have a set of queries based on TA. I want to substitute TB for TA (i.e. TB will stand in place of TA). I want the old TA queries remain unchanged but now be based on TB.
If I delete TA and then rename TB into TA, I will lose the queries. Therefore I want to 'point' the queries (in the most efficient way) to TB instead of to TA.
Then I can delete TA. Then I can rename TB into TA and the queries will follow along, I expect.
I have been trying in vain to find a way of pointing the queries to TA instead of TB? How do I do it?
I have done it with Forms (Design Views, Properties), where I can select a datasource. But where is the corresponding thing enabling me to select a datasource for Queries?
