Consolidating Tables

Aug 20, 2007

hi,

I have a database that includes 5 uploads of GL data - once in access I want to consolidate these into one table in order to query. All headings are the same (the reason for the five uploads is restricted amount of lines that can be downloaded in Excel).

Can anyone tell me how i do this?

rgds

jim

View Replies


ADVERTISEMENT

Tables :: Consolidating Multiple Tables In One Single Table (Sorted Ascending By Time)

May 30, 2014

What I have is a database setup with multiple tables in which different areas of my DC can input information simultaneously into their respective tables. I then have another database linked to it for myself to have a live view of each updated record. I would like to see all the records of each table in 1 single table (possibly just sorted ascending by time). Each table has the same Field headings but may have different qtys of records. As I will then have it linked to an Excel table to VLOOKUP from it.

I have tried Union coding but always get Syntax Error etc.

View 8 Replies View Related

Tables :: Consolidating Duplicate Values Without Losing Other Information

Jun 11, 2013

I've got a table which lists accounts with an associated charge. If the account has multiple charges, then the account appears multiple times, for each charge. I'd like to consolidate the duplicates without losing each charge that is associated with the account.

View 4 Replies View Related

Consolidating Records

May 24, 2006

I have a table that contains specific customer transaction data. In this table there are the following fields:

Document
Vendor
Vendor Name
Units
Value

There are instances in which multiple records could exist for one document number. Such as:

Document-----Vendor-------Vendor Name------Units------Value--------
12345678------AAAA--------ABC Co.-------100------200.00
12345678------BBBB---------BBC Co.--------25-------50.00
12345678------CCCC----------CCC Co.--------10-------20.00

What I am attempting to do is, sum the units & value fields and report the data on one line with the vendor record with the > unit count. So, for the example above, the data returned would be:

Document-----Vendor-------Vendor Name------Units------Value--------
12345678------AAAA--------ABC Co.-------135------270.00

Can this be performed in one single query vs multiple queries. If so, any guidance would be welcomed.
Thx

View 1 Replies View Related

Consolidating Fields

Nov 9, 2007

I have a table where there are one person can have multiple records. I
would like to consolidate the each clients records to one field with
an additional field showing the total of all the clients invoices. Is
that possible?

Sample:


Invoice# Client invoice_amt
12345 X $5
45767 X $8
89787 X $6


My desired result


Client invoice_total
X $19


Is this possible?

View 2 Replies View Related

Queries :: Consolidating Records In From In Same Table

Jan 31, 2014

I have a table with the following

Code:
fname,lname,location1,location2
-------------------------------
john, doe, dublin, null
john, doe, null, galway

and i wish to consolidate it to show like

Code:
fname,lname,location1,location2
-------------------------------
john, doe, dublin, galway.

I could do it by creating a new table, then merging, but its cumbersome.

View 6 Replies View Related

Consolidating Multiple Queries Into One Single Result

Dec 17, 2006

I couldn't find a similar question already answered, so here it goes. I have multiple tables used to store information on different research materials:

table Books:
- BookID
- Author
- Title
- Year
- Cabinet

table Papers:
- PaperId
- Author
- Title
- Year
- Cabinet

table Catalog:
- CatalogID
- Author
- Title
- Year
- Cabinet

And I have a query for each of those tables that helps me find information. What I'm trying to do is to consolidate the result of those individual queries into one single query that later I can insert into a form to display a search result.

So the resulting query would look like:

Type.....ID....Author.....Title....Year....Cabinet
Book.....001...John.....Good book....2005....C01
Book....002.....Mary....Other book...2006...C01
Paper....001....Albert...PaperABC....1987....C01
Paper...002....John.....PaperXYZ.....2006....C02
Catalog...001...Mark....Cat00A....1989.....C02
Catalog...002...Bill......Cat00B.....2004.....C03
etc.

Is this possible?

Any help is appreciated!

Thanks.
Daniel

View 4 Replies View Related

Consolidating Fields From Multiple Records Into One Record

Nov 29, 2012

In Access 2010, I have a table with two fields. The first field (Investor Name) contains several occurrences of the same text (i.e. Investor A occurs 2 times, Investor B occurs 3 times, etc.). The second field (Investment) may or may not contain duplicates (i.e. more than one Investor may have invested in the same Investment). I'm trying to create a query that will show me, in one record, all the Investments that each Investor has made.

So for the following data...

Investor Name
Investment

Investor A
Investment 1

Investor A
Investment 2

Investor B
Investment 1

Investor B
Investment 3

Investor B
Investment 4

I want the query to report back the following...

Investor Name
1st Investment
2nd Investment
3rd Investment

Investor A
Investment 1
Investment 2

Investor B
Investment 1
Investment 3
Investment 4

To clarify further, I'm NOT looking to concatenate the results into one field. Instead, I want each Investment to appear in its own field, so that I can pull each one into a report later. Ideally, there should be no limit to the number of Investments that each Investor has made, although practically speaking, it likely won't exceed 10 or so.

View 14 Replies View Related

General :: How To Create Form / Code For Viewing And Consolidating Duplicate Customers

Apr 8, 2014

I have a database with customers/orders etc. I need to create a form or something which will show if a duplicate customer is entered. I know about the duplicate records query wizard, but I also need an easy way to consolidate and delete records. When a duplicate customer is found, move the customers order/s [OrderNumber] to the existing customer and delete the new/latest customer record.

Customers are in tblCustomers, their orders are in tblCustomerOrders, [CustomerNumber] is the joining field.

I was thinking of just a date/time field for [DateCustomerEntered] with default value Now to keep track of the later customers?

View 7 Replies View Related

Tables :: Linking Field Data Between Tables And Within Tables

Sep 26, 2012

Currently trying to build a database for customer management and order placement/tracking. Want to set a couple of rules so that if I for instance click yes of billing and shipping address the same that the database will automatically fill the shipping address with the data I inputted for the billing address in the same table.

The other issue I can see I'll run into is, I want to be able to select one of the company ID's (made up of a three letter abbreviation of the full company name) in the product ordering table and it will automatically fill in the rest of the customer data (phone, email, address etc) data into that form.

View 2 Replies View Related

Tables :: Two Identical Database - Importing Tables By Linking To Data Source

Dec 3, 2012

I have 2 identical database in terms of structure but it differs in data.

Basically I would like to import data from subDatabase to mainDatabase and ensuring that there are no duplicate records.

I have used the "link to datasource method" through importing the tables to have the "updating" function.

However, this method also means that the records in mainDatabase are also imported over to subDatabase which I do not want.

Is there a method to ensure that the records are shared/update one way only? (i.e. import from subDatabase to mainDatabase and not main to sub?)

View 1 Replies View Related

Tables :: Export Multiple Tables Into Single MS Excel 2010 Workbook

Feb 10, 2015

I'm using the MS Access 2010 ExportwithFormating action to export three tables to a single MS Excel 2010 workbook. The action overwrites the first excel worksheet each time instead of saving all three worksheets in a single excel workbook.

How can I export three tables into a single excel workbook.

View 1 Replies View Related

Tables :: Developing Related Multi Level Child Parent Tables?

Jan 14, 2013

I have created a table that acts as a header for my data and a second table that acts as line item data. What I need to do now is add a second child table that uses the line item data as its parent table and stores associated line items for each record. Is this possible?

This is a skeleton view of what I'm going for:

Master Table:

tlbAuditReportHeader
- AuditDate
- AuditArea
- Auditor

Sub Table:

tblDiscrepancy
- Discrepancy
- CorrectiveAction
- ActualCompletionDate
- VerifiedDate
- Notes

Sub table to Sub Table

tblFollowUp
- FollowUpDate
- AssignedTo
- SpokeWith
- EstimatedCompletionDate
- Notes

Sometimes tasks change hands or are pushed back depending on work load. It would be nice to be able to track something like this.

View 1 Replies View Related

Tables :: Calculated Fields As Data Type In Tables - Calculating Total?

Apr 23, 2013

I am using calculated field as a data type in access 2010.

They are working fine.

However, I added a new field and now the final calc won't work.

I have Subtotal adding loads of fields together. Works fine.

Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.

The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?

View 2 Replies View Related

Update Query Using 3 Tables: Source, Joining, Destination Tables

Apr 19, 2007

I have some experience doing 'Update Query' using two different tables but I'm having a hard time doing an 'Update Query' using 3 tables.

I have my source table TP05XY with the fields 'Mark' 'Date' 'UTM_Edig' and 'UTM_Ndig'. Mark and Date are my primary keys (they together uniquely ID each record). I have my Observations table with the fields 'Mark' 'Date' and 'Obs_ID'. The last table is Locations with 'Obs_ID' 'UTM_E' and 'UTM_N'.

I want to update my fields UTM_E and UTM_N from UTM_Edig and UTM_Ndig. However, to do so, I have to go from my TP05XY table, through Observations table to update Locations table. Table TP05XY is joined to Observations through 'Mark' and 'Date' and Observations table is linked to Locations through 'Obs_ID' field.

I have tried a few options without success ... anyone knows how to do it?

Thanks,

Josée

View 1 Replies View Related

Tables :: 2 Similar Tables / One Takes Too Long To Append Data

Jul 19, 2014

I have being playing with ms access but I really don't know much about it or databases in general.I have created a very simple database to gather twitter following/followers data for research purposes.One table (table01) has a field for the "boss" user (=the user who I gather data for), another field for "client" (=bosses followers or friends).Both fields are numeric and contain the users id's.In order to distinguish if the link is follower or friend there is a third field, called type which can be either 1 (=follower) or 2 (=friend).So the data would look like this:

boss - client - type
12345, 67890, 1
12345, 54321, 2

If user with user id 12345 had a follower (type 1) with user id 67890 and a friend with user id 54321...In order to avoid getting duplicate rows I also added a unique identifier which is of the form boss_id-user_id-type.So the above row looks like this:

12345-67890-1, 12345, 67890, 1
12345-54321-2, 12345, 54321, 2

That works just fine.For several reasons I also needed data of the form source - target.So I also made another table (table02) of this form.

67890, 12345
12345, 54321
...

In table 2 you don't need the "type" field since the position of the user id shows the type of relationship.Still, you need a unique identifier in order to avoid duplicates, so I added on with the form: source_id-client_id..So table02 lookes like this

67890-12345, 67890, 12345
12345-54321, 12345, 54321
...

Both tables also have a date/time stamp for each line.As you can see, table01, having also a type field is bigger than table02.The problem is when I try to append data, exactly the same data in both tables.Appending data to table01 is ok, while appending data to table02 (which is smaller, having one less field) takes a really long time, maybe 10 times as long as appending data to table01.To make sure that no query's are causing the problem I have tried first creating temporary tables with the data to be appended, no duplicates, nothing that would cause the database to make extra calculations and used a simple update query with no filters to append data.Still I get the same result, table02 takes a very long time to finish while table01 finishes in no time.

View 2 Replies View Related

Tables :: ODBC Imported Tables / How To Ensure Index Is Created

May 13, 2015

I am currently using Microsoft Access 2010 32bit, and have one database acting as parent, with a second linked database as a client for people to work with, and the parent database has tables imported from Sage V21 via ODBC. I have used the following code as specified in other examples as follows:

Code:
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=MyDNSMachineName;UID=MyUID;PWD=MyPassword;LANGUAGE=us_english;" & "DATABASE=pubs", acTable, "INVOICE", "INVOICE"

one of the tables has 10k records in it, it only transfers 77 records. After manually attempting an import via the ODBC wizard it finally lets me access all 10k records.I wish to have a single button click delete and import fresh tables without worrying if all the data is coming across.Also, when manually adding a table, I am usually asked by the wizard to specify an index, but with the code above, I am not offered that option and the tables come across with no index. I am led to believe that having tables that link to each other without an index is bad, so how do I ensure an index is created?

View 5 Replies View Related

Tables :: Calculated Fields From Two Tables / Based On Relationship In Third Table

May 29, 2014

I have two tables of data, each relating to three business branches (branches A, B and C).

Table 1 shows the expenditure of each branch (by fuel, premises and wages).

Table 2 shows a number of units for each branch (mileage, floorspace and sales).

What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.

View 6 Replies View Related

Tables :: Joined Two Tables - Unable To Filter / Lookup On Second Table?

Feb 18, 2013

I have an Access Table with about 28,000 Automobile dealerships across the country shown. I've joined a new/small phone contact table to this to keep up with our phone contacts with the dealership and followup efforts. When I search/filter on the dealership table all is fine. However when I search/filter on the phone contact table with a few test entries, I get nothing at all. I supposed that after joining the tables, I'd be able to do a search on the field named follow up date and find/filter today's date or other dates and locate which dealerships to contact when the correct date arrives. But nothing.

View 14 Replies View Related

Tables :: Deleted Error In Microsoft Access Linked Tables

Jul 24, 2014

I have developed a Microsoft Access 2010 database for my client and the database is split with Front-end/Back-end, the Back-end and the database is shared on Network, The client operating system and applications for all users are hosted and consistent and the service is delivered over Citrix.

The database some times corrupt the tables record and give a permanent #Delete Error, I have attached one of the database table and the screenshot of the error,

View 3 Replies View Related

Tables :: How To Fill Local Tables In Application With Disconnected Record Set

Sep 26, 2012

I want to fill local tables in some application with disconnected recordset. The tables in the front end application having the same table structure as in the back end database. The front end application was linked with the back end password protected database tables. I want no connected linked tables in the front end application. How can I fill the local tables in the front end application with the back-end password protected tables?

View 8 Replies View Related

Tables :: New People Added In Table1 Not Showing Up In Other Tables Despite Relationship

May 5, 2013

I have 3 tables.

Table 1: contains staff names and contact numbers
Table 2: contains training above staff have been on or need to go on
Table 3: contains pc and printer asset numbers of above staff

I used a form and entered some new members of staff in table 1. They got their auto numbers etc but when I open table 2 and table 3 those new members are not showing up in those tables. I have checked the relationship status between the 3 tables and the staffID from Table 1 is associated to table 2 and to table 3.

What's stopping the new entries from showing up in tables 2 and 3 ?

View 10 Replies View Related

Tables :: Splitting Database - Front End Does Not Link To All Tables In Back End

May 28, 2013

I am splitting a database and have created the Back end already. When I create the front end and link to the tables on the back end... The front end does not link to all the tables in the back end. The list that comes up when creating the linkings does not show all the tables in the back end. What would cause this?

View 1 Replies View Related

Tables :: Updating Fields In Multiple Tables Without Onclick Event

Oct 23, 2013

I am working on a database which has two tables used as part of a registration and login process.

I would like a couple of fields from table one to automatically update in table two, once the fields in table one are populated without using an 'on click' event.

The reason I would prefer not to use an onclick is because the completion of the form used to generate the users table does not require any buttons for the data to save.

View 1 Replies View Related

Tables :: Set Up A Database But Wanted To Check Relationship Of Main Tables

Mar 4, 2013

I am about to set up a database but wanted to check the relationship of the main tables before I add to it. I have attached the relationship design

For a PROJECT, there can be many TESTS, for a TEST, there can be many PRODUCTS

Is my design reasonably sensible?

View 6 Replies View Related

Tables :: Data In Linked Back End Tables Not Saved From Front End

Jun 19, 2015

I have split database (B/E is in the SharePoint library, F/E has users on a local PCs). Sometimes, when I update/add data (does not matter if it is via form or directly in the table) it looks OK, but when I re-open the database, the data are gone.

Problem is that I cannot catch the moment when data were not saved (sometimes data are saved, sometimes not). I can point out this: if I re-enter the missing data, primary key continues subsequently, it looks like the data have never been entered. I tried to use script

Code:
If Me.Dirty Then Me.Dirty = False

on "On Close" form event, does not work.

B/E is linked by VBA code and it looks OK (no error, Link Manager shows correct path). I suspect interrupted connetion to the SharePoint but I don't know how to check it. I implemented VBA script co keep open connection to the SP but the issue persists.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved