This may have been covered already, and if it was I appologize. Searched for it but couldnt find it.
Any how, I have a large table of records (problem is its growing). It lists customers and dates. My table has way to many records, so i need to condense it a little bit. What i need to do is transfer contracts that have been fulfilled (by date) transfer to another table that contains records of contracts that have been fulfilled. I want to do this by a push of a button, automatically transferring any records that the date has passed to the pastcontract table.
I am currently studying for my A levels and need some help with microsoft access. What I want to do is transfer information from one table in my database to another. When it transfers however, I want it to alter under a certain validation. This is what i want to do;
Admission numberArt 011649 011650 F 011651 B 011652 C 011653
This is an example of the information. The letters regard to a GCSE result. I want the grade to be transfered to a table called "point score". Each grade has a value, A* = 8 points, A = 7 points, B = 6 points and so on. When the data is transfered to the point score, I want it to just state the point score and not the grade. Can anyone help?
I have three list boxes. Two of them has data from a database in them. I want to chose one piece of data from one index, and choose two pieces of data from the other list, and click a button to send it to another list(database, that would later be exported to excel). Does anyone know how I would go about doing this?
I am still trying to solve the following problem: I am trying to transfer data from one table to the other in a different folder using the follwing command:
By using the above command I could be able to transfer "tblSanTV" table from D:DBSsewertvinsp.mdb to another table in a different folder but it transfer the table and data together. But that is not what I want to do. All I want to do is to transfer the new data from one table to existing data in another table. These tables have the same names but different field names.
I tried the following command to solve my problem, but I end up with more problems than solution:
In the above command, I was trying to import data from "D:DBSsewertvinsp.mdb", tblSanTV" table to the general database. But I want data in the field "INSPNO;MAINLINE; SAN_MH_FROM" in "tblSanTV" goes to the corresponding fields in general dabase "tblSanTV" table, fiel "INSP#, MAINLINE ID#,US MH ID#" consecutively.
I want somebody help me out with this issue. Please help me. Thanks.
I have still an issue with tranfsfering data from two tabels to another two tables which have parental relationship. A while a go on this forum someone have suggested to me to use the append query but that only works to append to one table, which is not my case.
If I don't transfer these two tables together or at the same time, they will loose their relationship because of their relationship which is auto number.
I have tried to modify the append query SQL and didn't work for me. Please someone help me solve this problem or tell me other alternative way of doing this. Somone look at the following modified append query and see where I made a mistake or cannot do it this way:
I tried to modify the append query to the above format but didn't work. I even tried "inner join" to put the above table together but I could be able to make it work. Anybody knows how to solve this, please help. Thanks.
i have 3 databases containing data in the same format.
basically i work in a school, and have a database for 3 different classes.
each database has a table containing the childrens details(table a), and a linked table (through primary key in table a) to targets and points scote for each week.
--table a-- child number name carer address etc
--table b-- index child number (linked)(displayed as childs name in table, but stored as a number) week starting target 1 target 2 target 3 points 1 points 2 points 3 etc
now this database has been working really well. but if ever a child has to be moved class then i need to be called to transfer the records manually (because nobody else can do it) and i have to get the records manually and copy and paste them between databases. This works fine and i've had no problems. And the auto number in table a is changed from the original. but this seems to automatically corrolate to table b when i paste that over. so everything is working fine. but.... first of all, it creates lots of work for me when kids move classes, and also if i'm not there nobody else can do it. so.... i've tried using an append query to transfer the data. I tried using 2 append queries (1 for each table). but.... it works fine for table a, but i'm having trouble with table b. the append query for table b pastes all the records into the equivalent table on the other database through searching all records related to childs name on table a, therefore retuning all records in table b for that child. the problem....... when this data is transfered it transfers table a data fine. but table b is fine except for the fact that it doesn't transfer the childs name data (number), which i can kinda understand, but from what i can understand, my queries are doing the same thing as if i was to cut and paste manually and it works fine when i do that.
ok so its all very confusing, and i hope ive explained adequetly. oh and btw, i can't put it all in 1 database, because each class may need to access the database at the same time.
I have a subform called "Earthwork". I also have a query called earthwork200 and I need to dynamically transfer data from the earthwork to the query based on a certain column.
for example, the earthwork column i am using to transfer data to the query is "Desc" (the name of the column). How do i get data to dynamically goto the query as I click on a new item under the "desc" column?
I have a tbl called table1 which has 3 fields. i have a form called form1 which list field1 for all records in table1. when i double click on field1 i want to open form2, retrieve the data from table1 using field1 as input and display the results onto form2. can this be done just using Access or do i need visual basic? i am new at Access and have not ever used visual basic. i do know java. can you tell me how to do this or suggest a book which could explain it. the access book i have does not go into this type of detail.
and my job pickup form has the following code Option Compare Database
Private Sub Form_Load() Set rs = Me.RecordsetClone rs.FindFirst "[job id] = " & Forms!job![job id] If (rs.NoMatch) Then DoCmd.GoToRecord acDataForm, Me.Name, acNewRec Me.[job id] = Forms!job![job id] Else Set Me.Bookmark = rs.Bookmark End If rs.Close End Sub
i have a button 'Command6'... that opens my job pickup form from my job form. when i click it however i get the following error..
I have a access database with a customer table. And a excel spreadsheet wih customer details. I wish to transfer all the excel spreadsheet customers into the database - customer table.
The format of the customer table in the database is... Customer ID Customer Surname Customer Forename/s Position Company Name Address Line 1 Address Line 2 Address Line 3 Town/City County Postcode Telephone Mobile Fax Email Address Type Of Business No Of Employees Capture Method Preferred Contact Method Newsletter/Fact Sheet Update Auto Recieval Additional Notes Passed On to Partner Organisations Date Input Web Address Chamber Member Description Month & Yr Est Website ID Complained?
The format of the excel spreadsheet is.... Contact(consists of forname and surname) Role Email Tel. CompanyAddress(has to lines add 1 and add 2) City Postcode
I need to make fields match from the spreadsheet to the field table on the database?
can someone please provide me with steps as to how to transfer the spreadsheet into the database correctly?
I am creating a database for a hyperthetical car hire company. I have a table for the customer details, a table for car details, a mid table with current car hire information. I want to create a table for previous car hire information. Is there a way that i can transfer the details from the current car hire table to the previous car hire table without copying and pasting? (when the customer returns their car)
I've built a table and used it to build a form. One the form I have three boxes, one to enter the DoB, and two others that record the age and also put the individual into an age bracket.
In the latter two categories I have entered a formula in the Properties Box under Data & Control Source (the formulas are below) to automatically work out what the age and age bracket is when the DoB is entered. This works fine but the information isn't transferred back to the table.
Do I need to do the info in the table first and then update the form? And if so where do I enter the info in the field properties in the table design view?
Can you work out whats wrong with this I used the sample database password system and tried to add a button to enable the user to change their own password. The problem was at the time that the change password form must open displaying only their own username, this is where the complication starts where i try to transfer UserID from the menu form to the Change password form.
Hi there, this is probably a very simple problem for people with experience of Access, however i have been having problems with being able to tranfer information from a query to a table.
I have made a query which displays a number of different items with their prices and who bought them (ie. booking ID)
I have then made a query which asks for the booking ID, then once you have input that it searches the items and adds up the price for all the items which that booking ID has.
both these queries work fine. The problem i am having is that i want to transfer this total price into a table. I have been able to do this by manually typing into the sql code the price, however when i try to get the query to collect the price itselfs, it gives the error "operation must use an updateable query"
btw i am making the query as a update query,
sorry if this post is a tad long and messy, thx in advance for any help
At work we have a website with a mysql database attached to it. It keeps data such as customer name address postcode contact id etc.
Currently our main database is made in access.
I need to transfer data from the website database (which is in mysql) to our access database. And would like this to happen regulary. We have been quoted £1800 for someone to do this!
As this is a new job i would like to be able to do this myself, is it hard to do?
Could someone provide me with some information of how to do this? or where to start.
I have set up some pivot charts in Access 2003 that open up on the click of a button. No problems. My problem now is that several PC at work are running Access 2000. Is there a way of easily getting this to work????Many thanks Eq
I have a subform within a form that displays filtered data based on dynamic selection criteria which is assigned to the RecordSource/OrderBy of the subform. On this form, I have a "Print Results" button -- OnClick, I would like the RecordSource/OrderBy of the subform to be copied to the Report, then run the report. What I have that doesn't work is:
I am creating a database but I am having a problem with one little bit (so far!!)
I have form (and table) 1 and form/table 2. When I am in table 1 (which is company details) I want to be able to click a button which will open form 2 and allow me to enter a new record of incoming mail details regarding the Co. selected in form 1.
I have made a relationship between the Company ID on both tables.
I cannot seem to work out how to make Access take the Co ID no from form/table 1 and stick it into form/table 2 when I push the button.
In an Access 2010 form is it possible to export select records and fields in those records to a specific location?
Code: Set objDialog = Application.FileDialog(4) With objDialog .AllowMultiSelect = False .Title = "Please select a File" .InitialFilename = "C:" .Show If .SelectedItems.Count = 0 Then MsgBox ("Action Cancelled") Else
[code]....
The user can select the directory using the code above, but can specific fields in records be exported to a excel workbook in that selected directory?For example, if the are 5 records in the database can the fields LastName,FirstName,BirthDate in records 1,2,3 be exported to Setup.xlsx in that selected directory?
I have a table (tbl Team Info) which contains names and codes for teams within my business (>400 records) and another table (tbl Process) which contains a list of high level tasks (30 records).
I need to create something where for each team name 9in tbl Team Info) I can map them to the tasks that they undertake (in tbl Process) and assign a percentage of time then spend on each task. Each team could map to several different tasks.
In my simple database (attached), I need to mass duplicate Tasks and their Notes.
I have three tables: tbTasks (PK: Task_ID), tbNotes (PK: Note_ID), jtbTaskNotes (FKs: Task_ID and Note_ID). jtbTaskNotes is my many-to-many junction table that ties Tasks to Notes.
The main form (fmTasks), bound to tbTasks, has a subform (sbfm_TaskNotes) that displays notes associated with each Task. On themain form,you select which Tasks you want duplicated via a checkbox. The append query (quCopyTasks) will duplicate all tasks that have the checkbox checked. All good there. However, I can't figure out how to also duplicate each task's Notes.
I found Allen Browne's solution [URL] ....., but that only handles duplication of one record at a time, whereas I need to duplicate many records at a time (sometimes 10+ records). How do I go about duplicating multiple Tasks and their associated Notes?
Before you ask "why are you duplicating records?": There are times when tasks need to be re-accomplished and therefore need to have a new record. It's easier to duplicate records than it is to hand-jam everything again.
One shows my form with the Transporters Subform with 3 entries, and 1 entry.The three line items that say "Transporter" are in one subform. I used this code
Code: Private Sub Form_Current() If Me.RecordsetClone.RecordCount >= 3 Then Me.AllowAdditions = False End If End Sub
to limit the number of records I can add to 3 or less.My issue is that I lost the blank text box that allows you to add another record. So, if I only have one Transporter listed, there's no box to let me add a second or third.I have the following properties for the Transporters Subform set to "Yes":
Data Entry Allow Additions Allow Deletions Allow Edits Allow Filters
I have built a qry that initially shows the correct information. For example.
tblContent has 289 records with a Type = Class.
I built a Query to select from tblContent Type = Class and I get 289 records. I add additional criteria of Progress <>"Not Scheduled", I then get 206 records. I then add additional criteria Last Name <>"Demo" And <>"Care" And <>"Support". This brings up 200 records, but the query appears to duplicate each record 3 times. I do not have 3 of the same types of records.
The SQL Statement is below
SELECT tblProfile.LoginName, tblProfile.FirstName, tblProfile.LastName, tblProfile.Organization, tblProfile.CostCenter, tblContent.Title, tblContent.Type, tblContent.Code, tblContent.[Date Assigned], tblContent.[Date Started], tblContent.[Last Accessed], tblContent.Progress, tblContent.[Date Completed] FROM tblProfile INNER JOIN tblContent ON tblProfile.LoginName = tblContent.LoginName WHERE (((tblProfile.LastName)<>"Demo" And (tblProfile.LastName)<>"Care" And (tblProfile.LastName)<>"Support") AND ((tblContent.Type)="Class") AND ((tblContent.Progress)<>"Not Scheduled"));
The qry is named qryPhysical Class. I have provided the link to view the database. Can you help me?
All seemed to be working well, however, I noticed that all my subtable records in the database are exporting with each Primary table record. In my output, I'm looking to see each primary table record followed by one or more subtable records from a one to many relationship.
(Office 2010) Access/Word
Private Sub cmdPrint1_Click() Dim objWord As Word.Application Dim docm As Word.Document Dim db As DAO.Database Dim rstLandSales As DAO.Recordset
Dim dbs As DAO.Database, sql As String, rCount As Integer Set dbs = CurrentDb sql = "DELETE * dbo_InvPrice Inner Join (dbo_InvPrice Inner Join UpdatedPricing on dbo_InvPrice.StockCode = UpdatedPricing.StockCode ) ON on dbo_INvPrice.PriceCode = UpdatedPricing.PriceCode " dbs.Execute sql, dbFailOnError
