OK, I have a truck mileage database with a columns for monthly "StartingMiles" and "EndingMiles" - At the beginning of each month I want to automatically move the entire content of the "EndingMiles" column to the "StartingMiles" so I can start a new reporting month. I'm assuming I can use an Update Query to do this - correct? How do I setup the Update query to perform this?
Or is there a better way?
Below is the code that I have for a query. Currently the
GrandTotal Column appears to the left of all the Weekly Columns.
Is there a way I can have the GrandTotal column appear to
the right of all the Weekly Columns? The weekly columns
will expand or contract depending on the dates selected.
PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime, [Forms]![Queries_ReportsFRM].[FaultCategory] Text ( 255 ), [Forms]![Queries_ReportsFRM].[SystemGroupProblem] Text ( 255 );
TRANSFORM Val(Nz(Sum([Totals]),0)) AS SumOfTotals
SELECT [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, [Trends-1-3TON-WEEK].Problem, Sum([Trends-1-3TON-WEEK].Totals) AS GrandTotal
FROM [Trends-1-3TON-WEEK]
GROUP BY [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, [Trends-1-3TON-WEEK].Problem
PIVOT [Trends-1-3TON-WEEK].YearMonthWeek;
I have a report that displays maybe about 4 columns that read data and if a columns reads zero then I have a code where it will hide, but my problem is that I cannot get the columns to move over once the column that is zero is invisible.VBA code that will hide a column that has zeros and move over the remaining columns so that when the report is ran it will not show just an empty white space.
View 10 Replies View RelatedHello Tech gurus,
I have a database with existing data, that is not normalized, and all the data is in one field...:eek: This what I am dealing with:
As you notice the first row of data in field1 C10A CHOLEST&TRIGLY has 3 spaces to the right the next row ALTORVASTIN has five spaces to the right, the next line LIPITOR has 7 spaces to the right, and so forth, hopefully you get at what I am trying to do, I just looked at the data and it is not showing up in view of the leading spaces, but I am trying to move the data based ONLY one the position of leading spaces, example all data that has 3 leading spaces would go into its own separate column, and all data that has 7 leading spaces would go into a separate column, I have tried to use the left, mid, len functions but I cannot figure out how to move the text and keep it whole only by data position of spaces.
Field1:
C10A CHOLEST&TRIGLY
ATORVASTATIN
LIPITOR
PFIZER
SIMVASTATIN
SIMVASTATIN TEVA
TEVA
M1A ANTIRHEUMATIC N-STEROID
ETODOLAC
ETOPAN
TARO PHARMA
Thanks so much for your help... :confused:
Nana :D
I am trying to run a simple update query to copy data from one column (Addrl1)to another column (Working_Addrl1) within the same file and I can't for the life of me figure it out. Then I need to repeat for addrl2 and addrl3 to working_addrl2 and working_addrl3.
View 7 Replies View RelatedI need to input a string into a column named "EventType". The code should first check if the column "Agent Name" contains any strings. If there is none, it will input "IBM Director" into the EventType column.
Once it has looped through the agent names, the code will then loop through the Details column and input into EventTypes based on what is displayed within the string.
These are the codes that I am using to achieve this, however nothing is being input into the EventType column.
Code:
Private Sub Command11_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Final")
[Code] ....
I think the problem lies with the code that checks the agent name. When I removed it, it managed to populate the EventType column based on the details. But I still need to find out how to check the agent name too.
Hi. I have a question I'm hoping someone can help me with. I would like to take data from multiple columns and put the data into one column. Additionally, I do not want to exclude any data (union all) and I would like to group the resulting union by another field. For example:
Original data layout:
Column Headings: Sample Event, Depth 1, Depth 2, Depth 3,
1st Row Data: 1, 6, 9, 12, 9
2nd Row Data: 2, 7, 9, 8, 3
Desired data layout:
Column Headings: Sample Event, Depths
1, 6
1, 9
1,12
1, 9
2, 7
2, 9
2, 8
2, 3
So far I'm using the following SQL. What do I need to add or change to get my desired result of grouping the unioned depths by the 'sample event' field?
I appreciate any help anyone may have to offer. Thank you.
SELECT Depth1 AS Depths
FROM Depth_Velocity_Substrate_Correct
Union all
SELECT Depth2
FROM Depth_Velocity_Substrate_Correct
Union all
SELECT Depth3
FROM Depth_Velocity_Substrate_Correct
Union all
SELECT Depth4
FROM Depth_Velocity_Substrate_Correct
Union all
SELECT Depth5
FROM Depth_Velocity_Substrate_Correct
I have a Access 2003 file and I want to filter anywhere where there last name is "expired" and change the column first name to say "no". How do I do that?
View 1 Replies View RelatedI have two particlular listboxes in an Access Form the first is named listEmp and the second is listAllocated
The source of the first is a query which is based on previous combo boxes.
How can I move selected or all items from one box to another and back if needed.
I need the items moved to be removed from the original box so the data is only in one or the other box.
Please include any VBA that may be needed.
Please would you be able to advise me how I would copy data from a column in one table to a column another table.
But I need to only use table not a query.
Thank you in advance for your help.
Hi,
I need a query that could split data into 2 different field.
The table pic is attached for your ready reference. I tird with Left & Right funtions with no results.
Any one can help me please.
Table name is Agcad and field on which I need to split is Board_Size. The problem is Board_Size data is sometime in 3 digit (first half part) and sometime second half part is in 4 digit. It is a text data type field.
Thanks in advance...
Ashfaque
I have a table that contains:
item | quantity
123 | 1
456 | 10
789 | 4
I need to get this data listed into a table that just has item and for it to look like this:
123
456
456
456
456
456
456
456
456
456
456
789
789
789
789
I understand and have accounted for the obvious primary key issue.how to get the data to display out like this.
Hi, I have data from a form on my website in a text file, that corresponds to each visitor's input, each 13 lines in the form belongs to one visitor, as shown (twice) at the end of this message.
What I would like to do is have each visitors inputs translated to ONE row, with 13 columns/fields each. It could be appended to the same table or preferably generated in a new one. Note, there are no blank fields, some won't have data after their title, i.e. addy_line_2: is often blank, but at the very least, addy_line_2: or another field name is always there.
It would ROCK if I could also automatically take the name of each field out, i.e. each name is continuous characters up to the : (colon) ...
Thanks in advance, my Access knowledge is obviously limited, I'm sure this is fairly simple!
The fields:
Phone: 213-555-1212
Submit: Continue
addy_line_1: 1000 Melrose Place
addy_line_2:
badge:
city: Los Angeles
email:
homegroup:
name:
program:
state: CA
volunteer:
zip:
Phone:
Submit: Continue
addy_line_1:
addy_line_2:
badge:
city:
email:
homegroup:
name:
program:
state:
volunteer:
zip:
thank you thank you thank you thank you thank you !!
Hi,
I was wondering if i can get some help here. The aim here is moving data from excel to Access '03.
At the moment, i'm having troubled in finding out a way to open a database and execute SQL - insert the data into the table.
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source= C:MydocumentsmyDB.mdb;Extended Properties=Excel 8.0;"
.Open
End With
then, i got stuck in here whereby i need to execute the SQL insert command to insert record to the table.
Appreciates your help greatly
Thank you in advance
Ok here is what I've got.
I have a table called (EQUIP REG)
It contains all the information to the equipment in the hospitol.
next i have a table called (WO REG)
It contains all the information for the workorders for the equipment.
In the EQUIP REG table I have field titled (PM) this is the date that the equipment is due for PM's. Now I could just run a query for all the equipment on a given date called "MONTHLYPM" and then "open" the workorders by hand....but would be an all day job becuase there are nearly a thousand PM's a month. What i need is a wuery to run off of the above mention query that will copy the some data from the EQUIP REG table to the WO REG table. I only need the fields called "MMCN" "SERIAL" "MODEL" and "LOCATION"
I know very little about access and even less about VBA, so who ever is brave enough to help me must be able to talk on a very low level.
thanks for any help in advance. :)
I am working on a calibration database which keeps track of measurement equipment and their scheduled calibration date. There are several gages that become "In-Active" because they are old, lost, or whatever. I have two tables that have Active and In-Active gage listings containing all of the pertinent information regarding that gage. I would like to put a button in the Active gage form which in the event the gage becomes "In-Active", pressing the button moves all of the information in the "Active" page to a new page in the "In-Active" table. So essentially one can look at Active gages and In-Active gages depending on the switchboard button that is clicked.
I know that is probably a crazy question but any thoughts or insight would be greatly appreciated.
Hello. My question is how do I go about moving data from one field to another within a table? I want to move the data in ReportDateClo to ReportDatCom when both ReportDateClo and SupAct are true by record which are identified by the field LogNum. It is probably very simple but I am having a brain cramp.
ReportDateClo and ReportDatCom are "short date" date fields. SupAct is a Yes/No field. LogNum is an autonumber.
My boss has assigned a task for me to move the details under a field name (eg:[Name]) in table 1 to table2 under another field name (eg [Insured Name]).
Previously, the software I planned to use is Excel, but my boss says Excel cannot handle large data as the computer will be hanging.So,he wants me to use Access to do the programming.I have some programming backgrounds (like C++) but it seems like I hardly understand how Access works! So can any Access expert here teach me how to do so that i won't get fired.
Firstly, We will have the data like this.
Table1
Code:
Name | PolicyNo | Age | Benefit
--------+----------------+----------+-----------
Chris | 123 | 21 | Death
Jane | 456 | 34 | TPD
Adam | 789 | 55 | DD
Secondly, we let the other staffs to key in which column to be mapped with the other. Manually Key in (both columns).Mapping Table:
Code:
Table1 | Table2
--------------+-------------------
Name | InsuredName
Policy No | Pol_No
Benefit | InsuredBenefit
Age | InsuredAge
Thirdly, the staff will run the VBA and result would be like this.
Table2
Code:
InsureName | Pol_No | InsuredBenefit | InsuredAge
--------------+--------------+----------------------+----------------
Chris | 123 | Death | 21
Jane | 456 | TPD | 34
Adam | 789 | DD | 55
As you can see the new table(table2) , field number 3 is the same with the field number 4 in table1. There is something like copy and paste job, but user can decide how the arrangement for the new table is.
Hello,
I am looking for a way to use the dblClick function to move the current row of a list box to another. The idea is that users can look through data and pick certain rows to be printed in a report. I am not sure how to go about doing this and am not sure if it is the best way. The second listbox is so the user can review their selections before printing a report.
Thanks,
Kristopher
I have a Macro Which my team members fill and submit the information of their daily workload. And the person who Submits has a Pivot table to check if the Workload that he has submitted has been QCed or not.
I have made 2 Tables one is temporary database and other is a master Database, I keep clearing the Temp Database because when users refresh the Pivot table it takes less time if there is less data and Vice Versa.
Problem Statement: When a particular Column is updated for example "QC Result" column is updated as "Green" or "Red" the entire column needs to be moved to the Master Table.
I have two listboxes on a form listbox1 and listbox2. listbox1 is populated by table1 which has 40 fields and 1000 records. listbox2 is populated by table2 which has identical structure to table1(same fields) but has no records.
For both listboxes Multi Select is set to simple. Rowsource is Select * statement from tables. Row Source Type is set to Table/Query.
I would like to select multiple records from listbox1 and add them to listbox2 and table2 or just table2. After selecting the records from listbox1 the user will click a button to add the records to table2. Also need to delete the values selected from table1 on same button click.
The database is saved with a .mdb file extension.
I have several thousands of lines of data which I wish to manipulate programmatically, if at all possible. I think that all of the possible permutations are summed-up by the following examples :
123 A text string
2-8 Another text string
A-C Another text string here
3-20 And some more text
3A-126B More text
Some text without any numbers or letters at the left
What I need to do is :
Move the alphanumeric data at the left to the right. So the data would end up looking like this :
A text string 123
Another text string 2-8
Another text string here A-C
And some more text 3-20
More text 3A-126B
Some text without any numbers or letters at the left
I suppose it hinges on identifying where the first space in from the left appears, cutting the string at that point, and
Is this possible, programmatically ?
I have two separate columns in my query but they contain the same kind of data. These two columns data should match but some of the data doesn't. Can I write a criteria that will show me only the rows that do not match.
DUTYPOSC
11B111B1
11B111B1
11C111B1
11B111B1
92Y111B1
11B111B1
13F121B1
For example, I want the query to show me only the rows in which the data in the Duty column doesn't match the POSC column.
I am having trouble arranging my data from a record row into a query that will give me columns for the pivot charts that I need. My guess is that I need some sort of a complicated SQL statement, and I know almost nothing about SQL. I have expressed the problem better, and with graphics, here: http://www.olypen.com/bhardin/RGB/RGB.htm. I would love to hear the solution to this aggravating :mad: problem.
View 4 Replies View RelatedOk my problem is this......
I have a form with a field "Property Status" on it. It has 4 possible values -
"C - SHELTERED (with warden charge)";"H - SHELTERED (No warden charge)";"J - WHEELCHAIR SHELTERED (With warden charge)";"M - WHEELCHAIR SHELTERED (No warden charge)";"X - DISCONNECTED"
When the value "X - DISCONNECTED" is selected in the form I want the record to be removed from its existing table and sent to a new table which keeps all the "X - DISCONNECTED" records together.
Any ideas would be greatly appreciated. How would this be coded?
The school director is going to input a date (by default today) and a subform will display all of the students who started before that date and haven't been closed as of that date. Next to each name, there will be 4 radio buttons (present, absent, holiday, hospitalized), which by default will be set to present. The director will go down the line, only needing to click when a student isn't present. At the end, she'll push a save button and the new records will be added.
My proposed method:
1. When the textbox with date input is changed, the subform (or just form and I put the textbox in the header?) will populate itself with a query based on student names whose corresponding startdate and enddate work with the inputted date.
2. The subform will be in continuous view so that it kinda looks like a data sheet but it has radio buttons rather than just spreadsheet cells.
3. When the save button is pushed, some visual basic code in the background will look at record 1, grab the student name and selected radio button value, and add a new record to the AttendanceRecords table with the name, attendance type, and date. Then the code will move to the 2nd record, rinse and repeat until it goes through all of them.
Questions/problems:
1. At its core, is this the Access way of doing something like this? Is there a simpler, more efficient, or generally smarter way of doing attendance?
2. How is step 3 going to work? I can look up how to add new records to a table, but I'm not sure how to move around from record to record collecting corresponding data.
3. Since the AttendanceRecord table uses studentID and typeID rather than actual names and actual attendance types, what's the easiest way of adding the new records with that data efficiently? I'm thinking of making the RecordSource of the subform some sort of linked up set of tables such that if I'm looking at record 1 and the name in the textbox on the subform is "Bob" I can just reference studentID in the background without needing anything on the form at all with studentID?