SELECT IIf((Count([PO]))>1,1,Count([PO])) AS PO_Num, IIf((Count([PO]))>1,'Multiple',[Category2]) AS Cat
FROM [Final]
GROUP BY [Final].Category2, [Final].[PO];
k so stage 1 seems to work, all the PO's that are multiples for the one category have been grouped and their value set to 1... but there are PO's with the same number pointing to another category.
I need to "simply" merge those PO's to be just one line_item.
Apologies ... but I am quite new to Access. My database contains about 1000 peoples' addresses.
The problem I have is that I need to merge, say, 20 of these addresses into a word document. In the word document I can select records using the "Surname" field but it will not give me the option to select more than 6. I am sure there must be a way that I can select more than 6: I have tried separating the surnames with a "," or "or" but nothing seems to work!
I'm trying to determine the SQL to return only those records in a table which have duplicate values in each of two fields, but different values in a third field. Here's an example:
Code:
AcctNum FoodType FoodClass ------- -------- --------- A123 Apple Fruit A123 Apple Fruit A123 Grape Fruit A456 Potato Vegetable A456 Potato Perishable A789 Carrot Vegetable A001 Banana Fruit
For the above table, I'm trying to return records which have multiple entries for AcctNum + FoodType, but DIFFERENT values for FoodClass. So for the above table, the query would return:
It returns these two records because there is more than one record with for the AcctNum + FoodType (i.e. 'A456' + 'Potato'), but DIFFERENT values for FoodClass (i.e. one record has 'Vegetable' while the other has 'Perishable').
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?
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 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.
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?
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;
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.
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....
Once again, I'm working with a file that I got from somewhere else that isn't in *quite* the right format.
What I have is a table listing all of the titles and their holdings we have in various journal databases. The table looks something like this:
|____TITLE____|___COVERAGE______|____DATABASE___| |_ABC Journal_|_1998 until 2000_|_EBSCOhost DB__| |_ABC Journal_|_1953 until 1986_|_Proquest DB___| |_ABC Journal_|_1980 until 2006_|_Free Journals_| |_XYZ Journal_|_1899 until 1956_|_CINAHL DB_____| |_XYZ Journal_|_2000 until 2006_|_EBSCOhost DB__| |_XYZ Journal_|_1955 until 1999_|_Proquest DB___|
The titles, as you can see, are listed more than once, with one record for each holdings record from each database where that title is found. For my purposes, I need the holdings to show up all together. I can do this in a report, by sorting by the title, but I really need to have it all together in its own cell--one line per title. The above table should look more like this: |____TITLE____|_______________________COVERAGE____ _________________| |_ABC Journal_|_1998 until 2000_,_1953 until 1986_,_1980 until 2006_| |_XYZ Journal_|_1899 until 1956_,_2000 until 2006_,_1955 until 1999_|
I don't really care what happens to the database field, but if it helps, the title and the database fields together would be a unique combination in the table.
Currently, I am working on an exported version of this table in excel, merging the cells 2 by 2 with a function command like this: =A2&" "&B2. Since there are 32366 records, I hope there is a quicker way. At this rate, I'm not sure if I'll be done for 100 years.
Ok, this is only a very general question, but I'm hoping somebody here can give me some general advice.
Basically, I have three separate database which I've written for my company. They were all written at different times, hence the fact there are three and not one that does three things. The first is used to raise, edit, delete, and track RMA records for returned merchandise. The second is used to raise, edit, and track Training data for people who have undergone my training course. The third is my main database that contains all of my clients' details, plus a log of all calls taken from them. This is the largest and most complex of the databases and has some fairly advanced relationships setup to enabled hundreds of call logs to be linked to a single record of necessary.
Now all three databases have been written using the same kind of structure. They all use popup modal forms to prevent malicious/accidental damage to the database by the staff who use them. They are all locked down and Access cannot be broken into whilst they are running. they all also have their own primary keys.
All these databases work just fine, but I'm now a little tired of each staff member having three shortcuts on their desktops, depending on which database they need to use. what I'd really like to do is somehow merge all three of these databases into one single database, and alter the switchboard accordingly to allow users to access any of the three parts of it.
The problems - as I see them - are: (a) that my main database uses a lot of relationship links to maintain certain records, whilst the other two use none, and, (b) that each database, having been written separately, has records with their own ID keys and autonumbers.
I did toy with the idea of having some kind of function on my main switchboard that would allow me to close the current database being used and open another, all without closing Access, but I haven't been too successful in my tests...
...so, my question is, does anybody have any bright ideas as to how I can go about merging these three, or, at very least, centralising access to all three?
I have been trying to move some of my employer's database information from Excel to Access. The fields are simple stuff, first name, last name, address, etc. My only problem is the Amount Owed section in which I would have to put amounts such as 1270.70. In Access, I formatted the field as currency with 2 decimal places. Thus, it shows up as $1270.70 in the Access database.
However, when I mail merge the field to the letter, I only see 1270.7. I could not seem to make that last 0 appear. How could I make the 0 appear?
Further, as I have said, I have been moving records from Excel to Access. For mail merge purposes, would you recommend Excel or Access?
