I need to do a date calculation where dates that fall three months earlier than now() would be moved from showings to a clone of it to reduce clutter in forms and also keep only recent data viewable and archival data in an archive table.
Also, I'd like another table for old properties. When the status of a property becomes closed I'd like to have the information migrate to an archival table. Is there any way to do that?
When I try to transfer (ctrl c + v) data from a table in Excel to a table in Access it loses format.
Exemple: $ 1.000,00 (Excel) become 1000 (Access).
I need to keep the format to make sure that the code works properly.
Let's say that there're 2 workers: Worker A, and Worker B, and they are fixing some engines. There's 2 different engines: Engine A, and Engine B. And these workers are fixing these engines for all day, and in the evening they have to register how many engines they've fixed.
And my job is to design Access database for them. I thought it will be very easy to do; I've created table with columns: Date, Worker, Engine, Quantity - so every day Worker A and Worker B can register, that they have repaired some amount of Engines A and Engines B.
But there's a problem that I also have to register how many Engines have arrived to the factory, and how many of them weren't repaired on time (E.g. 15 Engines A have arrived, Worker A fixed 5, Worker B fixed 5 so there's 5 engines left for tomorrow)
I've figured out that I should somehow create table IncomingEngines with columns Date, Engine, Incoming, Fixed, Undone (field Fixed should be completed automatically every day for every engine - it would be a sum of engines A and engines B fixed by worked A and worker B - so I could fill Incoming field manually)
It's easy to create this kind-of Query, but I can't add column to querry, or edit it.
I am new in using MS Access. We have this thesis that checks the attendance of the professors and then sends the ID number of the professor to MS Access. I am using smsenabler as the software to encode the ID numbers to the database.
The problem is that, I want to transfer the ID numbers to specific table for the professors name. I don't know how to do it. And I can't make the ID numbers as primary keys because it duplicates every time the professor scans his/her ID.
See example below:
Let's say that there're 2 workers: Worker A, and Worker B, and they are fixing some engines. There's 2 different engines:
Engine A, and Engine B. And these workers are fixing these engines for all day, and in the evening they have to register how many engines they've fixed.
And my job is to design Access database for them. I thought it will be very easy to do; I've created table with columns: Date, Worker, Engine, Quantity - so every day Worker A and Worker B can register, that they have repaired some amount of Engines A and Engines B.
But there's a problem that I also have to register how many Engines have arrived to the factory, and how many of them weren't repaired on time (E.g. 15 Engines A have arrived, Worker A fixed 5, Worker B fixed 5 so there's 5 engines left for tomorrow).
I've figured out that I should somehow create table IncomingEngines with columns Date, Engine, Incoming, Fixed, Undone (field Fixed should be completed automatically every day for every engine - it would be a sum of engines A and engines B fixed by worked A and worker B - so I could fill Incoming field manually)
It's easy to create this kind-of Query, but I can't add column to query, or edit it.
Hey everyone,
I am a complete lamen on this, I am finding. Here is what I want to do.
I have an access database file with two tables inside it: Distributions AND Plan Data
In both tables, I have the following fields:
CRS ID Number:
Plan Name:
Company Name:
Company Address 1:
Company Address 2:
Company City:
Company Zip:
Company Federal Tax ID:
Company State Tax ID:
I want to be able to type in a 3-digit CRS ID Number in my form for DISTRIBUTIONS and for it AUTOMATICALLY to find that CRS ID number in PLAN DATA and populate all these common fields using data from PLAN DATA, inserting it into the DISTRIBUTIONS table.
The reason I need it to pull the data from PLAN DATA and insert it into DISTRIBUTIONS table is because we are using some out-of-access features that require us to have all data in one table. Thanks!
PLEASE ANSWER ASAP!!!
I was wondering if there was a way to automatically transfer data from one table into another. Specifically, if I have two tables, one called Students and one Alumni, is there a way I can click a button or something to transfer a student's data file from the Students' table into the Alumni table? Without having to delete the student's file from Student and retyping it all into Alumni?
Also, is there a way to, for instance, type individual monetary amounts into a single entry in a table and have the total automatically summed? Like, if I have a table for Donors, and every time one person makes a donation, add that one donation to their personal information and have another field that gives the total amount of all of their donations?
Thanks!
I have one table with the following records:
Name Surname School Date1 Date2 Date3
John Smith London 12/12/11 11/11/11 10/11/11
Mary Wayne Harvard 11/10/09 12/10/13 10/11/11
I would like to design a Macro which can create another table like this :
Name Surname School Date
John Smith London 12/12/11
John Smith London 11/11/11
John Smith London 10/11/11
Mary WAyne Harvard 11/10/09
Mary Wayne Harvard 12/10/13
Mary Wayne Harvard 10/11/11
That is duplicate the records, and create another table, with all the records and each record with just 1 date.
Any way to transfer the data in an unbound textbox into a separate table.
User enters an amount into a textbox2, and then it does some calculations that involves textbox1 and textbox2. Finally, textbox3 has the final answer. Can textbox3 overwrite textbox1's data on the table and on the form?
Also, is there an "auto date" feature in tables? If new data is put into tables, Today's date appears on the date column?
Hi All,
Im trying to make up a vb command to send date from a query based from to a table i have no clue how to even start.
The database is to record when a item is inspected so a user would type the date in to a unbound box and then at a click of a button the date along with the id code will transfer in to a table so that i can compair the difference between the dates of any one item
is this possible and how???
Many thanks in advance
I have a form with 8 textboxes which users input and transfer all these data to a table when pressed on a button(VBA Code)
Below is the vba code i used, which works perfectly
CurrentDb.Execute "INSERT INTO tblreverse(txtRequestedBy,dtRequestedDate,txtPrevB illTo,txtOrderNo,txtInvoiceNo,txtLicenceNo,txtInst ruction,mmremarks) " & _
"values('" & Me.ccReq & "', #" & Format(Me.ccDate, "yyyy/mm/dd") & "#, '" & _
Me.ccPrev & "', '" & Me.ccOrder & "', '" & Me.ccInvoice & "', '" & Me.ccLic & "', '" & Me.ccInst & "','" & Me.ccRem & "')"
In the table tblreverse, i have also a field name which is "attAttachment".
What i'm trying is to insert a textbox in my form names ccAtt and when click on the button, the attachment goes to the table.
Here;s how i put the code
CurrentDb.Execute "INSERT INTO tblreverse(txtRequestedBy,dtRequestedDate,txtPrevB illTo,txtOrderNo,txtInvoiceNo,txtLicenceNo,txtInst ruction,mmremarks,attAttachment) " & _
"values('" & Me.ccReq & "', #" & Format(Me.ccDate, "yyyy/mm/dd") & "#, '" & _
Me.ccPrev & "', '" & Me.ccOrder & "', '" & Me.ccInvoice & "', '" & Me.ccLic & "', '" & Me.ccInst & "','" & Me.ccRem & "','" & Me.ccAtt & "')"
But it does not function.
I am trying to export into a temp table (all text fields because it will be going into a text export later) and I'm having difficulty adding 0:00:00 onto the value of "ApptdateLast" for the update...
INSERT INTO cbt_Export_Temp ( TransactionType, ID, ApptdateLast )
SELECT "Add" AS TransactionType, "BC" & [TransId] AS ID, dbo_Info.ApptdateLast & " 0:00:00" AS ApptdateLast
FROM dbo_examInfo
There are two tabs named Table1 and Table2. In actual there are two tables in Access database named Table1 and Table2. How the data is stored in ACcess tables, I have made two tabs in excel workbook. Now I want Access VBA code that will check if data in Reference field of Table1 matches with any of the data in Reference field of Table2.
If it matches then change the status of the corresponding record of Table2 with either "Withdrawn","Obsolete" or "Updated". SO it depends upon which field out of "WIthdrawn","Obsolete" and "Updated" in Table1 stores "Y". At a time only one of them will have "Y" and rest of two fields will have "N" as shown in the sheets.
As in the example, now Reference "R566" of Table1 matches with Table2 Reference so the status field in Table2 for that record will be "WithDrawn".
I have an MS Access accdb with linked SQL Server 2012 ODBC tables. I am working on a procedure to copy data from local tables to these linked tables (identical schema). I did a simple
Code:
DoCmd.RunSQL "INSERT INTO linkedTable SELECT * FROM localTable"
This works, but is very slow. Way too slow. (INSERT copies the data one record at a time).
I would like to copy the data in a bulk operation, or operations that I can execute programmatically.
How to make a form open with data from a table based on a date and time in the/a table?
View 7 Replies View RelatedHi,
I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?
Thanks for your support
GinnyP
Ok 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?
I have one DB that is used for creating/storing customer ID's, and another DB that is used for creating/storing job information for customers.I have linked the table from the customer DB to the job DB.
There is a table in the job database that holds customer name and ID, and some VBA that generates unique job codes.
Is it possible to have the data from the linked table automatically update into the existing table?
Is there a way to append a pivot table to a table or possibly make a query based on a pivot table? I need to get a count of Part Numbers and I need the average price for all these parts. Additionally I want to ignore a count of less than 3.
Also I am having trouble filtering on the count in the pivot table... haha, so I was gonna Query on it later on.
How to update data from one table to another table using form.
I have data coming from design team in Database 1 and using form i want search data and assign the job to a person and store it in the database with his name. I have to do this because database from design team is read only.
I have an "order details" table that needs to populate a field called "Voucher" with data from another table called "codes". The "codes" table also has a true/false field called "allocated" because once allocated the code cannot be re-used.
I am trying to work out how to automatically allocate the next unallocated code in the "codes" table to each record in the "order details" table when that order details record has a DiscountID of "92".
Order Details Table Fields and conditions/criteria:
ID - primary key
DiscountID - only when the DiscountID = 92
Voucher - only populated when Discount ID = 92
Codes table Fields and conditions/criteria:
ID - primary key
code = text field with a code like "einstein01", "einstein02"
Allocated = False
Is there a way to put the next available code into the order details record then mark that code as allocated in the codes table. Then, move on to the next order details record that has a discountID = 92, input the next unallocated code and mark that code etc. etc.
Ideally, I would like to do this to happen via an event when the Order forms button "Close" is clicked.
Anyway, I would call myself intermediate level at best with Access. I never expected to have to do so much with it, but when my bosses found out I could do Access basics, they began demanding more and more.
I manage an EMR from which a datapull occurs on discharge to various access databases.
They wanted me to add a triage patient data pull to track what procedures are being done to triage patients. So I built a database with the following 2 tables.
tblTriageVisits and tblTriageProcedures
tblTriageVisits has the following fields
-Patient ID
-Export Time
[Code].....
The tblTriageVisits stores all the patients triage visits. The other table stores what was done to each patient on those visits
It should also be noted that this EMR exports times in number of seconds since 12/31/1975, so TimeProcFMS is the number of seconds since that date for example.
The pulls work great and the duplicate record elimination method works great.
I have to design a couple of different reports based on this data and one in particular has pretty much flumoxxed me.
I need to make a report that lists each patient by date they were there and then shows what was done to them. I have tried several different variations of this as both queries and reports. The report breaks down when I try to get it to show just those procedures that would be for that triage visit. I got to the point where it would group by day and then sort the patients alphabetically. Then I added in the visit fields. And it would put all triage procedures for that patient from all their visits in each visit.
So, I was like great, let me filter out those that don't occur between the two dates, admission and discharge
I used the following two formulas (the first one on the procedure name field the second on the procedure date field)
=Switch(DateAdd("s",[TimeProcFMS],#12/31/1975#)>=[Admission DateTime OB DateTime] And DateAdd("s",[TimeProcFMS],#12/31/1975#)<=[LD Discharge Date/Time DateTime],[Procedures FMS])
=Switch(DateAdd("s",[TimeProcFMS],#12/31/1975#)>=[Admission DateTime OB DateTime] And DateAdd("s",[TimeProcFMS],#12/31/1975#)<=[LD Discharge Date/Time DateTime],DateAdd("s",[TimeProcFMS],#12/31/1975#))
And they work, sort of. They do eliminate those procedures that occur outside of the date range I am looking for. However, the access report still leave spaces in the report where the filtered out items would be.
Did I approach this problem correctly? And if so, how do I get rid of those spaces?
Note, I can read and mostly understand SQL, but am not comfortable working in it yet. And the Access SQL box gives me a headache, it is so jumbled up.
I'm having trouble with a new project I'm working on. The application is mainly going to be used to display data, which comes from a linked table. It has to be a linked table (in my opinion) because it's replaced once per week from a fresh data dump. For each of those records, though, there will be notes made in a local table named "Custom-Data". My trouble is displaying a mix of information from the linked table, "Roster", and "Custom-Data" because linked tables can't be assigned a primary key.
Essentially, when a record is pulled up, a bunch of data from "Roster" will be shown in addition to the comments from "Custom-Data".
Some days ago I made it by using "query", but now I forget it how I make the relation on this situation. Here is my problem.I have 3 Table on mdb file, named Table: A, B, Status.Table Status have One Filed with 1 Data: Dishonor
Table A have three fields
Sl Number: (Auto Number)
Status: Lookup wizard-data of (Table-Status)-Default Value is "Honor"
Amount:Number
Table B have two Fileds
Sl Number: Number
Status: Lookup wizard-data of (Table-Status)
After Entering Some data on Table A it's Look like as:
Asl numberstatusamount
1Honor5222
2Honor855
3Honor988
4Honor7777
5Honor777
6Honor9999
[code]...
Now I want to change the Status of SL Number Honor to Dishonor so I fillup data on Table B is as like following
sl numberstatus
5Dishonor
Now how I can get the result as following by using query:sl numberstatusamount
1Honor5222
2Honor855
3Honor988
4Honor7777
5Dishonor777
6Honor9999
7Honor6666
8Honor7777
9Honor666
I have three tables: Vehicles; Vehicle Reallocated; and Vehicles Retired. I have a form that runs a query to find all the info in the Vehicles tbl that is not "Retired", not visible in the form. I then have the option to toggle to a Reallocated or Retired form. When i toggle to the reallocated form, i have the like fields in that table (ie Van #, Vin, Make etc) pulling the info from the hidden subform with the vehicle query, so i do not need to fill in repeat data. However, when i add a reallocated date and the new clinic that vehicle is for, i get the record ID for the vehicle reallocated table as expected, but when i save none of the data moved over from the query saves in the record?
How to get all the data on the reallocated form to save?
I am working from an existing database which is just two table. The main table has a massive amount of redundancy and duplication and needs splitting into, at first glance, 5 tables.
After I have run my various make table queries and added a Primary Key and FK field to the new tables how do I populate the FK with the Parent PK.
I thought I could simply add all the fields from the new table and then create an adhoc join in an update query to populate the PK to the FK. When I do this however I get "You are about to update 0 records"
I have tried the table analyzer but it doesn't give the correct options to split the table the way I need.