Insert X Records In Table Based On Value From Another Table

Jan 9, 2007

Newbie question: I have a database where we track dispatched with two tables, one called Slots, the other called Dispatches.
In the slots table there are 2 fields, one called Week (which hold the Monday date for each week in the year), the other called Available (in this field we want to manually enter a number that will tell the system how many dispatches we can do that week). We want to automatically take the number from the Slots.Available and enter X number of blank records in the Dispatch table with the monday date.
How can this be done?
Then we will create a form that can be filled in with the balance of the information.
Thanks

View Replies


ADVERTISEMENT

Insert Records Into Table Based On The Select Criteria From The First Table.

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

Modules & VBA :: Parent Table Revision History - Insert Multiple Records Into Child Table

Sep 8, 2014

I have a parent table (tblLabels) and a child table (tblRevision) where the revision history for the parent table is kept.

The parent table is populated via an excel import and may have several records imported at once. Instead of having the user manually enter a new record note in the child table for each record imported into the parent table, I've created a form that collects the necessary data (date, person who added the record, person who authorized the record, and notes) and then creates a revision history for each new record.

This is what I have so far:

Code:

Private Sub cmdAddNotes_Click()
Dim strSQL As String
Dim RevisionDate As String
Dim RevisionRevisedBy As String
Dim RevisionDesc As String

[Code] ....

When I run the code nothing happens. No error, no new records create, etc. My suspicion is that I may need to use an INSERT INTO query instead of an UPDATE query, but I'm not sure how to go about matching up the foreign keys if that's the case.

View 14 Replies View Related

Insert Table Records Into Another Table

Mar 5, 2008

I have this query below:

INSERT INTO TEST_DOC
SELECT *
FROM MPI_ADDSS_IFF;

I currently have 10 tables in my database (All with the same colunm names and formats) however i want to click a button that will put all the tables into TEST_DOC, instead of doing it one by one. To do this this i would like some code either in SQL or VBA that will do this.

Any ideas or help?

Kind Regards
Hewstone999

View 1 Replies View Related

Insert Records From One Table To Another

Apr 10, 2007

hi, i want to convert some below mentioned format into msaccess tables.

the exact scenario is that i have some text files from old applicaiton (pascal based) which is no longer working due to hardware compatibility problem...

which has generated some text files under below mentioned format...

[CLIENT]
L1_CUSTOMER_REFERENCE=AbreyCatherine
L1_TITLE=Ms
L1_SURNAME=Abrey
L1_FORENAME=Catherine
L1_DOB=1 Jul 1957
L2_DOB=9 Jul 1954
L1_ADD1=165 Downhills Way
L1_ADD2=Tottenham
L1_ADD3=London
L1_PCODE=N17 6AH
Q_L1_SEX=F
L1_CLCODE=CA1
L1_MSTATUS=M
L1_RELIGION=Christ.
L1_HASPARTNER=Y
L1_PARTNERNAME=Gian Paolo Caddeo
L1_PHONEH=0181 889 3870
L1_SALUTATION=Catherine
L1_OCC=Management Consultant
L1_EMP_STAT=S
L1_HEALTH=G
L1_SMOKE=Y
L1_NATIONALITY=British
L1_NRA=60
L2_OCC=Heating Engineer
L2_EMP_STAT=S
L2_HEALTH=G
L2_SMOKE=N
L2_NATIONALITY=Italian
L2_NRA=65
FactFindDate=30 Oct 98
TermsOfBusDate=30 Oct 98
LastVisitDate=18 Oct 95
LastLetterDate=14 Dec 99
Consultant=KP
AdminRef=AT
BestCallTime=Evenings
ClientType=UKCL
L1_WillDate=1993
L2_WillDate=1993
MailAllowed=-1
LastMailDone=0
L1_NINumber=YZ070258B
SeparateTax=
ContactFrequency=26
ManualSelect=
ClientLastUpdated=29 Jun100
[CLIENT]

each and every record starts from [CLIENT] and First part is the field and the part " = " is the value for the respective field.

now, i need help in converting that information into msaccess tables and its respective values...

like ...1) first part will become the field of table and
2) 2nd part after " = " will be the value for the respective field of the respective table.

i hope that this has given clear picture. where i want help from an expert...so, kindly help me. That will be great indeed to me.. thanking yours...om

View 9 Replies View Related

Adding New Records To Table Based On Values In Another Table?

Aug 19, 2015

I'm developing a database for the hospital I work in. One purpose of this is to keep track of patients with temporary invasive devices (there are many types such as urinary catheters, ventilators, etc.), specifically how many days each patient has a device for. Each device is associated with one patient only, but one patient may have many devices. Here's how the associated tables are set up:

1. tblPatients - PatientID (PK), LastName, FirstName, DOB,...
2. tblLocations - LocationID (PK), LocationLabel,....
3. tblDevices - DeviceID (PK), DeviceType, Device, DeviceDesc
4. tblDeviceUse - DeviceUseID (PK), DeviceID (FK), PatientID (FK), LocationID (FK) (where in the hospital was device inserted, e.g., operating room, bedside, etc.), DeviceStartDate, DeviceEndDate
5. tblDeviceDailyLog - DeviceLogID (PK), DeviceUseID (FK), DeviceDate, PatientLocID (FK) (area in the hospital that patient is in)

All primary keys except for PatientID & LocationID are Autonumbers; and the tables are linked appropriately.

Whenever an entry is made into tblDeviceUse, I want there to be new records to be automatically generated in tblDeviceDailyLog for each date between the Start and End Dates. For example, patient A123 had a urinary catheter from 1/1/2000 to 1/10/2000 that was inserted while the patient was in the ICU, but the patient was moved to the Medical Ward on 1/7/2000. So tblDeviceDailyLog should have 10 new records associated with this device, one for each calendar day, with the appropriate location for each day.

View 3 Replies View Related

Modules & VBA :: How To Insert Records Into A Table

Dec 9, 2014

I am reading through a table looking for duplicate values in the FullName text field. I want to store in a new table the duplicate records I find, storing just the MemberNumber and the FullName. When the VBA code runs and finds duplicates, the SQL statement to insert a new record into the Duplicates table asks for the value of LastMemberNumber and LastFullName when it already has the values and has displayed them in the message boxes! What am I doing wrong?

The code is:

Private Sub Command0_Click()
Dim rs As DAO.Recordset
Dim dbs As Database
Dim LastMemberNumber As Integer
Dim LastFullName As String

[code]....

View 7 Replies View Related

Insert Empty Record Between Two Records In Unordered Table

Aug 1, 2005

Is anybody can help me to solve problem with inserting empty record between two records in unordered (non-indexed) table - like in Excel work sheet.

Regards,
Gennady

View 4 Replies View Related

General :: Command Button To Insert Records From One Table To Another

Aug 11, 2013

I have two tables, one is GENETIC and the other one is BoneSampleDNAprofile. Both tables have the field CY-Code. I want to create small form with 2 text box fields and one command button on the form to be able to insert CY-Code from the table GENETIC to designated DNA_LAB_Code in the table BoneSampleDNAprofile.

In that small form I would type DNA_LAB_Code and CY-Code where after pressing command button will insert CY-Code for the designated DNA_LAB_Code. This is continuous process after our Laboratory receive results from DNA laboratory. I have attached two .png files which are example of two tables.

View 2 Replies View Related

Queries :: Insert Into - Copy Records From Another Base Into Existing Table

Apr 15, 2014

I'm trying to copy records from another base into existing table in current base by:

Code:
DoCmd.RunSQL ("INSERT INTO pivot (RFO_CLIENT_ID, FOLDER_DATE_CREATE, start_time, end_time) SELECT (RFO_CLIENT_ID, FOLDER_DATE_CREATE, start_time, end_time) FROM svod IN 'Z:NPSNPS - Operator - 1.accdb' ")

But it doesn't run. Says insert into syntax error.

View 6 Replies View Related

Modules & VBA :: Delete Records From A Table Based On Records In Another

Feb 7, 2014

I have a table InvPrice and Updated Pricing

Need to delete all records from InvPrice that Match UpdatedPricing

InvPrice.StockCode = UpdatedPricing.StockCode
InvPrice.PriceCode = UpdatedPricing.StockCode

I have tried something like this...

Dim dbs As DAO.Database, sql As String, rCount As Integer
Set dbs = CurrentDb
sql = "DELETE * dbo_InvPrice Inner Join (dbo_InvPrice Inner Join UpdatedPricing on dbo_InvPrice.StockCode = UpdatedPricing.StockCode ) ON on dbo_INvPrice.PriceCode = UpdatedPricing.PriceCode "
dbs.Execute sql, dbFailOnError

View 14 Replies View Related

Tables :: Use Insert Into Command To Insert Filepath Into Table That Adds Blank Label

Aug 28, 2014

I am at work, and I have acquired a database that prints labels. They now want the database to be coded so that after certain labels are printed the database will print a blank label. I have the code figured out as a Do While statement in order to print the blank label. The problem I am having is that I am trying to use the Insert Into command to insert the filepath into the table that adds the blank label.

|DoCmd.RunSQL "INSERT INTO Rod_tmakLabels ( Print, [Order] ) SELECT Yes AS Expr1, 'Rods Labels' AS Expr2"|

If I run the above command, it just adds the text "Rods Labels" at the end of the table. Is there anyway with the INSERT INTO command that I can insert the new label between the 2nd and 3rd row and add another row? Or is the command designed only to add a new row to the end? I haven't had any luck searching for this yet.

View 1 Replies View Related

Number Of Records Based On Table Value

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

Locking Records Based On A Value In Another Table

Aug 30, 2005

Hi,

I am working on a test equipment database project. Please could you help with a problem I am having.

I want to be able to lock out records on 1 form based on whether 1 field of its data is found in another table

Any ideas and suggestions would be welcome

Thanks :)

View 1 Replies View Related

Queries :: Access 2007 - Insert Into Table Where Source Is Another Table And Form

Feb 3, 2015

If you want to use a "DoCmd.RunSQL "INSERT INTO" command to insert data in a table and the data to insert comes from a table and a form, could this be done in one pass?

So...writing a record wit 4 values from table1 together with a additional value from a textbox in table2 as 5 values.

View 5 Replies View Related

Searching For Records Based Off Of One Field From A Table

Mar 19, 2014

I have a database that I am creating for my work. I have a form that I am trying to get it to search the information from a table to pull the record on the form. I would like to search infomation such as employee id and wanting it to pull that information from the table.

View 7 Replies View Related

Delete Records From One Table Based On Record From Another

Oct 10, 2014

I want to Delete all records from the Table Named "CustomerWiseRotaryTillerRates" (Given Below) if the State in the State Filed in found in the "Customer_Master" Table (attached Herewith). What will be the correct procedure ?

I followed the steps referred in page no. 67 & 68 in the Book "Microsoft Access 2007 Data Analysis" by Mr. Michel Alexender.

Customer Name
State
Product_Code
Product_Name
Price_perunit

TSI Engineering Indutries (P) Ltd.
Assam
P_05
1500_Rotary_Tiller_220_RPM
Rs. 63,722.00

Kishan Krishi Engineering Works
Jharkhand
P_05
1500_Rotary_Tiller_220_RPM
Rs. 62,997.00

TSI Engineering Indutries (P) Ltd.
Assam
P_06
1750_Rotary_Tiller_220_RPM
Rs. 66,663.00

Shri_Tata_Enterprises
Bihar
P_06
1750_Rotary_Tiller_220_RPM
Rs. 65,683.00

View 1 Replies View Related

Queries :: Update In Table From Same Table Based On Criteria Column In Same Table

Sep 29, 2013

How can I update (some columns) in a table from the same table based on a Criteria column in the same table.

View 2 Replies View Related

How Do I Automatically Make A Set Of Records Based A(some) Main Table

Nov 28, 2007

Hello people,

I have for some time now been fooling around with a database to keep track of several clinical trials
Basicallly I have made 4 tables..2 which defines the project ( Project and visist), 1 that populates the projects ( patients) and 1 that bind the patients visits to specific dates ( appointment)

The trouble is that im Lazy :D and therfore dont want to enter the appointment data for all patients one at a time each time we get a new guniea pig since all thats diffferent is the dates he/she has to show up.
Im hoping for some way to fill up my appointment table based on patientID and projectID alone.

SO this is my 4 tables... ( some unimportant fields left out)

Project
ProjectID (PK)
ProjectName
ProjectDescription

Patients
ProjectID(FK)
PatientID(PK)
PatientName
patientScrnr

Visits
ProjectID (FK)
VisitID(FK)
VisitName
VisitPrice
DaysToNextVisit ( integer)

Appointments
VisitID(FK)
PatientID(FK)
AppointmentDate
AppointmentID ( PK)

The idea is to create a project and then add a set amount of visits to the project ( days when they have to come and get a blood sample taken)
All patients involved in the project is likewise added in the Patients table and joined to a project.

Since the patients all have to come to a fixed amount of visits ( determined by the project) I was thinking I could make a form where I pick a patient from a combobox
(select projectID,PatientID,PatientName) and then automatically generate a record for each visit in my Visit table where Visits.ProjectID = cboPickPatient.ProjectID
and show them in a subform so I can enter the date they have to come. I hope that makes sense. ( preferably I would like to add the date also by just entering the first Visit date and the using the DaysToNextVisit to make the other visit dates or each patient, but thats a whole other problem :o )


My instinct tells me I have to do some sort of loop code but I havent figured out the details.

Can any of you guys lead me in the right direction before I loose all my hair in frustation??

This seems to me to be a pretty simple problem-making a recordset based on a projectID and the visits involved in that project but im just out of ideas:confused:

Kind Regards,

Brian Bj

View 5 Replies View Related

Create An Entry In A Table Based On Records Matching

May 23, 2006

Where a many to many relationships is resolved with a linking table...Is it possible to create an entry in the linking table where the two linked tables have the same value e.g. create a entry in the linking table where client table has same value as a job profile table i.e. both are in insurance. Therefore want to create an entry in the middle table with foreign keys from either link automatically

ta

View 1 Replies View Related

Select 10 Or Fewer Records Based On ID Field Within Table.

Apr 19, 2007

Hello,

I've looked through the thread and was unable to find exactly what I'm looking for ?

Basically I have a table with multiple fields & records and I would like to key on the ID field which has a slew of different ID's..... these ID's can be the same & show up on the table anywhere from 1 to 600 times.

What I'm trying to do is select each ID and pull only 10 records for each or if less than 10 pull in those as well.

So instead of pulling in the 600 records for that particular ID I only want to pull in 10 of those records, and for the ID that only has the 1 record pull in the info for that as well.

Any help would be greatly appreciated.

Thank you

View 6 Replies View Related

Queries :: Deleting Records Based On Selections From Another Table

Apr 1, 2013

I am trying to create a Delete query.

I am trying to delete a specific part from multiple BOMs in my database.

I have a table of the BOMs that I want to look in. I called this table PartTable. I also linked my database table SYSADM.REQUIREMENT which contains all the requirement parts for all of our BOMs.

So I am wanting to delete only part number 123XX from each of the BOMs in my PartTable.

I am able to select the records with:

Code:
SELECT SYSADM_REQUIREMENT.*
FROM SYSADM_REQUIREMENT INNER JOIN PartTable ON SYSADM_REQUIREMENT.WORKORDER_BASE_ID = PartTable.PART_ID
WHERE (((SYSADM_REQUIREMENT.WORKORDER_TYPE)="M") AND ((SYSADM_REQUIREMENT.WORKORDER_BASE_ID)=[PartTable].[PART_ID]) AND ((SYSADM_REQUIREMENT.WORKORDER_LOT_ID)="0") AND ((SYSADM_REQUIREMENT.PART_ID)="123XX"));

Now how do I delete these same records.

I am getting error saying I have to select a table to delete from....

View 11 Replies View Related

Queries :: Delete Records Based On Criteria In Another Table

Jun 3, 2013

I am trying to create a delete query that, for a given person, deletes records in Table B that do not have a corresponding record in Table A.

Here are the relevant tables:

tblStates holds StateID, StateName, and RegionID (RegionID is a FK to tblRegions).
tblPeopleStates is a junction table between tblPeople and tblStates.

It lists states assigned to people. It has 3 fields: PersonStateID, PersonID, StateID.

tblPeopleRegions is a junction table between tblPeople and tblRegions.

It lists regions assigned to people. It has 3 fields: PersonRegionID, PersonID, RegionID.

For a given PersonID, I need to delete records (i.e., states) in tblPeopleStates whose RegionID is *not* in tblPeopleRegions.

For example, pretend that tblStates shows that State IDs 1, 5, and 6 are all in Region ID (i.e., all have a RegionID = 10).

If Joe (PersonID = 200) has StateIDs 1, 5, and 6 in tblPeopleStates, but doesn't have a record for RegionID = 10 in tblPeopleRegions, I need to delete his three records in tblPeopleStates (i.e., the ones where StateID = 1, 5, and 6).

PersonID will be found on [Forms]![frmMain]![subform1].[Form]![subform2].Form]![PersonID]

View 8 Replies View Related

Modules & VBA :: Renaming Files Based On Records In A Table

Oct 16, 2014

Code to rename photos (Access 2010). The new name (full path) is listed on a table (although I would love to accomplish the task from the list query that I later used to generate the table). There is one field in the table (or query) called OldPath and one field called NewPath. The table name is RenPaths. Both paths are located in the same drive. I want to rename the file in a new folder. I created the new folder which is contained in the NewPath.

I was trying to accomplish this using the Name function

Name OldPath as NewPath

Here is the full code:

Private Sub Command0_Click()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim OldPath As String
Dim NewPath As String
OldPath = "Select OldPath From RenPaths"

[Code] ....

Here is an example of the paths in my table:

Old Path is - C:UserskcrespoDocumentsFulcrum to MACPFulcrum_Export_a97dee97-ba92-455e-9d5c-3b35617ad357SAA Inspection Form367e14e0-439b-4a50-99e1-9154bcc9e3f7.jpg

New Path is - C:UserskcrespoDocumentsFulcrum to MACPFulcrum_Export_a97dee97-ba92-455e-9d5c-3b35617ad357SAA Inspection FormimageswwMH51856_A3f7.jpg

Im getting an error: Run time error '53': File not found

I checked the OldPath and everything is correct so if the code is working correctly it should have found the file.

View 3 Replies View Related

Forms :: Return Records From One Table Based On Date In Another

Jul 24, 2013

When I add a record to table A (using a form) the first field I enter is a date. That date will then determine which records I see in my drop down list from table B (via a query).

Some records in table B have EffectiveFrom and EffectiveTo dates recorded - because they are now obsolete, other records have no dates recorded in these fields because they active.

How can I view and then select from the drop down only those records in table B which are effective/valid to the date entered in table A?

View 1 Replies View Related

Getting Several Records For One Customer In Fields Of A Table Based On Date

Oct 26, 2011

I am trying to create a database for farm auditing. The idea is you gather information monthly from the same farms and enter this into the database.So as time pogresses for example, farm 1 will have 6 entries for each field in each table, in 6 months time. But i need to be able to see each entry that was put in each month. I do not want it to just show the most recently modified information, and I want it to show the date f these.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved