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.
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.
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.
I want to make a table where in I have to input atleast 10 numbers in one reference ID which there is a start time and end time.
A form with inputing one reference ID and then adding multiple numbers at once and saving the data with a start time and end time. Is it possible to capture the time for that specific reference ID?
In a query I'm trying to return a list of rows sorted by Service Type Ascending and then the last item in the list should be a row called "Add Edit Value".If I 'ORDER BY 2' then the "Add/Edit" row appears at the top which is not what i want.
My SQL: SELECT '' As ServiceTypeID, 'ADD/EDIT VALUES' As ServiceType FROM ServiceTypes UNION SELECT ServiceTypes.ServiceTypeID, ServiceTypes.ServiceType FROM ServiceTypes ORDER BY 1 DESC;
I am new with Access and I am setting up a data base that will have 16 tables and each table with have over 3,000 customers. The reason for 16 tables is that we will be inputting information on a monthly basis for each of these customers (12 of the tables) and I also have 4 tables that will be perfomring different "tasks" for each customer (one is just to keep the notes we make for each customer, one will be to show any billing done, one is going to be a summary of the entire year along with some additioanl information not entered elsewhere and the final one is our customer information table).
The customer account number is the primary key for all of my tables.I want to know how I can add a new customer (either using query or form) at one time that will populate all the tables. Right now I have everything set up as tables but I can set up queries if need be.
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).
I've got a form called 'frmStudent' which is linked to 'tblStudent'. I have designed it as a tabbed form, the first tab has "Student name", "Student Number", etc. The second tab is linked to visits by the student to a mentor (such as a tutor etc). Here I'd like to have "Date of visit", "Mentor Name", etc.
So far I've been able to get the first tab to work (I'm guessing because it's linked to 'tblStudent'), but can't get my 2nd tab to work as I have no clue on how to link it with 'tblVisits'. Is there a VBA methor or perhaps a nother method to do this?
I'm attaching a link to the sample of what I'm doing (please shift-open into frmStudent to view what I'm talking about). Any ideas and suggestions would be greatly appreciated!
I have a tabbed form that contains multiple rows of data just like the following: (it's a service checklist for a store where the user makes choices from the combo boxes: e.g. good, fair, poor, etc)
Since the combo boxes describe data that relates only to that element I can't put all the data needed into a single table. There are 40 of these rows so does that mean I have to create 40 tables? If so, how do I add the data to them from the form? Any help with this problem would be greatly appreciated!
I need to set up an attendance database, that has multiple statuses available for a single day.Example: employee can be present, he can have a sick leave, he can be away on training, or business trip, etc... And for some of those statuses, like business trip, i need to be able to freely enter a comment, stating where he is etc..
Anyway, the key is that this database should be able to offer a "headcount" option, and traceability for past statuses for at least a year, for every and all employees. Now i just need to set up the database tables and relationships.
I wrote a database several years ago and recently pulled it out to give to a friend. The problem is, back then (not knowing better) I set the Employee table up with as a single field "NAME". Now, in order to make it effective, I need the Employee's name in four (4) parts (First, Middle, Last, Suffix).
I have several queries based on the "NAME" field and and would like to avoid changing all of them. I have a simple form "frmUpdateEmployees" that populates the Employee table and Name field. I was hoping to change the form and/or add a query that would be easier and more simple.
Table: Employee Field: Name Form: frmUpdateEmployees Queries: 16 that depend on the table and field above.
I have a tblCustomer table to track customer information. One field is for flights the customer books. I expect that a customer might book more than one flight, which should affect what he owes. (My tblFlights table includes fields for the cost of each flight.) How should I make the Flight field in tblCustomer so it accepts multiple flights and reflects on the customer's final bill and my own accounting tables that I haven't even created, yet?
I'm probably overthinking everything. This is just an Intro to Access class, and I only need 3 tables with 1 relationship. So far, I have 9 primary tables, 4 duplicate tables (for M:N relationships) and 11 relationships. I have to have tables for financial data, customer info and product/service info. I wasn't sure what I would need for my fictional airline, so I created tables to track everything I could think of, hoping to cover all 3 required types of data in the process.
We have two facilities that receive four different types of shipments. I wanted to set up a database to track these shipments. Is there a way for me to set up tables such that the person entering the information can leverage the same date and location field for each entry type? For example, we may only receive two types of shipments today and four tomorrow. I want to avoid having the clerk enter the date and location two times today and four times tomorrow for each data entry.
Here's an example of what I mean above:
Date Location Type Qty 5/16 1 A 10 5/16 1 B 1 5/17 1 A 1,000 5/17 1 B 100 5/17 1 C 1 5/17 1 D 11
I'd like for the clerk to select from a fixed number of locations (1 and 2) and a fixed number of types (A, B, C, or D) and provide the date and quantity received.
Now the "User" table has the ff. fields: ID(auto num),FName,MName,LName,User,Pass,Type.
Now what I want to do is, if I add a new a record in the table "User" and if the record has the data "Admin" in the field "Type" then the record should also be saved in the table "Admin" but if the data is "Teacher" or "Pupil" then it should be also saved in their respective tables.
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.
Hi, thanks in advance for any help you can offer. I've got a table that has
Date Time Tag ID Power Level
throughout the day a computer listens to several tags (transmitters) and records the power level of the signal generated by the tag each 3 seconds. What I'd like to do is build a query that gives the Date, Time and Maximum Power level reading for each tag ID. I only want 1 record per tag per day
I've tried using "group by" and max in the query but this gives me all the times throughout the day.
My database allows you to log issues (see attached)
An issue will belong to the project, the project may have multiple test plans.
The issue will also be the primary responsibility of a company, and that company may have multiple contacts (people)
Now, i could create a form over the top of each table but then if someone wanted to add a new project, testplans for that project, suppliers (companies) for that project AND new contacts for those companies, they are going to end up going through loads, and loads of forms.
So my question is, what approach would you recommend to create a control panel where they could add these items with the fewest number of page switches possible? (preferably none.
I have 5 tables that I would like to input data in. It can only be done with a single form. The fields I want to input in have the same names in all 5 tables, for example:
Table 1: Name Age DOB
Table 2: Name Age DOB
Table 3: Name Age DOB
Table 4: Name Age DOB
Table 5: Name Age DOB
Is it possible to input data into all of these fields in each table using one textbox for each field?
Preferably without having to use code but if it cannot be done without it then that would be fine.
I have a list of 22 soccer players with their weights sorted from heaviest to lightest in numerical order. I want to add further players and have the list reflect their standing each time it changes i.e. Col 1 Player ID, Col2 FirstName, Col 2 SecondName, Col 3 Mass:90 Kgs Col 4 Position In group: 1
A new player registers and is 95Kg.....he becomes #1 and the rest go down etc. Can this be done?
I have a couple of tables which only have a single field. Is it worth adding a second field in order to hold an ID number (is this commonly termed an index number?) for each entry.
My other tables with multiple fields include a field for an ID number and these tables are linked via these fields.
At this stage I cannot see any reason why, in the future, I would want to add additional fields to the tables which I am currently planning to make single field.
I have to redesign the database as things has changed. I've already built a table holding records of Clients along with their address information and some attributes that relates to Clients.Now, I need to create a table for our Business contacts. This will also need to store address info and then some attributes relating to businesses. The problem is I'm not sure if I want to take address out of Client table and have Client and Business table link to Address table to follow the rule of not having any repeating data or simply allow Business table to store address.What would be the best thing to do here?Thanks.
I've inherited a data base which has the address details of our members spread across multi fields i.e. Add 1, Add2, Add 3, Post town, Post code etc.
Not all the fields contain information which means when I do a mail merge for address labels there are blank lines.
I would like to either be able to create a single field in the table (like a memo field for example) which contains all the address detail, or create a mail merge without blank lines.
I want to "lock" a record in a table so that it is read only and cannot be accessed/edited/deleted. Is it possible to lock a single record in this way on a permanent basis? It's actually the first record in the table.
