Problem With Advanced Append Query

Advanced Query Help
The below Stored Procedure was refined to incorporate a UDF. My problem now is that I need it to be even more dynamic. I ideally need the user to have the option to select which fields are incorporated into the query. I have the front end framework although not sure currently how to make field selection dynamic...

CREATE PROCEDURE dbo.sp_ClientQuery

(
@inputRegion varchar(500),
@inputPub varchar(500),
@inputCountry varchar(500)
)

AS


SELECT dbo.tblClient.Country, dbo.tblClient.cName, dbo.tblClient.clientID, dbo.tblClient.Wholename, dbo.tblClient.TelNumberG, dbo.tblClient.TelNumberD, dbo.tblClient.City, dbo.tblPublication.pName
FROM dbo.tblOrders INNER JOIN
dbo.tblClient ON dbo.tblOrders.clientID = dbo.tblClient.clientID INNER JOIN
dbo.tblPublication ON dbo.tblOrders.id = dbo.tblPublication.id
WHERE (dbo.tblClient.Region IN (select value from fnSplit(@inputRegion,',') )) AND
(dbo.tblPublication.pName IN (select value from fnSplit(@inputPub,',') )) AND
(dbo.tblClient.Country IN (select value from fnSplit(@inputCountry,',') ))
GO


This is becoming pretty complicated so any help appreciated.

Phil

View Replies !

---

Advanced Sql Query
Hi Guys

I am having a bit of a delimma and am wondering if there is someone out there that could suggest how i could write a SQL statement that would alow me to return data in a certain way,

I have data as follows...

FIrst problemi is the Date in the LogDate COlumn is of Text Type not Date..

Second problem is i need to take this data and transform it to look like the table below

RCDIDEmployeeIDLogDateLogTimeTerminalIDInOut
411 07/23/200620:45:02iGuard# IN
421 07/23/200620:46:17iGuard# OUT
431 07/23/200620:48:08iGuard# IN
441 07/23/200620:48:18iGuard# OUT
451 07/23/200620:48:24iGuard# IN
461 07/23/200620:48:30iGuard# OUT
471 07/23/200620:48:36iGuard# IN
481 07/23/200620:48:41iGuard# OUT
501 07/23/200620:49:57iGuard# IN
511 07/23/200620:50:14iGuard# OUT
521 07/23/200620:59:34iGuard# IN
531 07/23/200620:59:40iGuard# OUT

Employee IDDateInOutIn OutTotalTimeIn
123/07/200620:3520:3620:3820:3900:02


Basicaly i need to transpose it..

If anyone has the sql or knows the sql on how to do this i would be very greatful

View Replies !

---

Advanced Ranking Query Options…
I have been developing a website where users enter details about positions that they would like to fill.
Other users enter details of positions that are available.
I currently run a ranking query for users that matches from the options they enter which positions best match their profile in a ranked order.

I'm using an Access Database and ASP.
The query is taking a very long time to run.

This is how I have the query built at the moment.


Code:strSQL = "SELECT qryApplicant.* FROM qryApplicant WHERE (((qryApplicant. ApplicantRID) & ''='" & applicantRef & "'));" call getFromDatabase(strSQL, objTempRS, currentPage) if not objTempRS.EOF then applicantWants = True applicantGender = objTempRS.Fields("ApplicantGender") applicantAge = objTempRS.Fields("ApplicantAge") applicantBSMinDuration= objTempRS.Fields("ApplicantBSMinDuration") applicantBSMaxDuration= objTempRS.Fields("ApplicantBSMaxDuration") applicantBSEarlyDate= objTempRS.Fields("ApplicantBSEarlyDate") applicantBSLateDate= objTempRS.Fields("ApplicantBSLateDate") applicantLanguage= objTempRS.Fields("ApplicantLanguage") applicantPosition= objTempRS.Fields("ApplicantPosition") applicantPositionType= objTempRS.Fields("ApplicantPositionType") applicantNationality= objTempRS.Fields("ApplicantNationality") end if if applicantWants then applicantPoss = 0 lineTemp = "" strSQL = "" strSQL = strSQL & "SELECT " & applicantRef & " AS ApplicantRef" strSQL = strSQL & ", qryEmployer.EmployerRID" ' ****************************** Check Gender ****************************** if applicantGender & "" <> "" then lineTemp = lineTemp & "(IIf(([EmployerCSGender]='any'),50,(IIf(([EmployerCSGender]='" & applicantGender & "'),50,1))))" else lineTemp = lineTemp & "(0)" end if applicantPoss = applicantPoss + 50 ' ****************************** Check CheckAge ****************************** if lineTemp & "" <> "" then lineTemp = lineTemp & "+" if isNumeric(applicantAge) then lineTemp = lineTemp & "IIf(([EmployerCSMinAge]<=" & applicantAge & "),IIf(([EmployerCSMaxAge]>=" & applicantAge & "),20,0),0)" else lineTemp = lineTemp & "(0)" end if applicantPoss = applicantPoss + 20 ' ****************************** Check CheckMinMaxDuration ****************************** if lineTemp & "" <> "" then lineTemp = lineTemp & "+" if isNumeric(applicantBSMinDuration) then if isNumeric(applicantBSMaxDuration) then lineTemp = lineTemp & "IIf(([EmployerCSMinDuration]<=" & applicantBSMaxDuration & "),IIf(([EmployerCSMaxDuration]>=" & applicantBSMinDuration & "),20,0),0)" else lineTemp = lineTemp & "IIf(([EmployerCSMaxDuration]>=" & applicantBSMinDuration & "),20,0" end if else if isNumeric(applicantBSMaxDuration) then lineTemp = lineTemp & "IIf(([EmployerCSMinDuration]<=" & applicantBSMaxDuration & "),20,0" else lineTemp = lineTemp & "(20)" end if end if applicantPoss = applicantPoss + 20 ' ****************************** Check CheckEarlyLateDuration ****************************** if lineTemp & "" <> "" then lineTemp = lineTemp & "+" if (applicantBSEarlyDate & "" <> "") then if (applicantBSLateDate & "" <> "") then lineTemp = lineTemp & "(IIf([EmployerCSLateDate]&''<>'',(IIf(([EmployerCSLateDate])>=" & DATE_DELIMITER & AusDate(applicantBSEarlyDate) & DATE_DELIMITER & ",(IIf([EmployerCSEarlyDate]&''<>'',(IIf(([EmployerCSEarlyDate])<=" & DATE_DELIMITER & AusDate(applicantBSLateDate) & DATE_DELIMITER & ",20,0)),0)),0)),0))" else lineTemp = lineTemp & "(IIf([EmployerCSEarlyDate]&''<>'',(IIf(([EmployerCSEarlyDate])<=" & DATE_DELIMITER & AusDate(applicantBSLateDate) & DATE_DELIMITER & ",20,0)),0))" end if else if (applicantBSLateDate & "" <> "") then lineTemp = lineTemp & "(IIf([EmployerCSLateDate]&''<>'',(IIf(([EmployerCSLateDate])>=" & DATE_DELIMITER & AusDate(applicantBSEarlyDate) & DATE_DELIMITER & ",20,0)),0))" else lineTemp = lineTemp & "(20)" end if end if applicantPoss = applicantPoss + 20 ' ****************************** Check Language ****************************** if lineTemp & "" <> "" then lineTemp = lineTemp & "+" if applicantLanguage & "" <> "" then getMultiValues applicantLanguage, ":", arrSplit01, arrSplit01Max itemTemp = "0" for intCounter01=0 to arrSplit01Max if arrSplit01(intCounter01) <> "" then itemTemp = "(IIf(([EmployerLanguage] Like '%" & Left(arrSplit01(intCounter01),3) & "_%'),20," & itemTemp & "))" end if next lineTemp = lineTemp & itemTemp 'Response.Write itemTemp & "<br />" & " " else lineTemp = lineTemp & "(0)" end if applicantPoss = applicantPoss + 20 ' ****************************** Check Nationality ****************************** if lineTemp & "" <> "" then lineTemp = lineTemp & "+" if applicantNationality & "" <> "" then getMultiValues applicantNationality, ":", arrSplit01, arrSplit01Max itemTemp = "0" for intCounter01=0 to arrSplit01Max if arrSplit01(intCounter01) <> "" then itemTemp = "(IIf(([EmployerNationality] Like '%" & arrSplit01(intCounter01) & ":%'),20," & itemTemp & "))" end if next lineTemp = lineTemp & itemTemp 'Response.Write itemTemp & "<br />" & " " else lineTemp = lineTemp & "(0)" end if applicantPoss = applicantPoss + 20 ' ****************************** Check Position ****************************** if lineTemp & "" <> "" then lineTemp = lineTemp & "+" if applicantPosition & "" <> "" then itemTemp = "" getMultiValues applicantPosition, ":", arrSplit01, arrSplit01Max itemTemp = "0" if instr(applicantPosition,"ANY") then itemTemp = "(10)" for intCounter01=0 to arrSplit01Max if arrSplit01(intCounter01) <> "" then if arrSplit01(intCounter01) <> "ANY" then itemTemp = "(IIf(([EmployerPosition] Like '%" & Left(arrSplit01(intCounter01),3) & "_%'),20," & itemTemp & "))" end if end if next lineTemp = lineTemp & itemTemp 'Response.Write itemTemp & "<br />" & " " else lineTemp = lineTemp & "(0)" end if applicantPoss = applicantPoss + 20 ' ****************************** Check PositionType ****************************** if lineTemp & "" <> "" then lineTemp = lineTemp & "+" if applicantPositionType & "" <> "" then getMultiValues applicantPositionType, ":", arrSplit01, arrSplit01Max itemTemp = "0" strType1 = "" for intCounter01=0 to arrSplit01Max if arrSplit01(intCounter01) = "ANY" then strType1 = "(10)" next if strType1 <> "" then itemTemp = strType1 for intCounter01=0 to arrSplit01Max if arrSplit01(intCounter01) = "rea" then strType1 = "(IIf(([EmployerPositionType] Like '%r??:%'),15," & itemTemp & "))" end if next if strType1 <> "" then itemTemp = strType1 for intCounter01=0 to arrSplit01Max if arrSplit01(intCounter01) = "coa" then strType1 = "(IIf(([EmployerPositionType] Like '%c??:%'),15," & itemTemp & "))" end if next if strType1 <> "" then itemTemp = strType1 for intCounter01=0 to arrSplit01Max if arrSplit01(intCounter01) <> "" then itemTemp = "(IIf(([EmployerPositionType] Like '%" & arrSplit01(intCounter01) & ":%'),20," & itemTemp & "))" end if next lineTemp = lineTemp & itemTemp 'Response.Write itemTemp & "<br />" & " " else lineTemp = lineTemp & "(0)" end if applicantPoss = applicantPoss + 20 strSQL = strSQL & ", (" & lineTemp & ") AS Total, qryEmployer.EmployerActive, '" & applicantPoss & "' AS Poss, qryEmployer.* FROM qryEmployer " strSQL = strSQL & "WHERE (((qryEmployer.EmployerActive)=True) AND ((qryEmployer.EmployerLastLoginDate)>" & DATE_DELIMITER & dateAdd("d",(optionDaysListed * -1),now()) & DATE_DELIMITER & ")) " strSQL = strSQL & "ORDER BY (" & lineTemp & ") DESC, qryEmployer.EmployerLastLoginDate DESC;"


Does anyone know any other ways to build this type of Query to show result in a ranked order.

View Replies !

---

MS Access Can't Append All The Records In The Append Query
hi Guys,
I have been looking at different post and checking Microsoft help files as well, but still can't seem to fix this problem.

I am having 2 tables. The first table is connected to a form for viewing and entering data, and in the second table i am just copying 3-4 fields from the first table.

I am trying to use the insert statement to insert records in the second table, and everytime i click on the "Add" button to add the records i get the following error "MS access can't append all the records in the append query ... blah blah blah"

However if i close the form and reopen it, and goto the record (as it is saved in the first database) and now click on the add button to add the fields to the second table/database, it works.

What am i doing wrong???

Any inputs will be greatly appreciated.

View Replies !

---

Append Query .v. Table Query .v. Headache!
Ok, this is what I want to do :

I want to 'append' individual records from 2 tables and place in an archive table or within another database, whichever is the best option.
I then want to be able to 'delete' the relevant records from one table.

The tables are tproperty and trents. This property paid rents but has since been sold. Therefore it no longer belongs in the database, however client wishes to keep details of the property/person/and rents paid in past, for any future ref. These tables are linked in relationships to tlessee and tbilling.
I've read books/notes/looked on here for inspiration and the right direction! If I choose 'append' which seems pretty straight forward then a 'delete' query, how do I choose only one record? Is a make-table onto a different database a better option? It would appear that the whole table is copied over? Can't understand the issue about auto-numbers being copied over? Do I have to use an append/delete query for each individual property that's ever removed?
I'm at a loss! :) Thanks

View Replies !

---

Help With Append Query
I have a database that contain foreclosure records. I'd like to create a query that will ask for a date and all records that are LESS than the date will be moved to a different table.

I'd also created an icon on my form and I'd like to attach this query to it.

Any help will be appreciated.

Bruce

View Replies !

---

Append Query
Please how can i use a procedure to create a query, then append the content of the query to a table (am using MS Access Project)? i did it in Microsoft acess database but now i need it on Microsoft access Project beacuase am transfering to SQL server.
Thanks

View Replies !

---

Append Query + Sum ?
hello,

i have 3 tables:

ACAD_U:
ID
PROIZ_A
TIP_A
DN_A
KOS_A

IN_U:
ID
PROIZ_I
TIP_I
DN_I
KOS_I

ZALGA:
ID
PROIZ_Z
TIP_Z
DN_Z
KOS_A
KOS_I
KOS_Z


Now i want to create an append query that will add in table ZALGA fealds PROIZ_I, TIP_I, DN_I, KOS_A, KOS_I by critera if TIP_A = TIP_I AND DN_A = DN_I then calculate KOS_Z = KOS_I - KOS_A?

Can someone pls tell me how to do that?

THX

View Replies !

---

Append Query
Hi,

I am building a database to hold information of training courses and staff that have requested or completed the training course.

I have built a form which the team leaders can use to request training for their team, basically when the form opens up it asks for team name and training session and then appends the names and training session ID to the main table. This then allows the team leader to tick the "request training" tick box which updates the table for each member they request trainig for.
They would then send this using a custom command button which is linked through outlook.

The problem I am encountering is that if they were to selct the same team and training session again it would then append the same data to the table and this would create duplicate entries.

How can I set it up so it appends only once and then any other time the same data is selected by team leaders it would populate the form with the existing date rather than appending the same data.

thanks in advance.

View Replies !

---

Append Query
Dear all,
I have a trouble to run the append query. It is failed due to the key violations. Could you please help me how to solve this problem?
Many thanks.
Bich

View Replies !

---

Help With Append Query
Hi,

Any assistance someone can give me with this append querry issue is appreciated.

I have a table called tblRoleAssignments that has three fields RoleAssignmentID (PK), RoleprofileID & ApplicationrightID. It looks something like this.

RoleAssignmentID,RoleprofileID,ApplicationrightID
58, 12,317
59, 12,796
60, 12,1
61, 13,179
62, 13,84

my append query will write new records depending on the RoleprofileID's I entered, for example If I run my append query on the above list I get the following output

RoleAssignmentID,RoleprofileID,ApplicationrightID
63, 0, 317
64, 0,796
65, 0, 1
66, 0, 179
67, 0, 84

My problem is when I run my append query I want it to enter a specific value in the role profile ID column (taken ideally from a field on a form that will be loaded) so instead of the query entering a zero it writes to the table a vaule I want. Below is how I want my append querry to write the data if I wanted the RoleprofileID to be set to 35.

RoleAssignmentIDRoleprofileIDApplicationrightID
63, 35, 317
64, 35, 796
65, 35,1
66, 35, 179
67, 35, 84

Any ideas????????

View Replies !

---

Append Query
I think i need to use an append query for this but have never used on before so just need to know if it is the right thing to do.

I have two tables [tblPoles] and [tblPoleInstructions].

I have a form set up for [tblPoles] so the user can enter the data required. One piece of data required is a start date. The only fields the tables have in common are the PK which is the Pole Number and the start date.

I want the user to be able to enter the start date on the form for tblPoles and it to be automatcially entered into tblPoleInstructions. Is an append query the right way to do this?

Any help would be greatly appreciated.

View Replies !

---

Append Query Help!!
I have made some amendments to my brothers Database. Now I have a problem. How the hell do I append the data from his old one into his new one? It all seems very complicated. Can anybody advise? Basically the tables of primary interest are:

Append From tblCustomers1 to tblCustomers
Append From tblCustomerContacts1 to tblCustomerContacts
Append From tblOrders1 to tblOrders
Append From tblOrderDetails1 to tblOrderDetails
Append From tblPayments1 to tblPayments

How is this possible as Orders relate to Customers, Order Details relate to Orders, Payments to Orders, Contacts to Customers.....my brain is scrambled!!

Help on understanding Append queries appreciated.
Regards,
Phil.

PS: Have attached a small sample DB.

View Replies !

---

Append Query Help
I have a simple one here I think, but I am a newbie to SQL and Append Queries.

I have an Order Tracking Database with three tables:
Orders (contains PK OrderID)
OrderDetails (Contains PK OrderDetailsID and FK OrderID)
Updates (Contains PK UpdateID and FK OrderDetailID)

The Orders table contains customer info and an order number (OrderID). This is manually entered, not an Autonumber. The OrderDetails table contains line items for products sold with that order. The OrderDetailID is an autonumber, and each record contains the OrderID field as a FK in a one-to-many relationship. Basic stuff. The Updates table is used for tracking the status of each product associated with an order as it flows through the back-end sales process, from production to shipping. The UpdateID field is the PK (Autonumber) and each record contains the OrderDetailID as a FK in a one-to-many relationship.

I do a monthly update of Orders and OrderDetails into those respective tables in Access on a monthly basis. I am just using cut and paste from a .csv file at this time. I am doing this because our company uses a Siebel program for tracking front end sales, but it does not do any back-end tracking at this time. So, I export the monthly sales as a .csv and import into Access. It seems to work fine for now, but it has been suggested in other discussion groups to use a temp table of the raw .csv data, import into Access, and create an Append query to update the Orders and OrderDetails. I will look into that.

The problem I have today is how to get Access to automatically create a new record in the Updates table for each OrderDetailID. So, when I paste (or later append) OrderDetails into the OrderDetails table, Access creates at the same time a new corresponding record in the Updates table. Each OrderDetailID can have many UpdateID's (one-to-many relationship).

I think I need a SQL statement that says "Update the Update table UpdateID with a new autonumber if the FK field OrderDetailsID within the Update table is null" or something like that.

Please help.

View Replies !

---

Append Query HELP
I have a Form with a combo box that takes in formation from a table and auto fills it. But i cant get that autofilled info to enter into the main table.the one that the form is based off of. I tried to use an append query to move the info from one table to the other. It didnt work. does anyone know why this error pops up i cant see anything wrong...

Concrete Pumper Database Set 0 fieldsto Nulldue to a type conversionfailure, andit didnt add 3 records due to key violations, 0 records due to lock violations and 0 due to validation rule violations...


in detail what im trying to do is this. database is for remote control concrete pumpers.

I have a form based on a table. In this table i have fields such as model #, button 1 configuration-button 12 configuration date entered, date shipped etc.I created a form from the table and Instead of entering each of the 12 button configurations I would like to have that entered automatically when a model # is selected from a combo box. So i made a table with just model # and button configurations and set up a combo box on the form to match. now i need to get the button config from the button config table to be entered into the MAIN TABLE when entered into the form by combo box. oh man...i dont know if im too far gone or what...this may not make sence so please give it your best...I appreciate any input at all. I thought i could do it with an append query and a macro but now i dont think so.


thank you

View Replies !

---

Append Query
I have a database with a table linked to an Excel spreasheet. I am trying to create an append query to load data from the linked table into a candidate table. Each record in the candidate table has a candidate number which is automatically generated and is the primary key. When I run the query it cannot update the table because of key violations. I haven't got the candidate number as a field in the append query, do I need to put it in there? and if so how should it be setup? Any help would be much appreciated. :)

View Replies !

---

Append Query!!!! Help
HI there,

sorry a bit of a newbie to access. I am trying to do an append query, or should I say I have done an append query.

I am trying to build a database. The first query pulls data from a server with four tables linked into this, when you fnally get the ODBC to connect the data comes through. I have a delete query to clear down my Import table and then an append query to put all the dat from my query into this. This is where it goes all wrong and its so basic.

INSERT INTO tbl_ImportCenceo ( [email id], process, type, changedate )
SELECT qry_email1.[email id], qry_email1.process, qry_email1.type, qry_email1.changedate
FROM qry_email1;

It has worked once and now it wont??????. The data runs and is in the append query but it is not transferring it to the import table. Can somebody tell me why Pleaseeeee!!! this is doing my head in!!!!.

Thanks:eek:

View Replies !

---

Append Query
Hello Gurus
I have D/ase (2000)
and one of the queries appended a set of wordings from another table (I'm in insurnace so these are endoresments)
my qry works on the append basis I pick an endorsment from a drop down list and push a button and in gos the wording in to a new field (memo format)

all is fine - now the problem if the wording is over 255 chars long I only get 255 chars - now i know this is something to do with text - the fields where the info is coming from is a memo and the field it is going into is a memo - its the append query that decided to format that field to a text size - any ideas on how to get the querie to append memo instead of text size

many many thanks if someone can shed some light on this

View Replies !

---

Append Query
I have two tables: a project table, and attendance table. I am appending name, id, date, status, etc from the project table to the attendance table. However, I only want the field "status" with XX to be appended once to the attendance table. For example the field "status" with XX would be appended once for that individual's record and date to the attendance table. The field "status" with YY would be appended continuosly for each date to the attendance table. So, I need to build a check before the query appends to the attendance table to check for specific data in the "status" field of the daily attendance table such as XX. If XX is already listed in the status field of the daily attendance table, then that individual's record would not be appended from the project table anymore.

View Replies !

---

Append Query
Hi all..

I have two tables. One I imported from another database. Table: Master and Table: Input. They are setup up exactly the same.

They each have about 16K entries. They each have a PK of IDNumber plus the other 10 fields.

What I want to do is run an append query to add the input data into the master. I want it based on EmployeeNumber because there are PK numbers in each table...

IE
Master:
IDNumber 1
EmployeeNumber 34567

Input
IDNumber 1
EmployeeNumber 456789

So i want the append query to look at the employee number. If they are the same no change.. If the employee number doesn't exist I want the entry brought into table:master.

Does this make sense? Am I going about this the right way?

Thanks
R~

View Replies !

---

Help With Append Query
Hello,

Could somebody please tell me how to write my Where statement without specifically mentioning the exact name of the parent form?

This is what I have right now:
WHERE (((tblOptions.OptionsID)=Forms!frmBrowseApplicatio ns!sfrmOptions!OptionsID));

It works, but I'd like to get rid of "frmBrowseApplications". And "sfrmOptions" as well if possible. I've been experimenting with "parent" and "me", but either that can't be done or I just haven't hit the jackpot yet.

Thank you.

View Replies !

---

Append Query
Hi

When running an append query, I get the error message, "Could not find output table TblArchiveOrders."

I'm only trying to append one table. When the box appears to type in the name of the table, I typed in TblArchiveOrders.

It should work, I've followed the instructions in a book I have exactly. Anyone got any ideas what is going on here?

View Replies !

---

Append Query Help
Hi, Im using an append query to update one table depending on what gets entered into another one(contable -> companytable). Im running this as part of the click to add details from a form to contable, it takes whatever is entered into the company column on contable and adds it to the company column on the other table. Ive set the second table not to allow duplicates because thats how i need it but this means that whenever it runs the append query it comes up with an error saying that it couldnt copy X amount of entries(as they would be duplicates). It does only copy the new one but how can i set it to only try and copy the new entry instead of all of them so i no longer get this error message. is it possible to intergrate an unmatched query into the append query?hope thats not too confusing reading! any ideas? Thanks James

View Replies !

---

Append Query Help
Hi guys,
I'm modifying a database slightly, trying to edit without really understanding the entire functionality of the system. Basically I have an append query where a set of default data is added to a table containing modified values. So if you had created A and C entries in the table already, this query will append B, D, E, etc...
However I want one of the fields that is by default set to B (and is B from the default table) (to indicate write/read) to be changed to R (read only) in all the entries that are added from this query.

Basically its so the log file records all of the values, but only uploads the modified values.

Thankyou

View Replies !

---

Append Query Help!
I need help with writing some kind of Append query. I do not know much about SQL and I can not seem to find any examples on what I'm trying to do.
This will probably be simple for most of you.

I have 2 tables. The first table is called tblTempList which is where I am importing a list of VINs into from an Excel sheet. The colum name is F1. The second table called tblVINList is a many relationship table. It has 2 colums. The first is called WorkOrderID and the second is VINList.

I have a subform called sfrmVIN with a subform on it called ssfrmVINList.
The form ssfrmVINList shows the list from the table tblVINList using the WorOrderID as its reference.

I need a method of getting the list from table tblTempList, colum F1, to table tblVINList colum VINList along with the subforms WorkOrderID number in the WorkOrderID colum of the table.

Example of tblVINList after transfer:

WorkOrderID | VINList
344 | C06P025655
344 | C06P025865
344 | C06P026157
344 | C06P026420
344 | C06P035832

This needs to be done from a Buttons click event procedure. Any help on this would be very appreciated.

View Replies !

---

Append Query Or Something....
Situation: I have a table (po_numbers) with 2 columns: po_number, po_used(boolean).

On a form I have a combo box which is populated by a short query

SELECT po_number
FROM po_table
WHERE po_used = False

After I select the PO number I'm trying to run an after_update event to change the selected po_number's po_used to 'true'.

This is what I've tried so far:

Private Sub po_number_AfterUpdate()

Dim strSQL As String

strSQL = "UPDATE po_numbers SET po_numbers.po_used =True" _
& " WHERE(((po_numbers.po_number)=" & [Forms]![po_req]![po_number] & "));"

CurrentDb.Execute strSQL, dbFailOnError

End Sub

It's not working and I'm not that great with access. Someone suggested do and Append query to do the same thing, but I don't really know how to structure it.

Any suggestions?

View Replies !

---

Help With Append Query
I have ‘S_NUM’ field in my Table1. This field assigns sequential numbers to records. Sometimes it needs to be changed to start from a different number. I created a new table (tblAppend) with one field (S_NUM). Then I made an Append Query. I inserted a new number in ‘tblAppend’ and run the ‘qryAppend’ from Query Objects Window, it appends the number I want to Table1(S_NUM).

In order to be helpful to users who don’t know much about Access, I made a form for ‘tblAppend’ with a command button to run the Append query, so that the user only has to type the new starting number and click the button.

This is not working. After typing the new start number in the form and clicking Run Append button is not inserting the new number in Table1. If I close the form, open again and click the button, it inserts the number which means the user has to open the form and click the button twice to get a new starting number.

Have I made a mistake somewhere? Please help. Sample DB attached.

View Replies !

---

Append Query
I have been working on this Append Query for hours and just can not figure out how to make it work.

I want the Append Query to write information from my main table named tblCatalog to a temp table named tblTempCatalog, but filtered using a text box named txtSearchBox from the main form named frmCatalog.

------------------
TABLE : tblCatalog
TABLE : tblTempCatalog

FORM : frmCatalog
TEXT BOX : txtSearchBox

QUERY : qryAppendSearch
------------------

Here is the SQL statement from the Query.

INSERT INTO tblTempChild ( AutoNumberKey, GroupCode, FaultCode, FaultDescriptionE, FaultDescriptionS, FaultDescriptionG )
SELECT tblNumChild.AutoNumberKey, tblNumChild.GroupCode, tblNumChild.FaultCode, tblNumChild.FaultDescriptionE, tblNumChild.FaultDescriptionS, tblNumChild.FaultDescriptionG
FROM tblNumChild
WHERE (((tblNumChild.FaultDescriptionE)=[Forms]![frmCatalog]![txtSearchBox]));

It appears not to see the txtSearchBox on the form. Keeps giving me 0 records. What I would like is to use a LIKE operator with the "*" wildcard so I could just type part of a word in the txtSearchBox and have all the records that contain that string end up in the tblTempCatalog. Any help on this would be very helpful....Thank you!!!

View Replies !

---

Append Query
Hi,
Is there any way to append form that contains subform to another form with subform.
See attachment.
I made sample database:

table Inv with fields
InvoiceId - autonumber
InvoiceDate - date

table Quo with fields
InvoiceId - autonumber
InvoiceDate - date

table Pro
ProductId - autonumber
ProductName - text

table InvPro
InvoiceId - number
ProductId - number

table QuoPro
InvoiceId - number
ProductId - number

Tables Inv and InvPro and Pro are in 1 to many relation ship
Tables Quo and QuoPro and Pro are in 1 to many relation ship

I created following Query InvProd
using tables InvPro and Pro
that contains InvoiceId ProductID and ProductName fields

Query QuoProd
using tables QuoPro and Pro
that contains InvoiceId ProductID and ProductName fields

I made forms:
Inv : InvoiceId, InvoiceDate
with subforms (query InvProd)

Quo : InvoiceId, InvoiceDate
with subforms (query QuoProd)
I populated booth forms and have in form Inv - invoice nr 1 and 3 products, form Quo invoice nr 1 and nr 2 booth with 5 and 6 product items.
What I want is to import from the form Quo invoice nr 2 to form Inv so that I receive in form Inv invoice nr 2 with 6 product items.
Meaning of is :
As i do quotation for some company, some of them get approved and some not, approved one will be realized so they have to go to Inv form, the other that they are not approved stay in quotation and they don't enter my bookkeeping side of program - they are not realized sales.

This is my Sql:
INSERT INTO InvPro ( InvoiceId, ProductId, ProductName )
SELECT [QuoProd].[InvoiceId], [QuoProd].[ProductId], [QuoProd].[ProductName]
FROM QuoProd
WHERE [QuoProd].[InvoiceId]=2;

I will make select query with criteria [EnterInvoiceId] that will ask you which invoice from Quotation will be append.
Tks,
GagaZ

View Replies !

---

Append Query Help
I have a form that has a DELETE button on it. When it is pressed, the record is deleted which works fine.

Now I would like to APPEND the record to a table "Archived Records" before it is deleted. In addition, I would like to include when the record was deleted with a time stamp. The table name I am deleting from is called "Report".

Have searched the forum and can't find what I am looking for. Anyone have an idea how to do this?

View Replies !

---

Append Query
I am trying to write an Append query that has a math formula. Part of the formula is to look at a text field called LastFullMonth (contains values such as January, February, etc) and do a calculation based on how many months have elapsed since the fiscal year. (the fiscal year begins in October).

To come up with the number of months elapsed since October, I tried creating:

View Replies !

---

Help With An Append Query
I am trying to setup a Append query in which you use an open form button and it appends data to the form that I am opening

The data on the main form is order info
OrderPK
ProductFK
UnitPrice
Quantity

On the Invoice form I want the data
ProductFK
UnitPrice

to append to the Invoice details tbl so I can see what the customer ordered and gives me the ability to invoice what we have on hand and then invoice later when we get more in. My other problem is linking the data to the order that I am working on instead of appending everything from the orders table to the invoice table

View Replies !

---

Append Query
I'm using a simple append query to append records to the end of an existing table. When I run the query, the records are automatically sorted within the table.

My intention is to append records so they remain at the end of the table without being automatically sorted. How can I do this.

View Replies !

---