Modules & VBA :: Combining Multiple Data Sets To 1 Table?
Aug 8, 2014
I'm trying to set up a way to import and combine excel files that contain multiple data sets. So for example, each excel file has a summary heading which consists of the first 3 rows.
Each data set thereafter consists of approximately 50-60 rows of data that I would like. There are approximately 1400 groups of data. Each group has a label which includes the state and the store number. I would like to automate a way to copy the state and store number information down each data set as well.
I have approximately 200 excel files that I want to load into access and have it format it so it basically will end up 1 big file with State, Store Number, relevant information from the data set. I've seen a module do this before, can't remember how to do it.
I HAVE A DATASETS cSV TEXT FORMAT WHICH HAS A SELECTION OF FIELDS THAT NEED TO BE IMPORTED INTO A TABLE. USING THE ADVANCED IMPORT FACILLITY I HAVE BEEN UNABLE TO IMPORT THE DATA PLEASE HELP AS THIS IS FOR MY A-LEVEL STUDENTS.
THE FIELDS REQUIRED IN THE SESSION TABLE ARE, Booking ID, Customer ID, Date Booking Made, Pickup Point, Payment Made
I have a simple line chart plotting price against date.
I would like to plot a secondary line on this chart from an array of data that I calculate. I've searched hi and low but can't seem to find a way of doing this.
The closest I've found is from this:-
[URL]
I can create my array of data but I can't seem to get it to work and think it may be for pivot charts....which access 2013 can't do anymore.
how to plot multiple data sets on the same graph in Access 2013.
I am having problems figuring out how to set up my table/query structure for a series of surveys that are based on specific user groups. In a nutshell how do I set up the relationships for the Questions, Employees, and Answers if I have the following tables (simplified):
1. Create a set of questions for two or more user groups (each set contain different questions). 2. Assign Employees to a specific user group. 3. Employees are able to enter the DB to answer the questions within their user group.
What should happen is the employee accesses their set of usergroup questions and enter the corresponding answers. Each time I try to set up the relationships however, the answer field ends up being uneditable.
Any help you can give would save what little hair I have left.
I want to split a table into multiple sets based on rowcount. Suppose I have a table having 10,000 records. I want different sets which should have values based on rowcount. Suppose if I select set 1 then the table should populate records from 1-2500. If I select set 2 then the table should automatically give the records from 2501-5000. If i select set3 then the table should have values from 5001-7500 and so on.
I have created a DB who's sole purpose is to track employee availability and job positions available (a poor man's version of a scheduling database, if you will). I have 2 listboxes on one form. Listbox A shows all the employees available for work, and Listbox B shows the available work positions that require an employee. I am trying to create a functionality behind a button that will allow me to highlight one row in Listbox A and one row in Listbox B to combine the information in a seperate table (still not created) and refresh each Listbox to remove the entries. Similarly, I would also have to create functionality behind another button to undo the above, if needed.
I have a database which is importing several Excel workbooks, each with multiple worksheets. Every workbook has 20 worksheets, with the same 20 worksheet names. When they are imported they come in as one table for each worksheet, named tblWorkSheetName_X with X starting at 1 and increasing for each worksheet brought in with the same name. So if the worksheet names are A-T I have tblA_1 through tblA_6 and likewise for B - T.
I would like to combine all of the tables which come from similar worksheets into one table (one table per name).
I.e. I want to combine the data in tblA_1 through tblA_6 into a singular tbl_A and likewise for tables B through T. So in the end I will have one table for each worksheet name A-T. t how to code this successfully?
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!
I want to be able to make row searches for multiple sets of characters at the same time.The default setting cancels a row if I have inputed a set that is not on that row.Something that could be used in a store or by a lawyer looking for specifics.So these are what I am looking for:
Primary
1.) I want any row with any of the words I type to show on the results.
2.) I want the rows with the most matches to show up first.
Secondary
1.) Recognize sets of characters that are close to what I type to make up for spelling errors and typos, prioritize those that are closest.
2.) To be able to choose the rows I want and add them to another list quickly where the summing cost will be calculated in the last raw (multiplying the price of a row by how many the customer wants.)
I have a set of code to keep an audit trail that calls a module:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer) Call AuditTrail(Me, CurrentCYIDPK) End Sub
Calls
Code:
Option Compare Database
Const cDQ As String = """" Sub AuditTrail(frm As Form, recordid As Control) 'Track changes to data. 'recordid identifies the pk field's corresponding 'control in frm, in order to id record. Dim ctl As Control Dim varBefore As Variant
[code]....
how to run both of these events on Form_BeforeUpdate.
I have a query that returns a large data set (~100k rows/month).
I am currently inefficiently exporting week-by-week to Excel to carry out some manipulation (mainly a set of "if" statements to calculate differences between records that have the same site id).
Is there a way to write a function in Access to carry out the calculations? I'm not too familiar with Access VBA apart from coding functions for forms etc.
i have 2 recordset and i need to compare the two. If a record doesnt existing i need it to be added I have VBA that works but it seems very slow. Is there a better way of doing it
Dim rs As DAO.Recordset Dim rs2 As DAO.Recordset Set rs = CurrentDb.OpenRecordset("SELECT * FROM ExorData") Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM MainForm") Dim TJb_Main, TJb_new If Not (rs.EOF And rs.BOF) Then rs.MoveFirst
I have the following tables in my Access database.
A < B < C < D
(The "<" represents a one to many relationship.)A given row in table A can have up to 4 children (stages) in table B (stages 1 to 4).The other one to many relationships do not have any limitations as far as the number of children are concerned.All tables have AutoNumber primary keys.When the user clicks a button in a form, I want to:
Copy all data in the current stage (current row) in table B (corresponding to a given parent row in table A), to the next stage in table B.All data in child tables must be included in the copy process.In other words, for a given row in table A, by use of buttons in the forms for each of the stages 1 3, the user shall be able to do the following:
all data for stage 1 are copied to stage 2 (for user modification), then all data for stage 2 are copied to stage 3 (for user modification), then all data for stage 3 are copied to stage 4
I am working on a report that has some special characteristics.
Let's say I have a list of groups of Vendors in a table, complete with VendorID. I have 3 other tables that use the VendorID: Complaints, Complements, and Terminations.
Each of these tables has a date that the Complaint, Complement, and Termination notice was received.
Every Fiscal Quarter, a report is pulled that looks back over the 4 preceding quarters to determine if a 5% threshold has been crossed by any of the vendor-groups in regards to the amount of Complaints they received.
The equation used for that is : (complaints/vendors_in_group)*100
It is imperative that the information has the current fiscal year and fiscal month (which I am tracking with functions from MS website), and I need to be able to store the information attached to the fiscal year and month.So when a user goes to the form and inputs the desired Fiscal Year and Fiscal Month, the database can display the 4 previous quarters of information...split into Q1, Q2, Q3, and Q4.
What I would like to have happen is to be able to have one table where the information is stored, quarterly, so that it can be retrieved for the report.
Questions: 1. Is it possible to have one line, per VendorID, that has the total number of Complements, Complaints, and Terminations, as well as the threshold percent stored in a table? Right now, I am getting LOTS of duplicates and blank lines when I try to put them all together. It has the right data, but takes about 10 rows per VendorID.
2. It is very important that the total number of Vendors in a group be captured on that quarterly report, so maintaining that number, in the same table, is essential and must be tied to the VendorID.
3. I have looked at Union Queries and Crosstabs, but I just dont know enough about them to make it work.
Here is the situation that I'm hoping that someone can help me with. I'm working with a database that tracks our condo units - from prospect coming into the system until we close them as a buyer. All the units are setup in the system so a salesperson will select from the units available. All that works fine when I create reports. The problem is trying to get the parking and storage on the same reports with the unit information. The problem is that there are multiple parking/storage units "attached" to a single unit and I cannot figure out how to get them to all appear on a single row of the report. As an example -
I have units A, B, C Parking units p1,p2,p3,p4,p5,p6 Storage units s1,s2,s3.
Unit A uses parking units p1, p2, p5 and storage unit s2.
Unit B uses parking unit p3 and storage unit s1.
Unit C uses parking unit p4, p6 and storage unit s3.
How do I write a query/report that would show:
Unit Parking Storage Unit A p1, p2, p5 s2 Unit B p3 s1 Unit C p4,p6 s3
I have an issue I can't quite get my head around. I have a table with four coloums that list Emplyees by Number. I need to pull thase number and combine them into one colomn through a querry (I think). I don't need to join the coloums in the same record. Example...
Now... leader Pilot Admin Facilitator 11800 11801 11802 11803
I'm trying to make a payment report which prints a separate pay sheet for each employee based on what job they did. I don't want to get into too much detail here, but basically, a name is associated with each action in a setup like this:
I want to have a query that gives one column that looks like this:
Bob Fred Jeff Jim Joe
I already know how to sort and group by, but how would I write a query that contains one column, then another column when that one ends, then another, et cetera?
(I need to know this, because it would be a lot cleaner to show one report for all 5 jobs than a report for each job separately, and of course, that's how the boss wants it!)
Not exactly sure if a query is what I need in this situation or if it is what I need how to get there.
In the attached db example on the case form there is a section for technicians to go in and take credit for steps that they performed as part of the overall case
So clv1 might be done by User A Then clv2 might be done by User B
But the next case it might be switched.
I need a method getting the sum of the total clv's field for each technician in two different ways 1 would be the total clvs for USer A for the current month, 2nd would be the total for the year- or actually a prompt for a date range
Tried using the query wizard but it doesnt combine the names Then I tried an individual query on each set - that worked but then I only get the ones in the first column - not all the clvs that they did.
Hope that makes sense - is there a way to do this or am I in the wrong forum for trying to figure out a way?
Well the db example is imb so it cannot be attached
I'm putting together a (hopefully) highly automated database that will allow me to produce clear reports for various areas of responsibility in a new job.
I have 3 excel spreadsheets linked directly to access, containing work queue reports. I then run queries that copy new work items into a main table, followed by a query that looks for work items that no longer appear (i.e. work is finished and closed) and then automatically inputs the date.
For reporting purposes i initially want a basic output, i.e. work items raised, and work items closed, for each area of resposnibility. The only trouble i've found is that i have to do a different query for each output, then another query that pulls all the other queries together in order to maniuplate into a graph...
So at the moment i have two queries per responsibility, and one pulling those two together i.e.:
New Work Items: SELECT Count(ticketsds.id) AS [DS New] FROM TicketsDS WHERE (((Format(ticketsds.[date received],"mmm"))=Forms!ReportingDate!comboMonth)); Closed Work Items: SELECT Count(ticketsds.id) AS [DS Closed] FROM TicketsDS WHERE (((Format(ticketsds.[datefinished],"mmm"))=Forms!ReportingDate!comboMonth));
Then i have this query pulling the two results together: SELECT DSClosed.[DS Closed], DSNew.[DS New] FROM DSClosed, DSNew;
Is there anyway of combining the first two queries into one, rather than have 3??
Any help would be greatly appreciated, as if i could find out how to do this it woudl enable me to streamline other queries....
Many Thanks! Gareth
PS I've tried 'UNION' but that just tags the other results under the same field name which i can't use to create graphs...
I have a table with a field called "description" another field in fame table called "length of cable" In description field it contains among other things, the length in it . It may be at various locations in the test data in the field. The length may be in the form xft or xxft xx foot xxfeet xx feet..
I have another field that I need to put the length in a standard form like xx feet (The field will have different units in it later not just feet.) I am using update query with criteria of Like "*3ft*" Or Like "*3 foot*" Or Like "*3 ft*" Or Like "*3 foot" with update to (of the length of cable field ) set to 3 Feet The data has many lengths 1ft 2ft 3ft 4ft 5ft 6ft 7ft 10ft 12ft 14ft 15ft 20ft 25ft and one....
I would like to automate it to one query and hopefully avoid the problem I have when doing it as is, *5ft* also finds 25ft and 15ft incorrectly for the goal of the update. same on 12ft 14ft
' and " are the symbols for inches and feet. Also some of the data is in the form xx' or xx" I hit a wall on this due to the ' character need to do the same to that as well.
Any suggestions ''' before I lose any more hair ...
I am trying to figure out the best was to combine fields from multiple rows into one row & field.
Example: I have a table that contains footnotes and products. With a simple query I would get the following 3 rows:
Product........................................... .......Footnote V.I. Capital Appreciation Fund.................3 V.I. Capital Appreciation Fund.................5 V.I. Capital Appreciation Fund.................1
What I want is one row and the 3 footnotes combines into one field:
Product........................................... .......Footnote V.I. Capital Appreciation Fund.................3, 5 ,1
I have a form that I use to filter a sub-form. In the form I have 4 toggle buttons that filter the corresponding fields in the sub-form quite well. What I would like to have is when one toggle is selected, the user can select a second or third toggle to further refine their inquiry. I am attaching the code that I used for the individual filters.
Code:
Private Sub Toggle_Filter_DOB_Click() If Me.Toggle_Filter_DOB = True Then [Forms]![Phase 2]![FormPhase2_sub].Form.Filter = "DOB = #" & Me.ATS_DOB & "#" [Forms]![Phase 2]![FormPhase2_sub].Form.FilterOn = True Me.Toggle_Filter_DOB.Caption = "Filter On"
Im trying to work out the best way to combine results from a table. Not knowing the correct terminology I didn't want to post in a specific section of the forum until its clear in my head.So I have a query currently which shows the entries into my database for example (these aren't the column names precisely as I know some are reserved words its just to demonstrate):
UserA - Hours - Date - ProjectA UserB - Hours - Date - ProjectA UserA - Hours - Date - ProjectB UserA - Hours - Date - ProjectB UserB - Hours - Date - ProjectA UserA - Hours - Date - ProjectA
What I am trying to do is combine UserA's entries with ProjectA and UserA's entries with ProjectB (seperatly) and UserB's with ProjectA and UserB with Project B (again seperatly).So the end result would show a total of User A's hours on Project A and separately ProjectB and the same for UserB.
I currently have an access database for tracking company assets and performing maintenance tasks on them.We also have a number of Stores Stock Items that are controlled under a separate finance package.We currently have forms that create individually numbered "Job Cards" in the Job Cards Table..we input the data and manually type into a Text Box the we have used 'X' number of Store Stock 'Item Y'
I have then use a query to export the data i need (Job number, dates, location and Store Stock Items).But I have to manually re-construct the data and items into separate lines in an excel sheet for a paste operation into the finance package (lots of typing of 16 digit stock codes)
What i want is to use a second table (linked via the "Job Card#" key).It will be called "Stock Items" which has a separate field for each of our stock items.the user then just opens second form while creating the job that immediately creates a new record.The user then simply has to put the number of each item used in the relevant text box on the form and close.
example of datasheet view Job# __ ItemX __ ItemY 2417_____3_______7
Now, I have all of that working as I want, But i nee to be able to combine the data from each field into a single field (either in access or excel).What I want to see is a field containing 3x ItemX, 7x ItemY, etc
if there are none of one or more items, I don't want anything displayed for that item (blank space is ok)I have 30 or more stock items, so there will be 30 or more columns in the table. the actual data stored in the table is simply the quantity of each item used.
this data is used twofold,on export to Excel I can split the data into new cells and it's used on a despatch note that is returned with the item so the end user can see the items that were used in the repair.So it needs to be done upon saving the record, record by record.
