I am trying to merge data from multiple rows into one row for each unique ID. I can replicate on how to do this on a table, maybe a query or something like that. Attached img shows
View RepliesI have several tables I have brought in from excel spreadsheets. Each one has one common column (lets call it serial number), but then all the rest have a variety of information (one has location info, one has network info, one has asset tracking info, one has sw load info, etc). What I want to do is a) collect all the records from each table into one, b) merge the records with common key field so I don't have multiple records for this one key field and can have just one record with the information from all the different tables in it, c) not lose any records that don't have a sister record in some other database.
Anyone have any idea the best way of attacking this problem is. Common joins cause problems because of records that are not in both tables get left behind. Union and append queries creae many multiple records with the same ser # but only the bits of info they brought in from their original spreadsheet, not the other info from the other speadsheets. I am an Access Newbie and trying to figure out if this is even possible without extensive coding.
Thanks,
CORiverRat
I have several tables I have brought in from excel spreadsheets. Each one has one common column (lets call it serial number), but then all the rest have a variety of information (one has location info, one has network info, one has asset tracking info, one has sw load info, etc). What I want to do is a) collect all the records from each table into one, b) merge the records with common key field so I don't have multiple records for this one key field and can have just one record with the information from all the different tables in it, c) not lose any records that don't have a sister record in some other database.
Anyone have any idea the best way of attacking this problem is. Common joins cause problems because of records that are not in both tables get left behind. Union and append queries creae many multiple records with the same ser # but only the bits of info they brought in from their original spreadsheet, not the other info from the other speadsheets. I am an Access Newbie and trying to figure out if this is even possible without extensive coding.
Thanks,
CORiverRat
I want to count based on 1 (same) field from each of two tables, based on if ID is in "X".
select a.groupid, count(a.id), count(b.id)
from ta a, tb b
where value in ('a','b','c')
group by a.groupid
Thats sort of the psuedocode but Im not getting the right results.
Any help would be appreciated.
Hello:
I'm new to Access but not SQL, and am having difficulty coming up with a solution to the following problem:
I have one table for all Strings used in all other tables (to make Internationalization easier). The fields are: zId, zType, and zString. zId is an AutoNumber field. zType is a number which defines what type of string it is ( Item Name, Person Name, etc. ). zString is a text field with the actual text string (can be empty).
I have another table for Items, which have (among other things) Names (field name= zName). zName is actually a lookup into the Strings table and stores just the ID.
I want to create a form for people to enter a new Item. When creating a new item using this form, they will have to give it a Name, which will need to add a new entry into the Strings table with the appropriate type (ie, Item Name).
So, my question is "How to do the following..."
* on my form to enter Item Data, when a user goes to create a new Item, I must first create a new entry in the Strings table with the correct value for zType. Then, I must get the value for zId for this new entry and assign it to the zName field in the Items table for the new record that I am creating.
Any help is appreciated.
_C
So I have two tables,
table1: (company_name, company_code, year_month, rating)
table2: (company_name, company_code, year_month, asset, debt, equity,...)
What I would like to do is to call up all the data in table1, and then call up (asset, debt, equity,...) from table2 where the company_code and year-month are equal. What do you think is the best way to do this? Keep in mind a few things:
1. I'm dealing with hundreds of thousands of data lines
2. I may need to get more data similar to table2 in the future and call it up in the same way
I thought I should create a primary key "company_code-year_month"for both tables, but that method doesn't seem like a good one, and if I were to have another table, will have to create the same primary key for that table as well?
I have three tables, with the following data (fields separated by "-" here):
Unit - Customer Number - Customer Name - Type of A/R - Total A/R
Unit - Customer Number - Customer Name - Type of A/R - Total Overdue
Unit - Customer Number - Customer Name - Type of A/R - > 90 Day Balance
I would like to combine these tables into one table with the following field names:
Unit - Customer Number - Customer Name - Type of A/R - Balance
When I append the data, it comes out like this:
Unit - Customer Number - Customer Name - Type of A/R - Total A/R - Type of A/R - Total Overdue - Type of A/R - > 90 Day Balance
I would like this new table to include all rows from the original tables, sandwiched on top of each other. As far as I can work it, the Append Query only adds the data as add'l columns; not as add'l rows. I tried changing the field names entirely, so that the final field in each table is called "Balance." However, I still get the same result as above - the columns are added, instead of rows. I swear there's a way to do this, I just can't remember it!
Right now i have a qry that takes ID - Name - Order - RepeatOrder - Month - Department.
Now when i run the qry what i want is a grand total of Order/Repeat Order by Name for the Month, what is happening is the qry is creating multiple entries for different departments. I even have Department "Group by" but still causing the issue, For example:
For APR:
4/1/14 Dep A 12 2
4/1/14 Dep B 10 2
However i do not want them separated, how to i merge the data into 1 entry? 4/1/14 22 4?
I have an Employee Records table with 29k records that I originally imported from an HRIS system. Lets call this table ORIGINAL table.Each week I export a new Employee Records table from the HRIS system as it contains the latest updated information. IE - New hires, terminations, rate changes, etc. Lets call this table NEW table. It likely contains new records as well as we hire 100+ people per week.
In the ORIGINAL table, I have added fields that are not included in the NEW table. I need to update the records / fields in the ORIGINAL table with the NEW table, but need to keep the ADDED fields.Originally my plan was just to overwrite the ORIGINAL table with the NEW table until I added the additional fields to the ORIGINAL table. I thought that I could export the added fields in the ORIGINAL table, import the NEW table, and run and update query based on the EE ID#.
Hi guys ,
I'm very new to Access , but I need to do this and I can't figure it out. I have a form that looks like this :
http://img64.imageshack.us/img64/5566/formemail4qz.jpg (http://imageshack.us)
I'm trying to add up all the emails in a single string , but I can't access the records one after the other. The best I can do is display the currently selected record's adress ( The one with the black arrow in front ) . I tried browsing through the Fieldname.Value propertie ( in this case Me.Courriel.Value ) . I though I could cycle through all the Controls in the form , but Me.Controls.Count returns 3 , so I figured all the records must've been embedded into one control. Can anyone help me :( ?
(Sorry if this is a documented question , I've tried searching but couldn't find anything )
I am designing a bead ornament database for my mom to track inventory of beads, inventory of finished ornaments and cost/pricing. I have a table that joins the Item and bead part with the quantity of each bead part needed, it has the following fields: ItemMatlID, ItemID, BeadPartID, QtySo I have multiple rows of ItemIDs for all the BeadPartID & Qtys.Now I need a query with 1 line per Item and all BeadParts and their quantities. However, I need the BeadPart to be a row data and not a column heading. So a crosstab is out, I believe since it wants to make the BeadPart the column heading and not data in the query.
View 7 Replies View RelatedDear Friends,:)
I have 2 tables .. (GroupNo.) & (employee)
The conjunction between them as :
tbl1 tbl2
--------------------------
No. Name
--------------------------
1 John
1 Alian
1 Sali
2 Robert
2 ......
...
..
.
---------------------------
How Can I make a new Query that produce the result as :
1 John,Alian,Sali
2 Robert,.......
3 ......
Kind Regards,
I use following function to concatenate data from multiple rows into a single row/field. It puts a comma ',' after each concatenate. What I would like it to do more is to put 'and' instead of comma between the last two concatenated words. For example: Now it gives me Value 1 but I want to get Value 2.
Value 1:
FormRegNo CombinedParticipants
DC-190 Political parties, CSOs, community residents
Value 2:
FormRegNo CombinedParticipants
DC-190 Political parties, CSOs and community residents
Function:
Option Compare Database
Option Explicit
Public Function ConcatField(strSQL As String) As String
Dim dbs As Database
Dim rst As DAO.Recordset
Dim strConcat As String
[Code] ....
I am creating a query to use in a Word 2007 mail merge.
For simplicity, let's say the query has three fields:
schname for school name, role for staff role, and name for the staff member's name.
The current query shows:
SchoolA, Admin, Jerry Garcia
SchoolA, President, Bob Hope
SchoolB, Admin, Janet Jackson
SchoolB, President, Mae West
I would like a query that would show one line per school like so:
SchoolA, Jerry Garcia, Bob Hope
SchoolB, Janet Jackson, Mae West
I've looked at Allen Browne's ConCatRelated function and can pull up a list of all names in a field called Expr1001, but that doesn't seem to be what I need.
I have imported 3 different excel worksheets into Access. I then created a query in which I made some relationships and select the fields that I need to make a new table. The problem is, when I run the table the final product is missing about 20,000 rows of data. I have used this protocol successfully for other other files, but for some reason, with this dataset Access is pasting 10,000 rows into the new table, where the original file has 39,000.
View 1 Replies View RelatedI would like to know which way is the best way to import excel data from multiple sheets in to multiple tables in access.
For example data from Sheet1 -> Table1, Sheet2->Table2, Sheet3->Table3 etc...
I have tried using this:
Cmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, "Table1", "C:Importedfile.xlsx", True, "Sheet1!"
Ironically, data from Sheet2 and Sheet3 seem to be properly imported in to table2 and table3, but some of the data from Sheet1 seems to be missing in Table1 after import.
Any other ways to import the data?
I have a database to handle both purchase orders and customer orders,
My problem is with the products.
there are six tables all with identical fields, but with different data in each, I need to be able to merge these six table into one and allow the original tables to be updated, inturn updating the merged table.
any help would be great.
Hi All,
I have designed a form that holds data from a table called Purchase, it also has got 3 more fields that are coming from another table called Master. These 3 fields, Code No, PartNo and Description are same in both tables. All i need is a way to bring the data of the stock table on purchase form. As soon as i enter code number in code field, it should check in stock table if the same code exist, if yes then it should bring the all 3 fields in purchase form and display.
Hope someone out there will return my call.
Warm Regards,
Darno
I have a form, created from a query. THe query contains an links about six tables using an AutoNumber ID.
OK, so when users enter their data into this form, I would like the autoincremented number to increase in each of these tables. The catch is that when the user enters data he/she does not have to enter data into every field.
So, some of my tables do not auto-increment and some do. How should I go about resolving this so that when someone enters new data, each table's ID is autoincremented no matter if that table contains information immediately or not? Please Help.:confused:
I haven't used access for a long time so I am very rusty.
I have a few tables but I'll just use two for an example:
Table A
Ticket No
Part No
Defect Code
WorkCenter No
Clock No
(Ticket No is Primary Key)
Table B
Clock No
Supervisor
Employee
(Clock No is Primary Key)
Is there a way for me to create a form, that when Clock No is entered it will be placed in both tables?
Thanks in advance.
hi, i am new to access and struggling so any help would be great...
i have a db with 3 tables. students contains a Primary Key ('studentid') and then 'firstname' and 'lastname' fields. The two other tables (contactdetails, coursedetails) have 'studentid' as a Primary Key and then they each have other fields.
I set up relationships between the tables (1 to 1) but i would now like to create a form that will allow me to enter data to all three tables at the same time. (this would be ideal as it keeps the data normalised) if this isn't possible i would like to create a form that adds the 'studentid' record to each of the tables.
let me know if you need anymore details but if someone can point me in the right direction it would be cool.
cheers
I am a new user to access and are therefore seeking the assistance of a more experienced user (I'm sure this will be an easy one for all out there).
I want to be able to get records from different tables after the table name has been chosen.
In other words and to be a bit more specific, at the moment in one table I am selecting the tables I want to get information from. Once I select the tables I need to then be able to extract the required data from the selected tables and then put it into a new table with summary totals.
K,
Maybe a noob question, but I'm still learning access (I know a little late at the age of 37, but better late then never).
I have a form with several tabs, linked to a table with employee information, works like a charm.
Now, my last tab is called settings, maybe no need to say that the information from this tab needs to be called from a different table called settings.
Certain information, like version number, department, etc I want to display all the time in the header and I want to be able to manipulate that information through the settings tab.
I definately don't want any relationships with the employee table.
Is there a simple way to achieve this, I tried drag and drop but this leads to errors, so definately I do something wrong here.
Much appreciated,
Hi!
First of all, I'm kind of a newbie to all of this, but not entirely, bare this in mind please ;-) Also I'm from the Netherlands and my english might be a bit rusty, so bear with me if you please.... :-)
I'm having a problem for which I cannot seem to find a solution. Below I've outlined the situation:
I have a couple of tables up to now:
CustomerTable
CaseDateTimeTable
CaseTechInfoTable
CustomerTable has got a primary key: Clientnumber
CaseDateTimeTable has primary key: Casenumber
CaseTechInfoTable has no primary keys.
CustomerTable only includes (apart from all adress info etc) the clientnumber field which is of importance in this case i think.
CaseDateTimeTable includes both Casenumber and clientnumber.
CaseTechInfoTable also includes both Casenumber and clientnumber. Only both other tables have Primary keys, and this one doesn't.
I've created a relation between CustomerTable - Clientnumber and CaseDateTimeTable - Clientnumber.
Another relation I've made is between CaseDateTimeTable - casenumber and CaseTechInfo - casenumber.
Now, I have made a form in which the name and adress data is fed into the database, in normal form view. Below these textboxes etc. I've created a SubForm in datasheet view in which the existing cases from the at that time selected/displayed customer should be displayed. At the moment only fields from the CaseDateTimeTable can be displayed, but I would also like to display some fields from, for example, CaseTechInfoTable in that same subform.
But how?
When I create a (sub)form via the wizard and for example select:
CaseDateTimeTable - clientnumber
CaseDateTimeTable - Casenumber
CaseTechInfoTable - Problem description
and display these fields in the subform as a datasheet, then the separate cases from each customer are not displayed. I have 5 clients in my test dbase, just about all of them have 2 or 3 cases associated with them. In the example above only the data from two clients is displayed and then only from 1 case each, not the actual amount of cases. So for example:
clientnumber 4 has casenumber 5 and casenumber 6. But only casenumber 5 is displayed. The same happens for clientnumber 2, he has casenumbers 9 and 3 but only casenumber 9 is displayed.
The strange thing is though, that when I create the same (sub)form in datasheet view, but only use fields from the CaseDateTimeTable all cases per client are nicely displayed in the subform.
Why? And how do I solve this problem? I need to solve it so that I can add data from other tables into the subform later on so a nice overview of certain important data can be given per customer per case, even before the case form(s) is/are opened.
I've tried making a query and base a form on that, but for some reason I can't get the query to display all my customers, let alone all cases associated per customer. In the past I have made another (smaller and simpler) database for which I also made a lot of queries. But now? Pff... I'm lost at the moment.
Anyone?
I know this is possible (e.g. opendatasource) but search as I might I can't get the exact code.
Scenario: Multiple front ends, in different folders, linked to single back end.
When mail merging, a temp table is created in FE - thereby different source for each user.
The Word doct is linked to a source (via Mail Merge) and I want to change that source according to the logon of the user, which is held on an open form.
Ideally like:
Case Mary use C: Db1 emptable
Case Tom use C:Db2 emptable
I am using mail merge to produce reports for lift inspections and it is all going well apart from the fields that are multi value do not quite transfer. For instance the "inspections completed" field has a list of the months Jan-Dec and therefore a visit may have been completed in Jan, Mar & Jul for example.In word the first two letter of the last month are transferred across followed by Chinese symbols.
View 1 Replies View Related