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:(
View RepliesI'm doing up a database of hazardous chemicals, based on publicly available information from various agencies. Each chemical has a name and a number, and has different hazards associated to it. The hazards are grouped into classes, and for each class the hazard is further broken down into categories. Hence for an explosive hazard (the class) I could have categories 1, 2 and 3 based on severity. For each iteration of the hazard class and category (for example explosive hazard category 1), there is a non-unique hazard statement. Another complication is that there is a qualitative element in the classification system, and hence agencies from different countries could classify the same chemical differently. This should be captured into the database as well. Thus for every chemical, I will have to enter the Japanese classification, the EU classification, etc.
I've managed to set up what I think is a correct relationship, based on my understanding of databases. However when I generate the report, fields such as the chemical name and ID are repeated for each hazard. I would like to merge these redundant entries, so the chemical name, I.D., and any other repeated fields appear only once. In essence, I'm trying to make it such that there is only one entry for each chemical+I.D/country, with multiple hazard subentries, rather than having multiple, seemingly duplicate entries.
Hi everyone,
I can't get my head around this so I'm looking for some help if possible please, there are two questions, the first is:
When in the query, I want the criteria for the date selection to be a question, ie. "[Week Start Date?]" but I want the actual criteria selection to be from the start date plus 5 days, the only way I've done that so far is to do ">=[From?] and <=[To?]", which uses two questions and I don't seem to be able to do ">=[Week Start Date?] and <=[Week Start Date?]+5" which seems basically correct, but I expect I'm writing it incorrectly (basic access knowledge I'm afraid :( )
The second question (after I've got the 5 day date criteria sorted) is that the query produces a table that shows basically the following:
Name Store Date Visited
Tom Bury 18/01/06
Tom Bury 19/01/06
Tom Diss 20/01/06
Dick Thetford 18/01/06
Harry Diss 20/01/06
The query is based on a part week period with the starting date ALWAYS a Monday, so no more than Mon-Sat will appear, I want to put the information into a table or query, so the result ends up as:
Name Store Mon Tue Wed Thu Fri Sat
Tom Bury 18/01/06 19/01/06 'Blank' 'Blank' 'Blank' 'Blank'
Tom Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'
Dick Thetford 18/01/06 'Blank' 'Blank' 'Blank' 'Blank' 'Blank'
Harry Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'
*Where the blanks are simply left empty, rather than putting in the word 'blank'
Essentially converting the "[From?]" (as stated earlier) or "[Week Start Date?]" to Monday, that date + 1 to Tuesday, etc, BUT putting multiple dates relating to name and store criteria onto one record :eek:
Beyond me I'm afraid, any pointers would be seriously appreciated, I expect I'm approaching the problem from the wrong angle.
Regards
Tony
Hi everyone,
I can't get my head around this so I'm looking for some help if possible please, there are two questions, the first is:
When in the query, I want the criteria for the date selection to be a question, ie. "[Week Start Date?]" but I want the actual criteria selection to be from the start date plus 5 days, the only way I've done that so far is to do ">=[From?] and <=[To?]", which uses two questions and I don't seem to be able to do ">=[Week Start Date?] and <=[Week Start Date?]+5" which seems basically correct, but I expect I'm writing it incorrectly (basic access knowledge I'm afraid :( )
The second question (after I've got the 5 day date criteria sorted) is that the query produces a table that shows basically the following:
Name Store Date Visited
Tom Bury 18/01/06
Tom Bury 19/01/06
Tom Diss 20/01/06
Dick Thetford 18/01/06
Harry Diss 20/01/06
The query is based on a part week period with the starting date ALWAYS a Monday, so no more than Mon-Sat will appear, I want to put the information into a table or query, so the result ends up as:
Name Store Mon Tue Wed Thu Fri Sat
Tom Bury 18/01/06 19/01/06 'Blank' 'Blank' 'Blank' 'Blank'
Tom Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'
Dick Thetford 18/01/06 'Blank' 'Blank' 'Blank' 'Blank' 'Blank'
Harry Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'
*Where the blanks are simply left empty, rather than putting in the word 'blank'
Essentially converting the "[From?]" (as stated earlier) or "[Week Start Date?]" to Monday, that date + 1 to Tuesday, etc, BUT putting multiple dates relating to name and store criteria onto one record :eek:
Beyond me I'm afraid, any pointers would be seriously appreciated, I expect I'm approaching the problem from the wrong angle.
Regards
Tony
I really never use Access but a client gave me a database with a couple thousand records in it. Two columns were "Area Code" and "Home Number". What I have to do is merge those two so that the area code and number appear in a single column. How do I do this?
View 6 Replies View RelatedI have two column of expressions
1=(<(Now()+365)-([Service Interval]*30) Or <(Now()*365)-(([Service Interval]*2)*30))
2= (<(Now()+365)-(([Service Interval]*2)*30) Or <(Now()*365)-(([Service Interval]*2)*30))
I want to merge these two columns into one but then have two entry's not one !
And then I can sort by date in a report (i am trying to sort all service due for the customer in the next year)...
I was able to use the UNION ALL qry. But, when I have another file (like original2) that does NOT have all the columns listed in the UNION ALL qry, I get a Parameter value box asking for the missing columns when I run the qry.
Example:
original1IDDateGroupChristianJohnnySteve 18/5/2013A1528/5/2013B338/5/2013C2348/5/2013D2358/5/2013E5
original 2IDDateGroupChristianJohnny18/6/2013A212528/6/2013B2338/6/2013C2248/6/2013D22
The UNION ALL qry includes all the possible resources ( includes all the possible column fields Christan, Johnny, and Steve).
When I run the UNION ALL qry with the original2 file, An "Enter Parameter Value" box is displayed with the mssing column name "Steve".
Is there a way to Map the original2 table into a working table with all the columns, or use VBA code to construct the UNION ALL qry to only include the existing columns? My data has variable columns and I'm trying to avoid the parameter popups.
I have a MS ACCESS 2010 database with a data table which i am trying to create a query from. I have 6 columns of data( one with an ID Field and 5 Name Fields). Below i have made examples of how it first appears as a simple query and the second will show you what i would like it to look like.
What the simple query looks like: [URL] ...
Second what I want the query to look like: [URL] ....
Currently I'm building tables and forms. My first table (called Clients) lists the details of fictional clients. My second table is for invoices.
In my invoices table, I wish to link the column for client reference (note: stored in the Clients table) to the column that precedes it. This column will list the clients’ names and is selected from a drop down list that is linked to the Clients table.
What I want to do (if its possible) is to have the respective client ref. automatically show up in the next cell once I've selected the client to whom the invoice relates?
Am I making sense? Is that possible? If so, how do I do it?
Secondly, how do I do a sum of selected columns for my “totals” column? Basically, I want to add the figures found in several cells that precede it?
I'm affraid my confusing topic title is an indicator of how confused I am by this. I can't even understand the variables well enough to fully utilize Access Help or the Search function here...
What I have is a database hat has column headers that look something like this:
Customer_Name, Order_Date, Qty_Ord, Unit_Price, Total_Price
What I'm trying to get is a query output that will have
Customer_Name, Total Orders (in Dollars) for January, Total Orders (in Dollars) for February, Total Orders (in Dollars) for March, etc.
I've been able to set it up to SUM for one month, but not multiples.
I know I'm totally lame (for proof read any of my previous posts) but you guys totally bailed me out the other time I asked a lame question.
Thanks in advance!
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?
MDW
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.
View 5 Replies View RelatedHi, 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?
I've attached screen shots of the two tables.
Thanks for any help.
Hello-
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??
THANKS
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?
Thanks
Nathan
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!
View 4 Replies View RelatedHello all
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?
Many thanks in advance for any responses!
Cheers
Gareth
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!
View 1 Replies View RelatedI have frequented this board on several occasions, and have used it extensively to help me on my project. But I am at a situation that I cannot seem to find a proper solution for, and feel like I need to solicit some input from more experienced people.
Problem: What I want to do is create a form utilizing two queries that will not only display the shoes and comments, but as I tab from record to record (each record a different shoe), the form will also display the total minutes for the shoe. (frmShoes in the database shows what I ultimately want)
Background: I am creating a database to track my exercise routine and jogging times, along with other variables. I have three tables (I deleted all the other extraneous garbage to alleviate any potential for confusion):
tblRunSession
Run Session ID pk
Shoes ID
-one-to-many w/tblLaps-
-one-to-one w/tblShoes-
tblLaps
Laps ID pk
Lap Number
Lap Time
Run Session ID fk
tblShoes
Shoe ID pk
Shoe Name
Comments
I want to use a form built off tblRunSession to enter a new day of running. On the form I would enter multiple laps, and minutes each lap was completed, along with the shoes (only one pair used per day) I wore that day. I accomplished the input utilizing tblLaps as a subform to tblRunSession.
I tried creating two queries: one (Query 1) from tblshoes, and the other (Query 2) from tblRunSession and tblLaps. I summed the individual lap times in Query 2 for each shoe. But when I try to create a third query based on Queries 1 and 2, it gets all messed up and confusing. I tried to use dlookup, but I quickly found that was beyond my knowledge level. I think some of the problem I’m experiencing (besides my limited brain capacity) is the fact that I am trying to use three tables to pull data from, and trying to make it all mesh. I’m sure it can be done, but I’m at a loss.
I attached what I have been working on in case anyone wants to look at it and set me straight. Thanks in advance for any suggestions or ideas anyone has to offer.
Hi,
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?
Thanks
Greg.
Hi, this is really puzzleing me!
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;
Should produce the following:
ThisQuery
yy
It dosent! Can anybody help?
I have an excell spreadsheet with member names
the member names are duplicated up to 6 times each depending on how many months from october - march they have been members.
The excellspreadsheet looks like this
Member name Member number October care level November care....
triscuit83 1234 1
Triscuit83 1234 1
Is there a way in access I could make triscuit 83 show up once and have all the care levels stay on the same line.
Please let me know if there are any clarifications needed.
I need help hardcore
****edit****** the example doesnt show up formated right basically 1 will be in the october column the first time and a 1 will be in the november column the second line.
Hi,
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.
Thanks
I have 2 form, I want to merge them into one. they do different things but they interconnect with each other in one field.
for example one form has customers, the other form is sales.
I want to merge them so I could see what the customer has bought over time.
any suggestions, it is just I cannot get them sincronized with each other.
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....