Insert Recs Based On A Given Number:
Feb 25, 2005
Here's what I'm trying to do:
Form A is used for data entry into SampleTable. On this form I have a some fields including these two: ReplicateA, LabBatchCode.
The database contains another table, IndividualSampleTable. This table contains two fields: ReplicateB, LabBatchCodeReplicateB.
When the user enters information into Form A, the data in fields Sample.ReplicateA and Sample.LabBatchCode are used to populate IndividuallSampleTable. But here's the twist: On IndividualSample Table, IndividualSample.ReplicateB needs to be autogenerated (I guess). Then the field IndividualSample.LabBatchCodeReplicateB is comprised of LabBatchCode entered in Form A, concatenated to the IndividualSample.ReplicateB field.
To make it tricker, the number of records added to IndividualSample table is determined by the value supplied on Form A in the field ReplicateA.
So, Form A could look like this:
ReplicateA: 3
LabBatchCode: CRBR05151998
IndividualSample table should end up with three records like this:
ReplicateB: 1
LabBatchCode: CRBR051519981
ReplicateB: 2
LabBatchCode: CRBR051519982
ReplicateB: 3
LabBatchCode: CRBR051519983
Hope this makes sense! And I appreciate the help!!!!
View Replies
ADVERTISEMENT
Mar 1, 2006
I've been given a file with 165,000 records that was saved as an .xls file. I'm trying to import it into Access so I can view all of the records. When I try to import the file I get an error that "C:AllFiles.xls" is not in the expected format. I've tried the external data wizard and transferspreadsheet method.
I can open the file in excel and see the first 65,000 records. If I copy them and paste them into a new .xls file, I can import them just fine.
How can I get to the other 100,000 records?
Thanks,
Sup
View 2 Replies
View Related
May 22, 2005
I am quite frustrated at not being able to solve this problem in a Db I am developing for my wife. :eek: I have searched through a number of solutions to similar thread questions. I tried using "Distinct" in the SQL statement, Grouping using Max, Min, First and Last all without success.
So my db is a simple 2 table set up with a one to many relationship as follows:
tblPatient Details (RecNo (PK), Surname, FirstName, DOB, Postcode)
tblVisit Details (VisitNo(PK), VisitDate, RecNo(FK), Location, Category, Diagnosis, Drug1, Drug2, etc)
Whenever I run a query I am getting duplicated records of Visit information which should happen. However, I would like to have only one of the visits displaying (first or last doesnt matter.) The Visit No is a unique autonumber.
Can anyone help, any assistance would be greatly appreciated.
View 2 Replies
View Related
May 13, 2005
Greetings all! We have a mentor-mentee database that's been setup with a many-to-many design. We're storing essentially the same data in each table except for the field names. We thought we were good to go but one additional request was made to determine a count of employees participating in the program (regardless of whether mentor or mentee) in each section of the company. We don't want each person counted multiple times if they are both a mentor and a mentee, or if they have >1 mentee or >1. I created a couple of queries to pull unique empIDs from each table to narrow down each table BUT I'm stuck on being able to pull unique id's from both tables.
I first tried the unmatched query wizard from both viewpoints but then I don't get those cases where a mentor is also a mentee. In other attempts, I end up with a cartesian product. I've cruised through the queries section but haven't come up with any examples related to this type of situation.
I need a way to pull both unmatched records from each table while also getting essentially the first occurrence of instances where mentorID=MenteeId. I'm no SQL expert so if someone can think of a statement that would suffice, it would be greatly appreciated.
View 2 Replies
View Related
Jan 21, 2014
I have a form [IUDATA]
I have a add record button.
I have a date field [DATEIN]
I have a text field [DRPNO]
If the [DPRNO] field is empty, I would like the user to have the [DPRNO] field be automatically populated after the user enters a date.
I'd like the format of [DPRNO] to be "dpr YY-XXX"
Where:
YY is the year of the [DATEIN] field and
XXX is number of records in that year.
So for example, if it was the 4th record with a 2013 date the [DPRNO] would be dpr 13-004.
View 12 Replies
View Related
Nov 12, 2013
I would like to insert a sequence number into a query that currently returns 7367 rows.
Here's what it looks like now:
ID_NUM
DTE_FIN_TRANS_RCV
1322
9/27/2012
1322
7/10/2012
1011
7/2/1999
1011
9/22/1999
1011
10/21/1999
Here's the desired outcome with sequences:
ID_NUM
DTE_FIN_TRANS_RCV
SEQUENCE
1322
9/27/2012
1
1322
7/10/2012
2
1011
7/2/1999
1
1011
9/22/1999
2
1011
10/21/1999
3
All I need is a way to number rows with a value higher than the previous for that particular ID_NUM.
View 4 Replies
View Related
Jul 16, 2013
I am currently re-vamping our IT Database and designing a new form for our Hardware but want to make look a bit fancy but struggling.
I want to have a picture based on a Model of a Computer/Laptop being pulled in from one of my Tables but finding it hard to make is possible.
The 'Model' field is looking a field in table 'Inventory' which is a 'Lookup & Relationship' to table 'Model'
View 5 Replies
View Related
Mar 3, 2013
I upgraded my system to Windows 8 and Office 2013, and it's now 64 bit instead of 32 bit as before. Also note that I'm still using Access 2010 as before.So the following problem now arise in this new config that was not there before.
When you do a calculation like '332.16 - 1', the answer is now 331,16, instead of 331.16. Note the point are now replaces with a comma.
Might sound trivial, but everywhere in my code where is have a dynamic SQL like:
Currentdb.EXECUTE "INSERT INTO Tasks (TaskID, MyNumber)
VALUES ("Tsk123", Var1 - Var2)",
the result was always something like:
INSERT INTO Tasks (TaskID, MyNumber) VALUES ("Tsk123", 331.16), but now it is:
INSERT INTO Tasks (TaskID, MyNumber) VALUES ("Tsk123", 331,16), so it is seen as an extra value and the statement fails.
So, yes I can fix this one statement, but I have multiple occurrences of this throughout my app. How do I fix this?
And changing something in my Control Panel - Regional Settings would not be the answer, as on all the client PC's it might be different.
View 3 Replies
View Related
Jul 30, 2012
I have three tables which I have linked with relationships. The linked fields are given the ID number of the parent, where I wanted the value. What on earth am I doing wrong, something stupidly obvious I bet! Access 2007
View 3 Replies
View Related
Jun 15, 2005
I have a form with a combo box this combo box looks up values in my STC table. The STC table has two fields STC & Resolver and each STC has a unique associated Resolver. What i want to happen is after the user has selected the STC from the combo box on the form the associated Resolver be displayed in a textbox on the form. I am ussing the following code but nothing seems to be happening:
Private Sub cboSTC_AfterUpdate()
Dim ResolverSource As String
ResolverSource = "SELECT tblSTC.[Resolver] " & _
"FROM tblSTC " & _
"WHERE tblSTC.[STC]='" & Me.cboSTC.Value & "';"
Me.txtResolver.RowSource = AgentSource
Me.txtResolver.Requery
End Sub
View 1 Replies
View Related
Feb 25, 2007
This forum has been so useful to me so far... but having searched through a load of topics for a few hours now I just cannot find a correct method of having the ability to update two tables from a form.
I firstly created a query that selected the nessary fields I wish to update from the two tables.
And its apparent that I need to use RecordSet to insert the information from the form into the query.
I have found a few different ways of doing this - none of which work for me :( HELP!
Method 1
Private Sub Save_Record_Click()
'Save all entered information to tblprocess request and tblBackupRequest
Dim db As DAO.Database
Dim rs As DAO.Recordset
messageusr = MsgBox("Save this infomation?", vbYesNo + vbExclamation, "Warning you are about to Save this information")
If messageusr = vbYes Then
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT .Server, [Backup Request].Location, [Backup Request].BackupType, [Process General].Type, [Backup Request].[Size(GB)], [Process General].Group, [Process General].[Date required by], [Process General].[Requested by], [Process General].[Date/Time of request], [Process General].Notes FROM [Process General] INNER JOIN [Backup Request] ON [Process General].ProcessID = [Backup Request].ProcessID;")
Me.txtserver = rs!Server
Me.cmblocation = rs!Location
Me.cmbtype = rs!BackupType
Me.cmbtype = rs!Type
Me.cmbsize = rs!Size(GB)
Me.cmbassign = rs!Group
Me.txtrequiredby = rs!Date_required_by
Me.txtrequestedby = rs!Requested_by
Me.txtrequest = rs!Date_Time_Request
Me.txtnotes = rs!Notes
'Clear fields on form to indicate write has occurred
txtserver = ""
cmblocation = ""
cmbtype = ""
cmbsize = ""
cmbassign = ""
txtrequiredby = ""
txtrequestedby = ""
txtrequest = ""
txtnotes = ""
'Close recordset and database
rs.Close
db.Close
MsgBox "This information has been succesfully saved"
End 'return user back to form
End If
End Sub
[B]Method 2
Private Sub Save_Record_Click()
'Save all entered information to tblprocess request and tblBackupRequest
Dim db As DAO.Database
Dim sqlStatement As String
Dim saverecord As DAO.Recordset
messageusr = MsgBox("Save this infomation?", vbYesNo + vbExclamation, "Warning you are about to Save this information")
If messageusr = vbYes Then
sqlStatement = "SELECT .Server, [Backup Request].Location, [Backup Request].BackupType, [Process General].Type, [Backup Request].[Size(GB)], [Process General].Group, [Process General].[Date required by], [Process General].[Requested by], [Process General].[Date/Time of request], [Process General].Notes FROM [Process General] INNER JOIN [Backup Request] ON [Process General].ProcessID = [Backup Request].ProcessID;"
Set db = CurrentDb()
Set saverecord = db.OpenRecordset(sqlStatement)
saverecord.AddNew
saverecord(0) = txtserver
saverecord(1) = cmblocation
saverecord(2) = cmbtype
saverecord(3) = cmbtype
saverecord(4) = cmbsize
saverecord(5) = cmbassign
saverecord(6) = txtrequiredby
saverecord(7) = txtrequestedby
saverecord(8) = txtrequest
saverecord(9) = txtnotes
saverecord.Update 'Write new record to database
'Clear fields on form to indicate write has occurred
txtserver = ""
cmblocation = ""
cmbtype = ""
cmbsize = ""
cmbassign = ""
txtrequiredby = ""
txtrequestedby = ""
txtrequest = ""
txtnotes = ""
'Close recordset and database
saverecord.Close
db.Close
MsgBox "This information has been succesfully saved"
End 'return user back to form
End If
End Sub
and I have even looked into an insert sql statement
[B]Method 3
Dim SQL_Text As String
SQL_Text = "INSERT INTO Backup Request (Server, Location, Type , Size(GB)) VALUES ('#Backup Request.txtserver#','#Backup Request.txtserver#','#Backup Request.txtserver#','#Backup Request.txtserver#') &"
INSERT INTO Process General (Group, Date required by, Requested by, Date/Time of request, General_Type, Notes;"
Docmd.RunSQL (SQL_Text, false)
Method 1 seems to be popular but its returning the message
Run time error '3061'
Too few parameters. Expected 3.
:confused:
View 14 Replies
View Related
Mar 15, 2005
Someone once posted an database on how to do this but I was not able to see how they were able to automatically input the data into a text field.
I know how to do a combo box to query and input those results into a combo box but I am confused how they got the information from the combo box selection to automatically put the other variables into the testbox. They did this without using the dlookup command. In fact it was done without any code.
They showed how to do this automatically without code and with two other methods (one being dlookup). Can someone please help me if it is clearcut as I don't have too much time to search the forums for empty answers (believe me I have already wasted too much time) Thank You
View 4 Replies
View Related
Jan 16, 2014
I import a CSV field which has not preserved the CR/LF when it was exported from BCM Remedy. There is no setting for that. The memo field prints on my report like this:
A custom solution would be developed, that once implemented, could become the standard product in similar situation. Normally would assign to Network Engineering, but will work with Ray and the IPT Team to cost out the solution and get approval to proceed. 2012/05/24 10:44:28 AM PCOLLINS Sent to Ray Massie for review to determine if a solution needs to be proposed, or if they can wait for the National IPT solution to be ready in 2013.
I want to add VBA code that inserts a CR/LF in the memo field before all but the first occurance of a string that looks like a date, the first occurance doesn't need it. I will do it right after I import the CSV file into the table, so it happens only once, and it always prints and displays the CR/LF.
The memo field is called "NBS Update" and the table is called "CCRR Remedy Data"Here is what I have, but don't actually know what to put in to find the date and add a CR/LF:
Code:
Dim db As DAO.Database
Code:
Dim db As DAO.Recordset
Dim srtSQL As String
[code]...
View 14 Replies
View Related
Jul 15, 2014
I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
View 9 Replies
View Related
Dec 6, 2005
Hi I have a couple of tables that should have an automathed value.
What I need is to get the last RecordId from the table from where we get the last RecordNr and insert the next value (RecordNr) in a new record.
I tried to do this autom. in the table by making the RecordNr a numeric Value with a mask for 000 numbers and a +1 as a standard value but it doesn't work.
What can I do? Should it be a function/query?
View 2 Replies
View Related
Apr 14, 2006
I am creating a query which determines the number of days a payment is delinquent. Is there a function I can use in a query field that would look at the numeric field and if the value of the subject field was:
< 60 Days "30-59 days"
< 90 Days "60-89 Days"
<120 Days "90-119 Days"
>=120 Days "120 Days +"
It was my understanding that the IIF function tests for a condition and can only return yes/no results. I seem to remember that SQL had something like a least or most function that would allow me to set maximum value for range of days. I apologize that my question is confusing and hope you can understand what I am trying to accomplish.
Thanks for any help
View 2 Replies
View Related
Dec 5, 2011
I have a report that is set up to count students in a class. Looks like this:
# Student Cert #
1 John Smith 12340
2 Joe Jones 12341
3 VOID 12342
4 James Adams 12343
5 Tom Arnold 12344
I want it to do this:
# Student Cert #
1 John Smith 12340
2 Joe Jones 12341
VOID 12342
3 James Adams 12343
4 Tom Arnold 12344
The row number is just a text box (=1) with the running sum turned on.
On this report I have to report all the certificates used for this class, in order. So I can't put the voided ones at the bottom.
I tried an IIF statement in the text box =iif([student]="VOID", "", 1)
This skips the VOIDs but starts over at 1 (no matter if it is set to "over group" or "over all"):
# Student Cert #
1 John Smith 12340
2 Joe Jones 12341
VOID 12342
1 James Adams 12343
2 Tom Arnold 12344
View 2 Replies
View Related
Aug 23, 2005
Hi,
I've got a combo box that uses
DatePart("ww",[Date],2) AS WkNum
to obtain the week number.
How would I obtain the start date and the end date from just the week number?
Is it also possible to change WkNum to the week number of the selected month?
E.g. if December is selected, instead of week 53, show week 5.
Any help would be much appreciated.
View 2 Replies
View Related
Jul 14, 2006
We want to send letters to clients on behalf of our salespeople on a weekly basis from a list of clients in a table. But some salespeople will have 5 letters go out and some will have 20 go out (depending on what amount the salesperson is able to followup on). I can store the weekly letter quantity number in the table no problem, but I am just not sure how to have the query show 5 letters for Bob and 20 letters for Sue.
I know about the "Top N" setting in query design, but not sure how to set it to read from the table.
Any ideas?
View 4 Replies
View Related
May 14, 2005
Hi Guys and Girls,
While doing a rabbit DB for the missus I have hit a problem, if one of the does gives birth to 3 babies i wish to have the details entered onto a form (not a problem) but i would like to do this:
if number of babies = 3 then fields to display to enter details is 3 on the form, i have my table setup with up to 10 seperate field for the babies per record but am stuck trying to do this
any help or direction would be great
View 8 Replies
View Related
Dec 9, 2005
I want my db to generate our reference # automatically based on input from other fields. The number is the date record entered, the users initials, and an incremental number. Ex. 120505GMM02
My form is based on a query and I have the query generating the first two components from the date entered in reqdate and the initials entered. How can I get an incremental number generated to go with the rest.
The number should start with 01 for each day and also for each user. Therefore when GMM is entering his third record on the 5th it should generate 120505GMM03 even if there were 10 other records entered on the fifth by other users. Each users numbers would start at 01 for each day.
I asked this earlier but thought I would restate my question in hopes of getting my thoughts around this. I appreciate your patience with me.
Thanks.
Gary
View 3 Replies
View Related
Mar 22, 2005
Hi
I have a small database with 4 tables that I am using for the current problem.
The tables are call, parents, mailman, orders.
Call and parents are related by the call ID (a primary key in the Call table.).
Mailman and orders are related by a Unique Id (a primary key in the mailman table.).
Forms involved are frmmain and frmsub.
Frmmain contains the call table information in the main form and parents information in the subform.
When a user enters a call with call ID and enters the operator name and parents information in the sub form,
When a user clicks the OK button on the main form, necessary changes should take place
if they enter the case type in the sub form part of parent information as ‘missing information’ or ‘missing link’ then the parent information with fields first name, lastname, case type, operator information should be inserted into mailman table in appropriate fields.
Simultaneously a record should be inserted into orders( after the record is first inserted into mailman, since both tables are linked with unique id) with the following information.
Orderid being autonumber.
Uniqueid from the mailman table.
Orderdate system date.
Ordertype should be “Mailman”
View 4 Replies
View Related
Sep 20, 2005
Hello all,
I'm having some difficulty obtaining the answers I want from my query. I'd have been more comfortable using filters in excel to find the answers, but my dataset is too large in this instance.
I devised a query which I thought should achieve the results, but based on a subset analysed in Excel the answers are significantly different.
My table has about thirty fields, the query I've produced is this:
SELECT AH_orphans.Product, AH_orphans.Comments, AH_orphans.ESP, AH_orphans.[ESP Parts],
<snip, all other fields in table>
FROM AH_orphans
WHERE (((AH_orphans.ESP)="n") AND ((AH_orphans.FRU)="y") AND ((AH_orphans.EntitlementName) Like "*uptime*") AND ((AH_orphans.Account) Like "nation*") AND ((AH_orphans.Type) Like "Primar*") AND ((AH_orphans.[Is a parent?])="#n/a")) OR (((AH_orphans.ESP) Is Null) AND ((AH_orphans.FRU) Is Null) AND ((AH_orphans.EntitlementName) Like "*uptime*") AND ((AH_orphans.Account) Like "nation*") AND ((AH_orphans.Type) Like "Primar*") AND ((AH_orphans.[Is a parent?])="#n/a"));
What I'm hoping for is the subset which match the following conditions:
- ESP field is not "Y". Possible values Y, N, null
- FRU field is not "N". Possible values Y, N, null
- Only those records from EntitlementName which include the substring 'uptime'
- Only those records from Account which start with the subtring nation
- Only those records from Type which include the substring primar
- Only those records from [Is a parent?] which equal #N/A.
Asking the same questions in Excel I get around 1.5k records. If I use the quoted query on the same dataset in Access I get precisely 11.
Thanks for reading this far! If anyone can spot the flaw/s in my query, or indeed advise that I'm going at it completely the wrong way I'd be very grateful of some advice.
Cheers,
Alex
View 4 Replies
View Related
Nov 23, 2005
I have an address database where people have been allowed to type in any characters they want in place of just leaving a missing field blank. For instance the Zip code was not a required field (don't as me why because I don't know. It just wasn't) so over the years when the person entering the data didn't know what the zip code was they would put a ?, a 0, a 000 or a period or what ever else they could think of at the time.
I would like to add a filter in my query where I could eliminate any thing with less then a certain amount of characters.
I thought something like this would do the trick
Zip: IIf(Len([V_ZIP]<5),[V_ZIP],"0")
But I am getting a data type mismatch in criteria expression. Any ideas of what I am doing wrong?
View 4 Replies
View Related
Jan 16, 2006
Hi all,
Im trying to print a report from a form. I want the report to have the contents of the forms current record.
The underlying table has an autonumber as it's index/primary key and it's this im referencing. What happens is a record is saved and the autonumber is generated.
DoCmd.OpenReport stDocName, acPreview, , Me.RefID.Value ' the value of the forms id box
= [RefId]' the value on the report
The problem i have is this:
when i ask it to return the record which shares the id number of the current form record i get a blank report with no error.
when i ask it to return the record equal to a specific record number - by swapping the varibale for a fixed value i get all records- can anyone suggets what is going on here?
greg
View 3 Replies
View Related
Mar 20, 2006
Hello
I have a field called [Hours]
If a user tries to change the [Hours] I need to check if any of these hours have been assigned to staff in another table.
How do I get to run the query and use the value from the result of the query as the Minimum value?
I would like to
run sql and if the value is being entered into the [HOURS] field is less then show a message and do not change allow the value to change.
I am sure this can be done in VB but I am am unsure of the syntax
I hope U can help
View 1 Replies
View Related
