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 table
Its also got a table called
agg_c_special_sales_fact_1997
with 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 table
So far I ve come up with this:
SELECT product.brand_name INTO new_sales_fact
FROM product, agg_c_special_sales_fact_1997
WHERE 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!
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?
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?
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?
Hi all :) I have 22 tables in my Access database, which is my catalogue data. I have it set up on dreamweaver. Now tho, i need a search engine going thro it, i have a script in access/dreamweaver that works. Only thing is, that the script only works thro one table. Now, is there some way to merge tables into one? Not just copy and paste, but all have the same specific name that i maybe able to link? (ie Name, description, code) Any ideas? A tutorial link would be great if anyone has come across a similar thing, thx!
I have a few tables with the same row and column headings but contain different data in their cells. Is there a query whereby I can basically merge everything to give me one view with everything in it?
I'm an access newbie and was wondering how to merge two tables. for example, i have:
table1 col1 col2 col3
and
table2 col1 col2 col3
They have the same column names, I just want to put the data from the first table in the second one. I don't care about duplicates for the moment, there is no primary key.
Hi, i am required to merge 2 tables and to be able to present the data out in a report. the prob is i do not noe how. both the tables do not haf an unique field which means all entries in each field are repeated. the size of each table is different. is it a must for the 2 tables to be the same size?the fields for the 1st table are as follows:Country, Company Code, Company, Product Group, Material, Date, Actual Qty and Final Forecast. the fields for the other table are: Country, Company Code, Company, Product Group, Material, Date, Sales adjusted forecasted and final forecast. the "date" field from each table are different. the 1st table will show the actual qty of goods delivered while the latter will show the forecasted qty. i am required to be able to extract out the data for eg to show the actual qty delivered from jan to jul and the forecasted value from aug to dec. pls help....
Ok, I have a problem. I have two tables with different data pulled from two different areas, but that have the same type of information in them. I need them merged into one query or table so it can be exported into excel.
The problem is that one table has data only once (has a primary key, then things by building and grade). The other table has it's primary key, but then could have more then one building and grade combination (i.e. building 1050 grade 8 membership of 10 more then once becuase off another factor).
I need to merge this stuff by query of some kind into another query or table that totals all things with the same primary key, building and grade. So there is only one line for xxx-xxx building 1050 grade 8 with the grand total of membership for all.
I have data that splite by month, so january data separated from february data, an so on. can i display those data on one table by using query? if yes.how can i do that?
I've got some tables I pulled off GeoNames.org that I need to extract data from. One table has everything I need except the counties listed in it. Instead of that, it gives a county code. The other table has the actual county names associated with those codes.In the main table, country and state are listed similar to "US" and "WA", representing U.S.A. and Washington state, with the city name ("Seattle") also displayed in a separate field. A county may be displayed as a county code of 003 or 101, or any such 3-digit number. The thing is, multiple counties can have the same county code, since each state's counties start their numbering over at 001, 003, 005, etc.In the county table, one column has the country, state, and county code in one field similar to US.WA.003, and the county name ("King County") in another field.What I need to do with this info is create a new table that will have only the country, state, county, and city names (omitting the text "County", if possible). So, my final result would be: US, WA, King, SeattleI just don't know how to pull the data from each table while merging the county information.The reason I haven't included the examples of the tables I'm using is because I'm hoping to be able to put together the query myself, with the help you all can provide me with. Alot of times, there are well-intentioned people who are kind enough to layout everything exactly as you need it to come up with finished results, and I'd just like to get the opportunity to take a shot at it myself first. If I can't get it from there, believe me, I'll be back for more help.Thanks in advance.
I've currently come across the issue where an end user copied the database locally to their computer and has been using it for months and months. After fixing an issue in the database on the server and then finding that someone still had a database bug is how I figured this out. Anyway, what would be the best way for me to merge the table 1 from the database she was using to table 1 in the database on the server? Is there a way to export a table so I can then import the data? Like I said, the databases are identical other then the data.
Hi all, im hoping someone can help. This is the problem.
I have 3 tables that have tax payer information for 1 year broken up into 4 months, 1 table for each 4 month chunk. all 3 tables mostly have the same taxpayers, but each table has payers that are not in 1 or both of the other tables. each table has a 2 part primary key, the business number is the first part, and branch number for businesses with chains. the next 4 fields are each month of taxes paid. heres the design of each table.
is there an easy way to merge all of this data into 1 table easily. ive tried using make table or append queries, but it only addes the information where the IDs are in each table. this leaves out the ids that are in only 1 or 2 tables.
if you need me to clarify or explain anything else, please let me know. i can post screenies or the design, but obviously, not the data.
Newbie here, i have two tables with 3 fields in each (code, Description and amount) what i need to do is join these two together to show data from both tables (some of the data in fields one and two will be identical, but the third fields in both tables will need separate entries for each table) the primary key would be the first field (code) ysee, the original table has been updated by a n other, some codes and descriptions changed for others so, i would like to end up with feild one, all codes from both tables, field two, all descriptions matched to codes from both tables, field three numbers matching from first table, field four numbers matching from second table.
I am trying to update an existing table in Access with current information that I have imported into Access from excel into a separate table. I was able to use an unmatching query and append all records that were not already in the original table. But there are records in the update table that match the original table, and there in information in fields within that records have new information.I need to find the records that match in the two tables and update the missing data.I am able to create a query that displays the matching records but when I run it as an update query the information in not entered into the original table.
I'm in the process of creating a database to track campaign contributions, and I'm kind of stuck.
I've created tables for the citizens, the candidates, and the contributions to the candidates. Now I'm trying to populate them with an Excel spreadsheet. The problem is I have no clue on how to split the Excel file so the appropriate parts go into each table.
The spreadsheet contains: Name, address, etc. - This needs to go into the Citizens table Names of candidates individuals contributed to - This needs to go into the candidates table. Dollar amounts and dates of contributions - This needs to go into the contributions table.
Each citizen may have made multiple contributions to multiple candidates.
The easy way would be for everything to be in one table, but that would be a bad database design, right? Here's a shortened version of how my tables are designed:
Citizens: Name, contact info, etc. of citizens Candidates: Name, party affiliation, etc. of candidates Contributions: Candidate (fk is pk of Candidate table), Citizen (fk is pk of citizen table), contribution date, and contribution amount.
I appreciate the assistance, as I'm getting really frustrated.
I have 5 copies of a database that 5 users enter data into (including Pre, Post and Follow-Up information). These users send me their database quarterly and I am suppose to merge the data into one database from which to run reports, etc.
The problem comes in with the auto-numbering. Each "Pre" table has an ID that is auto-numbered. This auto-number is also stored when a corresponding "Post" (or follow-up form) is entered so that we can be sure the pre-, post- and follow-up are all linked to the same individual. When I merge the data, they are reassigned ID numbers in the Pre (while the Post still has the original Pre ID number) so now I've lost the link as to one individual's pre, post and follow-up form.
I realize now that the database shouldn't have been built this way, but is there any way to work around this. I've even tried exporting all of the rows for each of the 5 users into Excel and then importing them into Access in a new table (just so I could have the ease of running queries) but I seem to be getting errors doing that as well.
I have 5 copies of a database that 5 users enter data into (including Pre, Post and Follow-Up information). These users send me their database quarterly and I am suppose to merge the data into one database from which to run reports, etc.
The problem comes in with the auto-numbering. Each "Pre" table has an ID that is auto-numbered. This auto-number is also stored when a corresponding "Post" (or follow-up form) is entered so that we can be sure the pre-, post- and follow-up are all linked to the same individual. When I merge the data, they are reassigned ID numbers in the Pre (while the Post still has the original Pre ID number) so now I've lost the link as to one individual's pre, post and follow-up form.
I realize now that the database shouldn't have been built this way, but is there any way to work around this. I've even tried exporting all of the rows for each of the 5 users into Excel and then importing them into Access in a new table (just so I could have the ease of running queries) but I seem to be getting errors doing that as well.
Hi all, I'm not an Access programmer by trade, but unfortunately got handed a project that nobody else wanted.
I have a small database taking shape which will track the auditing of paperwork/files. Each file has a unique ID associated with it, in this case, an account number. Audits will take place by selecting the job area (department), then the type of file (Line, Loan, Increase). This will be used to sort which type of audit questions are asked.
I have the following tables created:
tblQuestions: ID, Question, Product question applies to, Function question applies to, Comment field, and result field. tblTeamMember: MemberID, TeamMember, Manager, PrimaryFunction tblAuditResult: AuditID, MemberID, AccountNumber, Question, Result, Comment, Date of Audit tblProduct: List of product types (line, loan, increase) tblFunction: list of job functions
Currently a form is displayed so the manager can select the job function, then the file type. This will display:
frmAuditCreate - this form contains the fields: txtAccount, cmbManager, cmbTeamMember, txtAuditDate Sub form on frmAuditCreate - frmQuestionSub - this form contains the fields: Question, Comment, Result
The frmQuestionSub is built off a query which uses the function and product values to determine which questions to filter and display.
Now, once the user is done flipping through each question in the sub form and input the data into the comment and result field associated with that question, I need that information along with the information on the frmAuditCreate to be stored in the table tblAuditResult. This is the problem I'm having difficulty with. I can't find a way to copy the question, comment, and result and insert it into the same record as the rest of the data.
Unfortunatlly I can't strip the database down enough to meet the 100k or less attachment limit.
We are a non-profit that does blind mailings for our membership drive. The company who we buy names and addresses from sends us a delimited file that has these fields as the headings
" ID, FULLNAME,COMPANY, ADR1, ADR2, CITY, STATE, ZIP, FIPS"
Once they send out the mailings, people then send in back a remit slip with a contribution that gets scanned through a program that creates a file that gives us these titles
"ALT ID, AMOUNT PAID, RUN DATE, TENDER, FUND, PURPOSE, SOLICITATION, MEMBERSHIP QUESTION, MEMBER TYPE, CONSTITUENT TYPE, SEGMENT"
The "ALT ID" and "ID" are the same in both tables.
I need to find a way to merge the tables and combine the fields that have the same ID # , and then have it create a csv file that reads like this (see below) for only the files of the people that responded so that I can import it into our membership software.
...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 need to merge two tables together, but not create new rows of data.So for example, I will have a table provided to me with the following information for one entry: Individual ID number (table key) and the Individual's Name.
I will then need to populate the unknown data (the Individual's address and contact information) from a master dataset (another table in Access), based upon a matching Individual ID number.Is there a way to complete the Individual's missing data, rather than creating new rows?
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.
