I have started a database to track my coin collection I have 2 questions.
1. I started tables for various coins the tables have 6 fields in addition to the primary field description, year, mint, condition, value & notes I decided it would make more sense to include the mint with the year is there any way to combine these 2 fields or do I have to redo the information?
2. I initially started with multible tables for certain denominations of coins ie: jefferson nickles, Buffalo nickles I have since decided it would be easier to have all the same denomination coins in the same table is it possible to merge 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.
What i want to do is take a field from one table and a field from another and join them together to make a single field.
I know this wouldnt work in practice without selection statements etc so only one field is selected, but if you could just help me on this code i would be greatful!
For example, assuming that the tables tblMyDatatable, and tblOtherData consists only of one record with each containing a letter of "y" then:
SELECT [TheData FROM tblMyDatatable] & OtherData AS ThisQuery FROM tblOtherData;
I have a table which has each claim as a single record. A client wishes us to provide the data in a format that lists the each procedure on its own line. I have attached a highlevel example of what the data currently looks like and what the query results should look like.So I need to create a query that would repeat the Claim Number and place all the Procedure fields into one field.
Hi, I have a db (MS Access 2000) that currently has 37 tables in it, each table holds about 30 rows and 72 columns of data. I need to basically create the entire database again but change all the column names slightly so that they still hold the same information but with different column names, then I need to merge each newly created table into the existing corresponding table.
As there are about 86400 entries I would rather change 2000 odd column names than re-enter all the data again. I would be extremely appreciative if someone could suggest the easiest and quickest way to do this. Many thanks in advance.
im having some problems with my db. i have 2 tables(ATL and BATE) in ATL, i have (im, IMAGEID, BREAK, TASK, LOC, bate) and in BATE, i have (Bate, var)
im trying to take Bate from Batetable, and insert it to ATL, even tho thers already a bate in ATL, both contain different records... i tried this, but didnt work Select ATL.im, ATL.IMAGEID, ATL.break, ATL.task, ATL.loc, ATL.Bate, BATE.bate from ATL, BATE; it worked, but BATE shows the same value for the hole row, it displays the same thing thing, even tho each row has a different value... can anyone help me out? thanks
Say you have multiple excel sheets where forecasting is done daily.
For example, sheet one headings: Depot, department, location, 01/09/10, 02/09,10, 03/09/10
and then second sheet is similar but: Depot, department, location, 02/09/10, 03/09/10, 04,09, 10 - as you can see each day the forecast starts a day after and ends one date late (14 days each in the real one)...
How to I join all these forecasts into one table; vertically with each forecast identified by the day it start for analysis later.. rather than doing cross-tabulate which would take ages, I just need to keep adding more excel sheets but the headings change as they are dates...
We are creating a database to log data on a project. There will be thousands of files. Can we input data, using the same table, at separate locations and then merge the data into a master table? We will need to do that many times.
Every quarter I run a report that pulls loans that meet specific criteria.I export this report into excel (the loans fall into column A)I add a file number and box number in columns B and C.I import the excel spreadsheet to table 2 (they're linked so I don't need to import, it's automatic)
Now that I have the updated information back into the database (table 2), how can I get this information back into table 1? The excel spreadsheet only contains a few loans that need to be updated in table 1. I have tried creating an update query with both tables linked and use the "update to" field. However, when I tried to run the query, it says I have 0 records updated.
My update query is as follows:
Field: Access Bar Code Table: Table 1 Update To: [Table 2].[Access Bar Code]
Field: Access Box Number Table: Table 1 Update To: [Table 2].[Access Box Number]
Basically I'm trying to have the query update specific fields in table 1 based on the information from table 2.
I don't have any database experience whatsoever so please go easy. I'm guessing this kind of this is extremely simple for all of you.
I'm constructing a database of network resources and devices and I'd like to automatically update the values in one field based on the values of a field in another table.
The first table is called "IP" and the fields are called "Address", "IP Type" and "Device". The second table is called "Devices" and contains the fields "Name", "Description", "Asset Number" and "IP".
Here's an example of the tables: (ignore the "code" tag. i've only used it to align my columns properly)
What I want is for the Device field in the IP table to automatically update it's values based on the values found in the Devices table. In this case, the values that should appear in the Device field in the IP table are "Xserve" and "ProliantX".
I've searched through but haven't found a complete solution, just little pieces which I'm too inexperienced to put together myself.
I created a form in Access that retrieves data from a table. Inside the form, I am able to access/populate data fields with data from the table. I also have data fields, inside the form, that requires key-in data. I have some how lost the ability send all data field information to a second table and clear existing data fields for new entry.
Questions: What settings, code or buttons can I use to send/store data field information to a new table? What settings, code or buttons can I use to automatically clear all data fields from my form once data has been sent to the new table?
I have a totalquery that runs fine and give me the sum for both fields I'm looking for but I can't get the outputs to fill the fields on the form. I have tried the Dcount query in the control source but that just returns an error and locks up access.
Code: SELECT [Tble-wcDelays].Causedby, Sum([Tble-wcDelays].HoursDelay) AS SumOfHoursDelay FROM [Tble-wcDelays] GROUP BY [Tble-wcDelays].Causedby, [Tble-wcDelays].LinkingID HAVING ((([Tble-wcDelays].LinkingID)=[Forms]![Frm-ePlusCent]![cleanID]));
I have a database that will register the emails coming in and what time, also the time, date out and person.
I have a form with the fields to be filled in and a submit button. There are some fields that are automatically filled in and others need manually fill in.
below that part is a sheet (subform in the form of sheet) that should be filled in with the above data. Once it is there, it should empty the fields so they are ready to be filled in again. If I fill new data and press the submit button, it should go to the next available row.
I have a copy of the back-end that gets a search key error 3709 on two records. In other words, I can duplicate the problem.
The interesting part is that I can update any other field on both these records and save the record, but when I try to change two specific fields, I get a Search Key Error and have to ESC out to continue (basically UNDO the change). Both fields are text fields with lengths of 7 characters and 255 characters, and both are COMBO Boxes on the form.
I tried to focus on the form think there was an issue in the code. I can definitely TRAP the 3709 error on the ON ERROR event on the form using "if dataerr = 3709", but then I tried something even simplier.
I went directly to the table and to each of the records. Again I can update any other field in the record but these two specific fields. When I try to change either of them and move to another record, you get a Search Key Error 3709.
By going to the table record directly I'm as low level as I can get. There are no validation rules on either field at the database level. If it was truly CORRUPT would it let me update any of the other fields on either of these records? One is an empid (not a primary key but is indexed with duplicates okay and not required), and one is status code (not a primary key but is indexed with duplicates okay and not required) so they're no critical fields, but something is keeping them from CHANGING.
Just tried something else; deleted the INDEXES on both the fields. Now it works! I am completely confused now because it really wasn't a corrupt record, but the indexes are causing the problem. Do I need to update the indexes somehow when the users selects a new empid or status code?
I have a product table that includes the name of the product, the quantity ordered, and the unit price. This is a summary table for the whole year showing all of the orders. The Primary Key is [ID] from the [tblOrders] table. I attached a screen shot of my query. I don't know if you can see it. I want to generate a report where each product is listed once, the unit price for that product is listed, the total number of that item that was ordered throughout the year, and finally, the total amount spent for each item during the year.
My network was using Access 2000 and Word 2000. We recently upgraded to Access 2002 and Word 2002, but now none of my merge forms are working. I don't get any error messages, and the merge function does open Word, but all I get is the original source document and not the actual merge information. I checked my VB references and nothing is glaringly missing. What's the deal?
I am new to Acess, I am trying to merge 2 Access DBs and am not sure of the steps to accomplish this, if it can be accomplished and/or are there any tools to reach the same goal.
Hi, I'm very new to access so sorry if this request seems a bit stupid.
I'm trying to get one csv file for a database of cars so I can upload it to my open-realty account.
In one table is the car ID and there are fields like Price, Extras, Mileage etc associated to that ID. (this is how I want everything to stay) I would like additional fields like Photo 1, photo 2, photo 3....photo 25 related to each car.
In the second table there are photos of the cars but each photo has its own unique ID but a field that relates to the ID of the car it is associated to.
How do I merge the two tables into one simpler version?
We are using an Access database for off-site data entry. We therefore have one master database we keep in the office and then a "template" version -which is identical to the master version except no records - we bring to each site, do the data entry and then merge that data back into the Master database.
Can anyone help suggest ways to make the data merging process more efficient. The database has approx 12 tables, so I dont' want to create an append query for each one every time. Is there a way to just "merge" two identical databases??
I am trying to import some data (job details for a machine shop), that is being exported from a CNC program.
The export has all the information we need, in a delimited format, but it spreads this over several lines. I also contains lots of junk we don't need.
Access imports this, but those lines then form an individual record in a tempory table.
I missunderstood what the 'UNION' query was, and whilst this has created nicely organised data, with just the stuff we need, it gives it over 3 individual records.
I either need to combine the 3 records from the output of the UNION query into a single record, or (and I suspect this is the case, as the UNION query doesn't actually do anything if I think about it), find a way of combining different fields from the temp import table into a single record in the query.
I've done a fair amount of searching on 'merging records', but can't seem to find a similar problem. Can anyone point me in the right direction?
Hello I need to merge a set of Access tables. I am creating a database which will hold a large amount of records (upto 250 000 in one table). I am importing the data from an excel spreadsheet. As each spreadsheet is limited to 65000 records I had to save the file in 4 different spreadheets. However in Access I need all these files saved in one table. Can anyone please help me?
Hi I have search the forums for an answer to this..i have 4 fields address 1, 2,3,4 and i would just like to all all the information to one field call address..how can i merge the information..please bear in mind i am useless at access..i thought there might be a way of copying the columns and just having a merge function to do so ..but cant find anything...please help..Williebear:(
Hello first post here, I need some help!Sorry not sure if this should go under queries or tables.Anyway I'm working with quite a large access db (64mb).I want to merge data from several tables into one table.Its a supermarket db (not a real one). Its got a customer and product tableIts also got a table called agg_c_special_sales_fact_1997with the fields customer_id and product_id this shows what customers bought what product.I want to merge the data from customer, product and agg_c_special_sales_fact_1997 into one big table. I want the merge to be permanent so I can see the which customer (and all their details) bought which product (all details) in one big table. I ve been trying to do this all day (im quite new to SQL) I copied the data in agg_c_special_sales_fact_1997 into a new table "new_sales_fact". I added a row called brand_name to that I want to copy from the product tableSo far I ve come up with this:SELECT product.brand_name INTO new_sales_fact FROM product, agg_c_special_sales_fact_1997WHERE product.product_id = agg_c_special_sales_fact_1997.product.id;unfortuantely access asks me enter a parameter value when I run the query.Could this becuase agg_c_special_sales_fact_1997 is huge, its got 86,000+records?Can anyone help? I'd be most greatful this has been driving me insane all day!!thanks in advance! P.S the database is the test database that comes with Mondrian......P.P.S I know its not good practice to have all data in one big table but for the purposes of what im doing it will simplfy things a lot!
I'm not really an Access programmer, but I maintain a couple of databases for my own use (music collection etc.). I hope no-one minds me posting here :)
I've made a bit of a mistake. I had two copies of a database on different machines, and I entered new data into both of them.
Is there any way of merging two tables, discarding any records which are identical?
