I am assisting my employer by combining two databases into one. Both databases have the same field "structure" but the data differs. When creating my append query.
View RepliesI have a linked ODBC table in my database that contains data like this -
Code:
Task Serial CrewAmount
00_INSTALL ENGINE INTO PEDESTAL STAND 707308AS30.2
00_INSTALL ENGINE INTO PEDESTAL STAND 707308OMB0.2
01_BORESCOPE INSPECTION 706496AS114.24
01_BORESCOPE INSPECTION 706496AS223.24
[Code] .....
What I would like to do is remove the duplicate values for the Task field, but show how much time each Crew has spent on each Task per Serial. The end result would look like this -
Code:
Task SerialAS1AS2AS3OMAOMB
01_BORESCOPE INSPECTION 70649614.2423.2428.78
00_INSTALL ENGINE INTO PEDESTAL STAND 7073080.20.2
01_INSTALL OIL TANK 7073085.67
[Code] ....
What is the best way to achieve this result? I've played around with Append queries, union queries etc, but nothing seems to give me the result I'm after.
I have a table in MS Access 2010 that is a link to an external data file in .csv format. I assumed that it was not possible to append data to a linked table until recently. I appended 3 records to the linked table and discovered that the 3 records were appended to the table and the external data file it was linked to.
What am I missing ?
I have 2 tables as below
Table 1
ID RID StartM EndM
1 xyz 1100 040.0935 040.1254
2 xyz 1100 029.0110 038.1003
3 xyz 1100 029.0110 038.1010
Table 2RID
StartM
EndM
[code]...
Now I need to create 3rd table based on above two tables
for table1.XYZ 1100 table1.StartM>Table2.StartM then table1.ID=2or 3 and
for table1.xyz 1100 table1.EndM< Table2.EndM then table1.ID=1
I am developing a database for incoming inspection. I have set up two tables, one with spec callouts per product and the other table contains fields where I want the specs from the tblSpec table to be Append into the tblMeasurement table. Now, within the tblMeasurement table (where I am trying to append specs from the tblSpecs table into), I also have additional fields for actual measurements from received goods.
My plan is as I go through and select the Vendor, it populates the associated products to that vendor (no problem there). When I want to select the product, I would like the specs from the tblSpecs to dumped into the tblmeasurement table that correlates to that product on the form.
How can I have this automatically append after selecting the product? I am not sure if I have my relationships set up correctly or not, but I cannot see to append when I am trying to execute this function while in Query view.
I have the db attached in a zipped format.
I have to import a file from an external website into a table (tableA) that I have established. The issue is that I don't need all that data, just specific ones (the website does not allow me to select the specific data to export). I created another table (tableB) to capture the data that I require for my report.
How do I take raw data from tableA to append to tableB the required fields that I want?
I currently have an unnormalized table that consists of individuals (employee profiles) which currently has a field for attachments. I am using the "attachments" for photographs of each employee however I dont want to maintain photographs for former employees, or if I do not forever. I was told to normalize the table by removing the "attachment" field and placing it in a new table with referene to the employeeID.
Since I currently have something like 200+ records where I want to relocate the employee picture, i tried to create a make table query off the origional table and only referencing the employeeid and "attachment" to the new table however I keep getting an error when running the query. The error indicates something to the affect of having multiple values.
I want to import an Excel spreadsheet into a table in my Access DB.
I am using following: DoCmd.TransferSpreadsheet acImport, 0, "tblSchedule", "C:mailinimport.xls", false
This code works but it is appending to the table.
What is the best way to update the table - or would it be best to delete the old table and then to create a new one to import tha data into?
If the latter would be most elegant solution, can anyone help with some code that would do this ?
Thanks
Mat
I have a single table database for inventory. Every 3 months or so, some records get changed or updated. My manager wants to keep an archive of all of the records that have been changed so we can go back and look at an history of all of the records. So, My thought is to create an "Archive" table, appended all of the current records to it. Then, when changes are made, create an Append Query, or what ever works, to copy just the changed records from the Main to the Archive table. There are only 200 items in the inventory so it is not a large database.
View 1 Replies View RelatedAfter appending data in a table, I open a particular form. I want to display only the last record. I've added the code (DoCmd.GoToRecord , , acLast) to the On Load and On Open properties without success (opens first record).
View 14 Replies View RelatedI use Cognos to pull raw data from Oracle. I take this data and put it into Access 2010 tables to then run queries. Because the people creating the data in Oracle are often slow, I run back data to catch missed information, typically two months. For October, for example, I'll run October and also September and August.While I have new data, I also have a great deal of duplicate data. How can I ensure that I append only the new data and exclude the duplicate data?
View 6 Replies View RelatedI have 3 separate tables with the following fields: Year; Name of School; Emis No. (Primary Key); No. wrote; No. passed; Pass %. These tables were created in Excel and imported in Access.
I would like to do the following (see table below): 1. create a form that would allow me to view all the data per school in datasheet view and also allow me to enter new data annually; 2. create a new table where this information can be stored
.
YEAREMIS NOSCHOOLNO WROTENO PASSEDPASS %2009109964Bhuqwini4012302010109964Bhuqwini6126432011109964Bhuqwini6422342012109964Bhuqwini
I have 3 separate tables with the following fields: Year; Name of School; Emis No. (Primary Key); No. wrote; No. passed; Pass %. These tables were imported from Excel. I would like to do the following: create a form that would allow me to view the all the data per school in datasheet view and also allow me to enter new data annually.
View 2 Replies View RelatedI'm fairly new to Access, but have set up a database containing 8 normalized tables. I would like to know if it's possible to update all those tables by importing an Excel file into each table, or importing the Excel file into one Access table and then appending that table to those 8 tables. AND STILL MAINTAIN THE RELATIONSHIPS. The Help directory only talks about importing or appending into one table.
View 7 Replies View RelatedSome days ago I made it by using "query", but now I forget it how I make the relation on this situation. Here is my problem.I have 3 Table on mdb file, named Table: A, B, Status.Table Status have One Filed with 1 Data: Dishonor
Table A have three fields
Sl Number: (Auto Number)
Status: Lookup wizard-data of (Table-Status)-Default Value is "Honor"
Amount:Number
Table B have two Fileds
Sl Number: Number
Status: Lookup wizard-data of (Table-Status)
After Entering Some data on Table A it's Look like as:
Asl numberstatusamount
1Honor5222
2Honor855
3Honor988
4Honor7777
5Honor777
6Honor9999
[code]...
Now I want to change the Status of SL Number Honor to Dishonor so I fillup data on Table B is as like following
sl numberstatus
5Dishonor
Now how I can get the result as following by using query:sl numberstatusamount
1Honor5222
2Honor855
3Honor988
4Honor7777
5Dishonor777
6Honor9999
7Honor6666
8Honor7777
9Honor666
I have three tables: Vehicles; Vehicle Reallocated; and Vehicles Retired. I have a form that runs a query to find all the info in the Vehicles tbl that is not "Retired", not visible in the form. I then have the option to toggle to a Reallocated or Retired form. When i toggle to the reallocated form, i have the like fields in that table (ie Van #, Vin, Make etc) pulling the info from the hidden subform with the vehicle query, so i do not need to fill in repeat data. However, when i add a reallocated date and the new clinic that vehicle is for, i get the record ID for the vehicle reallocated table as expected, but when i save none of the data moved over from the query saves in the record?
How to get all the data on the reallocated form to save?
I am working from an existing database which is just two table. The main table has a massive amount of redundancy and duplication and needs splitting into, at first glance, 5 tables.
After I have run my various make table queries and added a Primary Key and FK field to the new tables how do I populate the FK with the Parent PK.
I thought I could simply add all the fields from the new table and then create an adhoc join in an update query to populate the PK to the FK. When I do this however I get "You are about to update 0 records"
I have tried the table analyzer but it doesn't give the correct options to split the table the way I need.
I have two table
1 is name master containing EMPID(Primary Key)
2 is Saving which also contain EMPID
i want to entered data in Saving with each and every EMPID with Master
Let's say that there're 2 workers: Worker A, and Worker B, and they are fixing some engines. There's 2 different engines: Engine A, and Engine B. And these workers are fixing these engines for all day, and in the evening they have to register how many engines they've fixed.
And my job is to design Access database for them. I thought it will be very easy to do; I've created table with columns: Date, Worker, Engine, Quantity - so every day Worker A and Worker B can register, that they have repaired some amount of Engines A and Engines B.
But there's a problem that I also have to register how many Engines have arrived to the factory, and how many of them weren't repaired on time (E.g. 15 Engines A have arrived, Worker A fixed 5, Worker B fixed 5 so there's 5 engines left for tomorrow)
I've figured out that I should somehow create table IncomingEngines with columns Date, Engine, Incoming, Fixed, Undone (field Fixed should be completed automatically every day for every engine - it would be a sum of engines A and engines B fixed by worked A and worker B - so I could fill Incoming field manually)
It's easy to create this kind-of Query, but I can't add column to querry, or edit it.
I have a table called "WorkRequest" consisting of some fields such as WR Number, WR Date, WR Time, WR Requested by and WR Work Requested.Once a work request is completed, I want to open a form called "JobCards" from a table called "JobCards". When I click on the pulldown box for the Job card number, it lists the "WorkRequest.WR Numbers" which is what I want.
My problem is: As soon as I select the WR Number and it displays in the jobcard form in the JobCardNumber field, and I press ENTER or TAB, it must automatically populate the corresponding fields on the JobCard Form. These fields are defined identically in bot the "WorkRequest" and the "JobCard" tables.
I currently am working on a small inventory project. I have a table with the fields "Part Number" which is my primary key, "Description", "Cost", and "Sale Cost". I have a second table that I would like to use to keep track of purchase orders. It has the fields "Part Number", "Description", "QTY", and "Cost". I would like to be able to open purchase orders and be able to select a part number from a combo box that pulls "Part Number" from my item list.
So I can enter items in to my item list and later when I do purchase orders I can go to purchase orders select my item and have it automatically fill in the description and cost in my purchase order. If the item does not exist I can enter in the item in to the item list table. At this point i'm not worried about a prompt to enter in new items if they don't exist in the item list table. I just want to my Purchase Orders table to be able to autofill description and cost by selecting a "Part Number".
I am new in using MS Access. We have this thesis that checks the attendance of the professors and then sends the ID number of the professor to MS Access. I am using smsenabler as the software to encode the ID numbers to the database.
The problem is that, I want to transfer the ID numbers to specific table for the professors name. I don't know how to do it. And I can't make the ID numbers as primary keys because it duplicates every time the professor scans his/her ID.
Hi
I am trying to write an update query which will return a value within a new table, based on a date in my import table.
The date format is dd/mm/yyyy and I want to return the month in another column (ie 01 to 12).
To complicate matters, I want to return a value based on some additional logic. My invoice statements run on a cycle of 17th of the month to the 16th of the next month.
So if a date value in a line item of text in table 1 says 17/11/2007 to 16/12/2007, I want the value returned in my new column to be 11. If the date range is from 17/12/2007 to 16/01/2007 then I want the value returned as 12.
My American colleague wrote the following expression, but due to the different way we write dates in Europe, can't quite get this to work.
Expr1: IIf(Day([call date])<17,Month([call date])-1,Month([call date]))
I'd be grateful for any thoughts.
best regards
Mulhouse.
I have rather complex select statement (at least for me) that produces a summary of how a team scored in a league competition that week. I would not even worry about this issue, except that people float around as alternates and I deduced it would be easier to compute and store scores for a team the week they shot with a given group of people versus keeping track of who shot on what team over a 10 week period.
The selection takes 40 individual scoring records and consolidates them into 10 team summary records. I have verified that the query works, but implementing it has turned into a problem.
Problem one: my knowledge of recordsets is almost purely theoretical at this point, having never worked with them. Therefore commands and structures are a problem - mainly, I'm not sure what code is needed to post a recordset to a table
Problem two: the select statement that works as an Access query is bombing in VB
My code to date:
Dim RS As Recordset
Dim DB As Database
Dim strSQL As String
Dim inpWeekNum As Integer
inpWeekNum = 0
'InputBox "Enter Week Number" 'eventually request week number from user. an integer from 0-9
'strSQL = "SELECT tblRoster.* FROM tblRoster WHERE (NIGHT = 'Fri');" 'a test str. this worked.
strSQL = "SELECT tblRoster.TEAM, tblScores.WeekNo, " & _
"Sum([A1T1]+[A1T2]+[A1T3]+[A2T1]+[A2T2]+[A2T3]+[A3T1]+[A3T2]+[A3T3]) AS TeamTotal," & _
"Sum([A1T2X]+[A1T3X]+[A2T2X]+[A2T3X]+[A3T2X]+[A3T3X]) AS TeamXs" & _
"FROM tblRoster LEFT JOIN tblScores ON tblRoster.HEDR = tblScores.HEDR" & _
"GROUP BY tblRoster.TEAM, tblScores.WeekNo HAVING (((tblScores.WeekNo)=0))" 'hardwired week number
' "GROUP BY tblRoster.TEAM, tblScores.WeekNo HAVING (((tblScores.WeekNo)=inpWeekNum))" 'user prompted week number
Set DB = CurrentDb()
Set RS = DB.OpenRecordset(strSQL)
Do While Not RS.EOF
'Appending Code here... 'obviously missing code, but I know that it does cycle through as I would expect it to.
RS.MoveNext
Loop
'This was a previous test based on a canned qry I had saved (that resembles the above qry.
' this successfully posted, but only one record
'DoCmd.RunSQL "INSERT INTO [tblTeamScores](TeamNo, WeekNo, TeamTotal, TeamXs) " &_
' "VALUES (" & TEAM & ", " & WeekNo & ", " & TeamTotal & ", " & TeamXs & ")"
The select statement correctly produces this output in Access (for Week 0):
TEAM WeekNo TeamTotal TeamXs (how does one properly post a table in this interface?)
1 0 2397 182 0 3152 333 0 3292 664 0 2014 135 0 2836 186 0 3652 577 0 2024 198 0 1108 29 0 2630 2210 0 1977 11
Anyone out there have some suggestions? Am I even on the right track?
Thanks.
-Brian.
i have a table with tons of records in it. There is a field called "date to return by" that previously had a default value of Date+30. Now, some things changed in our company, and under certain conditions, the "date to return by" for new records will be Date+14, or Date+30. At first, i tried making this relationship in a table as a default value, but, you cannot make a relationship like this in a table.
So, i made a query to evaluate the relationship.
what i want to do, is have a query that will have all the same fields the table had and evaluate whether a records' "date to return by" should be Date+30 or Date +14, and then put that new record back in the table.. but, this will only be for new records, as i will be keeping all the old records in the table as they are.
how can i do this?
all of my reports read off of my table so i want to keep the table. can i make a query that will put new records into a table one at a time as i make them?
I am stuck on a problem!
I have a table containing line items which are appended to another table using Append Query. But in the table being appended to I have a field 'QuoteRef' and i need to append a value from a form into this field but am stuck how to do this.