Is it possible (and logical) for me to create a table with a calculated field from 2 other fields in the same table? Example; I have a long time field (mm/dd/yyyy hh:mm:ss) aka StartTime, and a long time field EndTime. I would like to subtract the StartTime field from the EndTime field to get the total time elapsed, but in hh:mm:ss format. I can get it in hh:mm format, but I really need the hh:mm:ss format. Any Ideas?
View RepliesI created a form in Access that retrieves data from a table. Inside the form, I am able to access/populate data fields with data from the table. I also have data fields, inside the form, that requires key-in data. I have some how lost the ability send all data field information to a second table and clear existing data fields for new entry.
Questions: What settings, code or buttons can I use to send/store data field information to a new table? What settings, code or buttons can I use to automatically clear all data fields from my form once data has been sent to the new table?
Allen Browne has an page explaining how to set up a database relationship of many to many using a connection table at the following link
[URL] .....
I can set up what he has done easily, but I want to be able to show the information much like the matrix he shows at the top of the page and also include in the query or form "sports" that no one has checked.
I am working with some chemical compounds in a database I am building. I grabbed an html file off the web on a listing of chemical compounds...converted it to ascii and imported it into ACCESS. Rather than write a Perl routine I was wondering if anyone out there had some sql code that might handle it. The first 3 records look like this and are contained in one field. I am trying to break this guy into 4 fields.
0001 Acetaldehyde 75-07-0 AB1925000
0002 Acetic acid 64-19-7 AF1225000
003 Acetic annhydride 108-24-7 AK1925000
The fields would be named in order as:
GUIDE 002
CHEMICAL_NAME Acetic acid
CAS_NO 64-19-7
RTECS_NO AF1225000
The first two fields are left justified but the number fields are rather zigzagged throughout.
Anyone have a cure for this beast?
I thank you in advance.
the raven man.
I'm working on a database that has to be done in Access and in coming up with a unique ID for each worker I want to take the first letter of the first and last name and the last 4 digits of their social to create the UID for the table. So for FIRST_NAME:Homer LAST_NAME:Simpson with SOCIAL:123-45-6789 his UID would be HS6789. Is there a function within Access that would allow me to do that, or is that something that's just too advanced?
View 9 Replies View RelatedI am not sure that I am in the Correct Section for this question but I am sure I can be forgiven for that :
I am attempting to build a db for a friend who has all his data in one table. During normalisation I have seperated the various threads of data into various tables of usable information. My problem is that I need to seperate his clients fullname into First & Surname i.e. Fullname: Mick Burke to FirstName: Mick and Surname: Burke - in other words make two fields from one.
Any help in doing this would be well appreciated.
I need to create a bunch of new records that will contain some existing data and some blank fields.... what I want it to look like:
ACCOUNT | NUMBER | PERIOD | AMOUNT
4G334223 123-224-2212 1/1/2006
4G334223 123-233-2334 1/1/2006
What I want is the query to pull the account and number from the db, then add the date in automatically (not the current date, just a specified criteria to signify jan 06, feb 06,etc) and leave the amount field blank (which I will then add in the corresponding values manually).
Hi there,
I'm making a document control database where the a unique reference number is automatically generated from 2 tables and updates a 3rd table.
xxxx or yyyy/lookup in 2 table/autonumber
I have been trying to do this is a form but because the control sauce is a calculation 'Text1&Text2&Text3' I am unable to update the 3rd table with the combined reference number.
I have limited knowledge particularly of VBA coding
Any help much appreciated
I am trying to finish off my database with this last query. I have two fields named date_to and date_from, within a table tbl_non_avail . I need to show people who are available not between the from and to dates.
View 3 Replies View RelatedHi All!
I am trying to create an updateable non-calculated field in a select query. Specifically, I want to create a Yes/No data type called "Fail".
This is a multiple-user application so I can't solve the problem by using append and delete queries to modify records in a table.
This has turned into a much stickier problem than I anticipated. I would appreciate any insight or advice you have.
Hi
I have a single form for which I need to create 2 records from in the same table.
On the second record when created I need to change some of data contained in the fields - How do I do this:
I give an example. On my form I have a tick box - if true it enables 2 text boxes and will need to create 2 records within a single table.
I need the first record to include the data contained in the first text box and the second record to contain the data from the second text box.
I have tried to use update queries but have had no luck. Anybody have any ideas?
thanks
I have a table, which when i first set it up i decided that a couple of the fields had to be mandatory.
So, i set the "Required" property of the field to "Yes" (at table level - which is probably poor practice?)
I now have a form that allows a user at the front end to enter items in the table.
If they complete all the fields on the form i have used macro builder to save the record, present a nice message and move to a New Record. (3 elements in all)
However, if they try to save the record having not completed the "Required" fields then they get an error message instructing them the field is required, but then the macro crashes.
So what's the best way to approach Mandatory fields? Is it best to leave the "Required" property at table level set to No and then have something at form level which checks they have entered a value in the field? (i'm guessing this is probably the correct approach)
I tried removing the table level condition from the [Field] and adding this as the first argument of the macro builder which saves my record:
Code:
IF [Field] = "" Then
MessageBox "You Must Complete Mandatory Fields"
Stop Macro
End If
But my macro still completes and saves the record...
I'm creating a catalog that shows all the equipment we use in our company. The problem is that most of these equipment don't have the same properties, or fields. For example, all of them have a code, model, supplier and price.
However, most of them have different properties like size, orientation, or material.My first attempt was using one-to-one tables.
My database is formed by a main table (catalog), a type table (which lists all the types of equipments the table has, e.g. control valves, tanks, field instruments, etc), and smaller tables for each type of equipment (the table for control valves has the size and material field; the table for tanks has connections and orientation field; and the table for field instruments has the fields of dial size and connections).
All of these small tables are connected to the main table using a one-to-one relationship.My question here, any better way to store and access these type of data.
I have a field I need to create on my report that needs to be based on what is in another field on my report.
If Note (that's my field name) = 1,2,3,4,5, or 6, I want to sum a field called PlateNumbers.
What is the syntax?
I am writing a small program for my sons school for there athletics day. We want to register all the students on a data basis. I have a form where you capture all the child's information and then tick box for each event that there will be for the day. So we will only tick the events that the specific child will participate in. I design the table to just import the information from each school from a excel spread sheet directly into the table. What my goal is to print report for each event that will only show the child's names that has register for that specific event and all the different age groups separate. I am stuck at the query and can't seem to get my mind around how to define the query.
View 2 Replies View Relatedway to merge various fields from a table into just one using a query.The purpose will be for easier copying and pasting in to an email.So we have a table to stores information such as Site Contact, Address 1, Address 2, Address 3, Postcode etc.
I want to be able to run a query that will put the address in to just one box, either in the query or on a report then it's much easier to just highlight the full address and copy and paste it into an email. Rather than copying each field individually.
I am trying to create an expression in a query to sum only the # of hours a student attended between two date fields. I do not want to use a parameter because each student has different start and midpoint dates and I need to see all of them in one list.These are the fields I'm using in the query:
Student Name
Start Date
Midpoint Date
Hours
It keeps giving me "0" or if I move the () around it says the correct syntax is [NOT]
Expr1: Sum([Hours]) between [Start Date] & [Midpoint]))
In the code below I am creating a record with the INSET INTO statement and then Updating with additional info in various fields. it is not working the way I thought it would, so I am trying to create the record in it's entirety.
Code:
SQL_Grade_GUSD_ID = "INSERT INTO Grades (GUSD_Student_ID) VALUES (" & Me.GUSD_Student_ID & ")"
SQLM1_1_ELA = "UPDATE Grades SET Grades.Subject = ""BM1(ELA)"""
SQLM1_2_ELA = "UPDATE Grades SET Grades.Type = ""Exam"""
SQLM1_3_ELA = "UPDATE Grades SET Grades.Score = ""0"""
SQLM1_4_ELA = "UPDATE Grades SET Grades.Nam = ""GUSD BM-1"""
DoCmd.RunSQL SQL
[Code] ...
I am running to syntax problems when I try to USE the INSERT INTO to create the record with all the info in one statement.
Code:
SQLM1_1_ELA = "INSERT INTO Grades ( GUSD_Student_ID, Subject, Type, Score, Nam ) " & _
"SELECT (" & Me.GUSD_Student_ID & ")"" AS GUSD_Student_ID, ""BM2(ELA)"" AS Subject, " & _
"""Exam"" AS Type, ""0"" AS Score, ""GUSD BM-1"" AS Nam " & _
"FROM Grades"
I am Getting this error:
Syntax error (missing operator) in query expression '(12345)" AS GUSD_STUDENT_ID,
"BM2(ELA)" As Subject, "Exam" AS Type, "0" As Score, "GUSD BM-1" AS Nam From Grades'
I'm working on a form with almost a hundred various fields (it's what my employer needs). He wants to be able to create new records with much of the same information as the old ones by clicking on a button that will copy the information to a new record, then clear certain specific fields (or some process that will safely create the same result). I don't need specifics on how to write the same line of code for each and every field I wish to clear.
Say I have a form with 5 fields: part_number, part_owner, procedure, file_name, and date.
How could I copy the record to keep the fields part_owner and procedure the same, but clear part_number, file_name, and date?
All of the fields are from the same table (no, it's not normalized...).
I have a form which I am using child/master fields to create a filter (see attached pic)
For the date ranges I need these to be
>= Start Date and <= Actual Finish Date
How can I set the criteria for these two fields, so that when someone picks a date it knows it's from or before the date entered.
How do I create a table that can cross index items in another table. Maybe I am not using the right terms here so let me show a small example.
Say I have a tables of words.
tblWords
numWordID
txtWord
Then I have some entries, all more or less synonyms of each others
fresh
new
clean
Now I want to create a cross-index table, related to the table "tblWords" where I can select synonyms from words already in the table "tblWords", so if I for the word "fresh" add "new" and "clean" as synonyms or entries, if I then go and look at the word "new" it will already have the synonyms "fresh" and "clean", likewise the entry "clean" will then have the synonyms "fresh" and "new".
Kind of a many to many relationship junction table but only with one table!
I hope my explanation have not been to confusing, but let me know if you need a clarification.
Thanks
I have 2 tables.
Table 1: Master table
Table 2: Temporary table
This is my current process:
Every quarter I run a report that pulls loans that meet specific criteria.I export this report into excel (the loans fall into column A)I add a file number and box number in columns B and C.I import the excel spreadsheet to table 2 (they're linked so I don't need to import, it's automatic)
Now that I have the updated information back into the database (table 2), how can I get this information back into table 1? The excel spreadsheet only contains a few loans that need to be updated in table 1. I have tried creating an update query with both tables linked and use the "update to" field. However, when I tried to run the query, it says I have 0 records updated.
My update query is as follows:
Field: Access Bar Code
Table: Table 1
Update To: [Table 2].[Access Bar Code]
Field: Access Box Number
Table: Table 1
Update To: [Table 2].[Access Box Number]
Basically I'm trying to have the query update specific fields in table 1 based on the information from table 2.
I don't have any database experience whatsoever so please go easy.
I'm guessing this kind of this is extremely simple for all of you.
I'm constructing a database of network resources and devices and I'd like to automatically update the values in one field based on the values of a field in another table.
The first table is called "IP" and the fields are called "Address", "IP Type" and "Device".
The second table is called "Devices" and contains the fields "Name", "Description", "Asset Number" and "IP".
Here's an example of the tables: (ignore the "code" tag. i've only used it to align my columns properly)
DEVICES:
NameDescriptionAssetIP
XserveFile Server107203.30.144.75
ProliantXDHCP119203.30.144.15
IP:
AddressIP TypeDevice
203.30.144.75Static
203.30.144.15Static
What I want is for the Device field in the IP table to automatically update it's values based on the values found in the Devices table. In this case, the values that should appear in the Device field in the IP table are "Xserve" and "ProliantX".
I've searched through but haven't found a complete solution, just little pieces which I'm too inexperienced to put together myself.
thank you
-Tim
I have a totalquery that runs fine and give me the sum for both fields I'm looking for but I can't get the outputs to fill the fields on the form. I have tried the Dcount query in the control source but that just returns an error and locks up access.
Code:
SELECT [Tble-wcDelays].Causedby, Sum([Tble-wcDelays].HoursDelay) AS SumOfHoursDelay
FROM [Tble-wcDelays]
GROUP BY [Tble-wcDelays].Causedby, [Tble-wcDelays].LinkingID
HAVING ((([Tble-wcDelays].LinkingID)=[Forms]![Frm-ePlusCent]![cleanID]));
That is the query.
I have a database that will register the emails coming in and what time, also the time, date out and person.
I have a form with the fields to be filled in and a submit button. There are some fields that are automatically filled in and others need manually fill in.
below that part is a sheet (subform in the form of sheet) that should be filled in with the above data. Once it is there, it should empty the fields so they are ready to be filled in again. If I fill new data and press the submit button, it should go to the next available row.
I have a copy of the back-end that gets a search key error 3709 on two records. In other words, I can duplicate the problem.
The interesting part is that I can update any other field on both these records and save the record, but when I try to change two specific fields, I get a Search Key Error and have to ESC out to continue (basically UNDO the change). Both fields are text fields with lengths of 7 characters and 255 characters, and both are COMBO Boxes on the form.
I tried to focus on the form think there was an issue in the code. I can definitely TRAP the 3709 error on the ON ERROR event on the form using "if dataerr = 3709", but then I tried something even simplier.
I went directly to the table and to each of the records. Again I can update any other field in the record but these two specific fields. When I try to change either of them and move to another record, you get a Search Key Error 3709.
By going to the table record directly I'm as low level as I can get. There are no validation rules on either field at the database level. If it was truly CORRUPT would it let me update any of the other fields on either of these records? One is an empid (not a primary key but is indexed with duplicates okay and not required), and one is status code (not a primary key but is indexed with duplicates okay and not required) so they're no critical fields, but something is keeping them from CHANGING.
Just tried something else; deleted the INDEXES on both the fields. Now it works! I am completely confused now because it really wasn't a corrupt record, but the indexes are causing the problem. Do I need to update the indexes somehow when the users selects a new empid or status code?