SQL Statement - Listing The Records Where Those Records Are Not In The Bridging Table
Hi what would be the SQL command of when:
-You want to list all the records and columns of table A
- But, only if the employeeID is not in the table B
- If the matching employee ID is in the table B, that record or value corresponding to that ID won't be included in the result
table A
-------
field1 - employeeID
field 2 - firstname
field 3 - lastname
table B (bridging table or many-to-many)
------
field1 - employeeID (PK)(FK)
field x - xxx
field x - xxxx
and so on
Thank you
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Adding Records To A Table And Editing Records
What is the best way to bind text boxes to recordsets? Is it to put a recordsource (data1) on a form and then bind each text box to a column in data1 or is there another way. I know when using just Access alone I don't keep tables open I update records using append queries and update queries. Is this what I should be doing in VB as well? It seems hard to manipulate data on a form when there are 2 recordsources (data1 and data2) to get everything to the correct record I have to use data1.recordset.seek on an indexed column in the table.. is this a round about way to match records or is this the only way?
Adding Records So They Are Related To Records In Another Table.
I am trying to build a database with many one to many relationships. I got the view part down, and have a few records to in there to test, basically i choose a item from the first combo box, then the second combo box populates with items related to the first combo box and so on. When I add a new artist(first combo box) I want the second combo box to light up and allow me to add a new record related to the first. I'm not sure how I can pull this off, any help would be appreciated!
Access97...append Records From Table Of Varying Fields Into Single-field Table
Hi all,
New to this forum, and pretty much a complete noob with VB, but I've done a few things with it in Access so I'm not tatally lost. Here's my situation: my users take in a rather large and ugly Word document from a client (essentially a print-out from a mainframe application) which contains a few key bits of information that need ultimately need to be used to place an online order at a vendor website. Since this online order can involve copying/pasting a single column of data instead of manually keying each bit in, these bits of information need to be extracted from the initial document and normalized into a single column of data for the user. On the source document, these key bits of information are on individual lines separated by commas. I've gotten far enough using import/export specs and queries to produce a table that has each bit of data in it's own field; hoever, the number of fields in this table will vary with each new client document, and not all fields in each row will have values. My need is to code a routine that will cycle through each field in this table, regardless of how many fields there are, and append the non-null values from each into a different single-field table (or single field data source) which can then be spit out to the user. What's the best way to go about this?
Regards and thanks,
Stuart
Update Records In Table 1 With Existing Record In Other Table
hi,
can somebody help me on how I can resolve my problem..here's my code first thing of all...
VB Code:
Dim rsdelSet rsdel = New ADODB.RecordsetDim rsupdSet rsupd = New ADODB.Recordset rsdel.Open "Select * from tblTrackOrder Where RawID Like '" & lblRawID & "'", goConn, adOpenDynamic, adLockOptimistic, adCmdText If rsdel.RecordCount <> 0 Then While Not rsdel.EOF rsdel.Delete rsdel.MoveNext Wend End If rsdel.Close rsupd.Open "Select * from tblContacts Where ContactID Like '" & SupplierID.Caption & "'", goConn, adOpenDynamic, adLockOptimistic, adCmdText If rsupd.RecordCount <> 0 Then rsupd.Fields("CRequest") = "N" rsupd.Update rsupd.Close Else rsupd.Close End If
in my table tblTrackOrder, I deleted a record that has been processed...
let say the remaining records in my tblTrackOrder are:
ID NAME
1 Robee
3 Wendy
now, I have to equate these records to my other table called tblContacts...and in my table tblContacts my records are:
ID NAME CRequest
1 Robee Y
2 Dundee Y
3 Wendy Y
4 Plagie Y
in tblTrackOrder I only have 2 records..now I want these 2 records to be the look up so I can edit the records at tblContacts...
i want the output to be like this:
ID NAME CRequest
1 Robee Y
2 Dundee N
3 Wendy Y
4 Plagie N
when a record exist in the tblTrackOrder, it's corresponding record in the tblContact should have a Y value in CRequest field...but when a record is not existing in the tblTrackOrder it's CRequest field value should be N...I hope I've shown enough on how I want my system to work..im having difficult time thinking..i hope somebody could help me out..many thanks
How To Delete Records In One Table Based On List In Second Table
Hi,
I have 2 tables
tbl_Docs with Fields:
- ID1 (Autonumber)
- DocRef (Integer)
- Others (Text)
tbl_DeleteList with fields
- ID2 (Autonumber)
- DocToDelete (Integer)
I want to write a query to delete those records in tbl_Docs where the DocRef matches the list in tbl_DeleteList (field DocToDelete)
I know how to do the simple delete for a single record
CODE
DELETE tbl_docs.* FROM tbl_docs WHERE (((tbl_docs.DocRef)=2))
Use VB To Append Records To An Linked Table From An Access Table
HELP! Sometimes this statement inserts records and sometimes it doesn't. Any ideas?
Select_Statement = "INSERT INTO OECSHFIL_SQL ( ... ) SELECT ... FROM PrepayRecs"
cnnAccess.Execute Select_Statement, TotalRecsAfter, adCmdText
Using ADO data control with Access2000 database. OECSHFIL is a linked Btrieve table and PrePayRecs is an Access 2000 table.
When Adding A Records, Do You Need An SQL Statement?
Ok, Im just now getting to understand ADO, and databases and stuff of that nature, although there are still some things Im unclear about. When adding records to a database, do you need an SQL statement? For example, the code below adds a record (as far as I know) Does it need the SQL statement.....
Dim strdate As String
Dim strtotal As String
Dim strassocID As String
Dim stsql As String
Dim rcst As ADODB.Recordset
strdate = lbldate.Caption
strtotal = txtTotalDue.Text
strassocID = lblAssociates.Caption
Set rcst = New ADODB.Recordset
stsql = "SELECT * FROM InvoiceMaster"
With rcst
.Source = stsql
.ActiveConnection = conn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open "SELECT * FROM Invoicemaster"
.AddNew
!InvoiceDate = CDate(lbldate.Caption)
!InvoiceTotal = strtotal
!AssocID = strassocID
.Update
.Close
End With
Or could you just leave it out and do something like this....
Dim strdate As String
Dim strtotal As String
Dim strassocID As String
Dim rcst As ADODB.Recordset
strdate = lbldate.Caption
strtotal = txtTotalDue.Text
strassocID = lblAssociates.Caption
Set rcst = New ADODB.Recordset
With rcst
.ActiveConnection = conn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
.AddNew
!InvoiceDate = CDate(lbldate.Caption)
!InvoiceTotal = strtotal
!AssocID = strassocID
.Update
.Close
End With
With the 2nd piece of code...how does it know what table to add to? If anyone could shed some light on this, it would be appreciated.
SQL Statement To Move Records
Hi,
I need to move certain records to a different table.
I am thinking to use select statement to save the list of records into
VB array and then loop through array to update it to the other table.
Is there one SQL statement that can move certain records into another
table?
Thanks for your help!
SQL Statement For Dated Records
I have a table in Access Database and a VB6 Front-end and I need to write an SQL statement to get the data I want from a table.
The table stores certain information for several countries each day. Each country can be logged more than once per day and a timestamp of when it is logged is recorded in the field LogTime so that the most recent record can be found. I want to retrieve the most recent record each day for a specified country between 2 dates
The SQL statement I am currently using is:
"SELECT * FROM t_Log WHERE [Country] = 'Country' AND [LogDate] >= #Date1# AND [LogDate] <= #Date2#"
But this gives me all the records for the specified country between the 2 dates and does not take into account the timestamp, how can I only get the most recent records for each day between 2 dates?
Thanks for any help.
When I Select In Combo Related Records Display In Grid, Few Records Are Not Displaying
I think this is the right place to post this, without knowing i place this in other discussion.
I am new.
Actually my project's Purpose [in the Form1, two combo boxes listing ItemCode and Description from two tables, where ItemCode is equal in both tables, after selecting in combo, the related record displayed in the Form2-MSHflexgrid]
Everything is working perfectly except one thing. Only three records is not displaying the details among the 1600 records
OFFICE 2000 W32 EN INT'L
MONEY 2000 W32 ENG INT'L
COMPAQ PRESARIO MV500 15" MONITO . this 3 records are in the present in the table and the ItemCode is also equal. The 3 belongs to Description field. I check through in another way, by giving the Itemcode in the first combobox, it displays correctly the details of all records. where as in the second combobox only these three is strucking up.
where as PRESARIO MV500 15" MONITOR is also a record similar to the three records but it is displaying.
I don't know this is because of any string error or something else. What's the problem, Kindly reply me.
Form_Activate()
If Form1.optItemcode.Value = True Then
sqls= "selecttab1.Itemcode,tab1.Description,tab1.Product,tab2.warehouse,tab2.Qty,tab2.Amt,tab2.Amt * 5/100 from tab1,tab2 where tab1.Itemcode=tab2.Itemcode AND tab1.Itemcode='" & Form1.cmbItemCode.Text & "'"
End If
If Form1.optDesc.Value = True Then
sqls="select tab1.Itemcode,tab1.Description,tab1.Product,tab2.warehouse,tab2.Qty,
tab2.Amt,tab2.Amt * 5/100 from tab1,tab2 where tab1.Itemcode=tab2.Itemcode AND tab2.Description='" & Form1.cmbDescription.Text & "'"
End If
rst.Open sqls, cnn, adOpenStatic, adLockReadOnly, adCmdText
If rst.RecordCount = 0 Then
Me.Hide
Form1.Show
MsgBox "No Matching RECORDS !", vbExclamation, " * Invalid Entry"
End If
because of this I am not able to finish my project. Kindly help me. My mail id is
itchocks@rediffmail.com
Thank you very much,
CHOCK.
Hai !!!
Question:Also send it to my mail ID. I did'nt get practice with forums, I don't know where to search this again.
<HTML>
<B><font color="#008000">HAVE A NICE DAY !</font></B>
<HTML>
When I Use DISTINCT In Sql Statement It Returns -1 Records
##############################
Set gloV_adoCnString = New ADODB.Connection
gloV_adoCnString.Open "Provider=MSDASQL.1;Persist Security
Info=False;Extended Properties=DRIVER={Microsoft ODBC for
Oracle};UID=mmsys;SERVER=scenrdb;"
If gloV_adoCnString.State = False Then gloV_adoCnString.Open
gloV_SqlStr = "SELECT START_YEAR FROM MR_WSAMPLE WHERE STCODE LIKE
'QF%' AND START_MONTH = 11"
Set gloV_adoRsUserData = New ADODB.Recordset
gloV_adoRsUserData.Open gloV_SqlStr, gloV_adoCnString, adOpenKeyset
MsgBox gloV_adoRsUserData.RecordCount
##############################
I am using oracle... Calling from VB....
My problem is the above code is working and it returns the No. of
records(Eg. around 47 records)
But if I use DISTINCT in sql statement, given below
gloV_SqlStr = "SELECT DISTINCT START_YEAR FROM MR_WSAMPLE WHERE
STCODE LIKE 'QF%' AND START_MONTH = 11"
or simple
gloV_SqlStr = "SELECT DISTINCT START_YEAR FROM MR_WSAMPLE"
both statement returns -1 records. Why?
Anybody help me?
Display All Records Base On SQL Statement
Hey, All.
How would it be possible to display all records with in my access database to a another form...
e.g..
show all records like in a datareport...but without the datareport....
Thanks S.
Making A Retired Emplyee Records Inactive Instead Of Deleting His Records
I am developing a Payroll Software. Instead of deleting the records of a retired employee from the payroll, I want to make his records Inactive. This will allow the user of my program to be able to refer or recall the records of the retired employee if the need arises. I would want the retired employee's records to kept in an Arhive file if possible instead of deleting it completely.
I am totally ignorant of how to write the necessary codes.
I should be very grateful if you can help me out.
Thank you.
Akinyemi
BUILDING SQL STATEMENT TO FIND MATCHED RECORDS
CAN ANYONE TELL ME HOW CAN I BUILD A SQL STATEMENT SO THAT TO FIND SPECIFIC RECORDS ACCORDING TO A FIELD WHICH CONTAINS
DATE WITH FORMAT 12/10/2001
FOR EXAMPLE I WANT TO FIND ONLY RECORDS WITH DATE EQUALS TO 12/05/2001 AND SO ON
THANKS FOR YOUR HELP
RESOLVED: Select Statement - Exclude Certain Records
I am working on a hockey statistics application and working on the part where I create the rosters. I am using both VB and ASP with an Access database.
I have a player table, a roster table and a team table. Each table has a unique identifier.
Each record in the roster table also consists a combination of the unique id from the player table and team table.
Table Examples:
Player Table
ID Name
1 Player 1
2 Player 2
3 Player 3
4 Player 4
Team Table
ID Name
1 Team 1
2 Team 2
Roster Table
ID PLAYERID TEAMID
1 1 1
2 2 1
3 3 2
4 4 2
If I want to select the players for a roster, I select * from Roster where TEAMID = X and join on the Player table for the player details.
My question is whether it is possible to create a select statement that will select the records from the player table, but EXCLUDE any records that have already been added to a roster for that team. Using the sample data above, if I were to select team 1 the players who are on team 2 would be returned by the select statement.
I have attached my database in case anyone would like to take a look or fiddle around with the queries. The team in the database that has existing data is ID 4 in the team database.
Thanks in advance for your help.
Edited by - Luber25 on 5/17/2005 11:39:59 AM
Increment Records And Loop Between Records Based On Condition
dear all,
I want to increment each record in my table 50 times.
for example if i have 3 records: 50,100,200.
I want to out put these record in a text file.
My first row in file will be 50(50+0), second will be 51(50+1), third wiil be 52 and so on till 99.
Now i want to do a check before incrementing the second record in my table.
only if next record, in my case = 100 minus first record 50 is 50 then go to next record and do the same incrementing.
if the minus operation is more than 50 than the file should be ended. and a new text file should be created.
i have done the incrementing part but i cant make the minus part work..
as i cannot get the second record in a variable
my code is below;
datum = Text1.Value
item_no = Combo1.Value
tmp_val = ""
serial = ""
If Not rs.EOF Then
rs.MoveLast
rcount = rs.RecordCount
rs.MoveFirst
Close
Open "c: est.txt" For Output As #1
While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
serial = rs.Fields(rs.Fields(i).Name) + 0
tmp_val = serial & "|" & datum & "|" & item_no
Print #1, tmp_val
tmp_val = serial + 1
tmp_val = tmp_val & "|" & datum & "|" & item_no
Print #1, tmp_val
tmp_val = serial + 2
tmp_val = tmp_val & "|" & datum & "|" & item_no
Print #1, tmp_val
tmp_val = serial + 3
tmp_val = tmp_val & "|" & datum & "|" & item_no
Print #1, tmp_val
Next i
tmp_val = tmp_val & vbCrLf
tmp_val = ""
tmp_val = serial + 50
rs.MoveNext
If tmp_val = serial Then ......
DoEvents
Wend
Print #1, tmp_val
MsgBox "Process Completed"
Close #1
End If
End Sub
can some one help me please?
Textfile Records And Access Database Records Matching
how do i do matching whith using looping on textfile records and database records? if the records in textfile and access database are not match then it will have a output on a.txt and the output is retreive from textfile
the bugs is, there are no output (it suppose to have output)
please help ... thanx
VB Code:
Do While Not RS.EOF strFileNoDB = CatchNull(Trim(RS("FILE_NO").Value), "Null") strBenCdDB = CatchNull(Trim(RS("BEN_CD").Value), "Null") strBenNameDB = CatchNull(UCase(Trim(RS("BEN_NAME").Value)), "Null") strBenICPassDB = CatchNull(Trim(RS("IC_PASSP_B").Value), "Null") strGuardNameDB = CatchNull(UCase(Trim(RS("GUARD_NAME").Value)), "Null") strGuardICPassDB = CatchNull(Trim(RS("IC_PASSP_N").Value), "Null") strBenICPassDBHy = Replace(strBenICPassDB, "-", "") strGuardICPassDBHy = Replace(strGuardICPassDB, "-", "") strFileOutputDB = strFileNoDB & strBenCdDB & strBenNameDB & strBenICPassDBHy & strGuardNameDB & strGuardICPassDBHy If Left(ARBFile, 2) = "01" Then strTxnYear = Mid(ARBFile, 17, 4) strTxnMonth = Mid(ARBFile, 15, 2) strTxnYMJ = strTxnYear & strTxnMonth End If SeqNum = SeqNum + 1 strSeqNum = CStr(SeqNum) For x = 0 To aIndex If Left(ARBFiles(x), 2) = "02" Then If Len(strSeqNum) = 1 Then SeqNumJ = strSeqNum & " " ElseIf Len(strSeqNum) = 2 Then SeqNumJ = strSeqNum & " " ElseIf Len(strSeqNum) = 3 Then SeqNumJ = strSeqNum & " " ElseIf Len(strSeqNum) = 4 Then SeqNumJ = strSeqNum & " " ElseIf Len(strSeqNum) = 5 Then SeqNumJ = strSeqNum & " " ElseIf Len(strSeqNum) = 6 Then SeqNumJ = strSeqNum & " " ElseIf Len(strSeqNum) = 7 Then SeqNumJ = strSeqNum & " " ElseIf Len(strSeqNum) = 8 Then SeqNumJ = strSeqNum & " " ElseIf Len(strSeqNum) = 9 Then SeqNumJ = strSeqNum & " " ElseIf Len(strSeqNum) = 10 Then SeqNumJ = strSeqNum End If strFileNo = Mid(ARBFiles(x), 51, 8) strTxnStatus = "N" strBenCd = Mid(ARBFiles(x), 59, 2) If Len(strBenCd) = 1 Then strBenCdJ = strBenCd & " " ElseIf Len(strBenCd) = 2 Then strBenCdJ = strBenCd & " " End If strBenCdC = Trim(strBenCd) strBenName = UCase(Mid(ARBFiles(x), 61, 60)) strBenNameJ = Trim(strBenName) strBenICPass = Trim(Mid(ARBFiles(x), 15, 12)) strBenICPassJ = strBenICPass & " " strGuardName = UCase(Mid(ARBFiles(x), 165, 80)) strGuardNameJ = Trim(strGuardName) strGuardICPass = Trim(Mid(ARBFiles(x), 141, 12)) strGuardICPassJ = strGuardICPass & " " strAmount = Mid(ARBFiles(x), 271, 13) strAmountCent = Right(strAmount, 2) strAmountDecimal = "." & strAmountCent If Left(strAmount, 11) = "00000000000" Then strAmountRinggetFilterJ = "0" strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal strAmountJ = strAmountCombine & " " ElseIf Left(strAmount, 10) = "0000000000" And Left(strAmount, 11) <> "00000000000" Then strAmountRinggetFilter = Right(strAmount, 3) strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 1) strAmountRinggetFilterJ = strAmountRinggetFilter1 strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal strAmountJ = strAmountCombine & " " ElseIf Left(strAmount, 9) = "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then strAmountRinggetFilter = Right(strAmount, 4) strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 2) strAmountRinggetFilterJ = strAmountRinggetFilter1 strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal strAmountJ = strAmountCombine & " " ElseIf Left(strAmount, 8) = "00000000" And Left(strAmount, 9) <> "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then strAmountRinggetFilter = Right(strAmount, 5) strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 3) strAmountRinggetFilterJ = strAmountRinggetFilter1 strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal strAmountJ = strAmountCombine & " " ElseIf Left(strAmount, 7) = "0000000" And Left(strAmount, 8) <> "00000000" And Left(strAmount, 9) <> "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then strAmountRinggetFilter = Right(strAmount, 6) strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 4) strAmountRinggetFilterJ = strAmountRinggetFilter1 strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal strAmountJ = strAmountCombine & " " ElseIf Left(strAmount, 6) = "000000" And Left(strAmount, 7) <> "0000000" And Left(strAmount, 8) <> "00000000" And Left(strAmount, 9) <> "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then strAmountRinggetFilter = Right(strAmount, 7) strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 5) strAmountRinggetFilterJ = strAmountRinggetFilter1 strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal strAmountJ = strAmountCombine & " " ElseIf Left(strAmount, 5) = "00000" And Left(strAmount, 6) <> "000000" And Left(strAmount, 7) <> "0000000" And Left(strAmount, 8) <> "00000000" And Left(strAmount, 9) <> "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then strAmountRinggetFilter = Right(strAmount, 8) strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 6) strAmountRinggetFilterJ = strAmountRinggetFilter1 strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal strAmountJ = strAmountCombine & " " ElseIf Left(strAmount, 4) = "0000" And Left(strAmount, 5) <> "00000" And Left(strAmount, 6) <> "000000" And Left(strAmount, 7) <> "0000000" And Left(strAmount, 8) <> "00000000" And Left(strAmount, 9) <> "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then strAmountRinggetFilter = Right(strAmount, 9) strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 7) strAmountRinggetFilterJ = strAmountRinggetFilter1 strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal strAmountJ = strAmountCombine & " " ElseIf Left(strAmount, 3) = "000" And Left(strAmount, 4) <> "0000" And Left(strAmount, 5) <> "00000" And Left(strAmount, 6) <> "000000" And Left(strAmount, 7) <> "0000000" And Left(strAmount, 8) <> "00000000" And Left(strAmount, 9) <> "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then strAmountRinggetFilter = Right(strAmount, 10) strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 8) strAmountRinggetFilterJ = strAmountRinggetFilter1 strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal strAmountJ = strAmountCombine & " " ElseIf Left(strAmount, 2) = "00" And Left(strAmount, 3) <> "000" And Left(strAmount, 4) <> "0000" And Left(strAmount, 5) <> "00000" And Left(strAmount, 6) <> "000000" And Left(strAmount, 7) <> "0000000" And Left(strAmount, 8) <> "00000000" And Left(strAmount, 9) <> "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then strAmountRinggetFilter = Right(strAmount, 11) strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 9) strAmountRinggetFilterJ = strAmountRinggetFilter1 strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal strAmountJ = strAmountCombine & " " ElseIf Left(strAmount, 1) = "0" And Left(strAmount, 2) <> "00" And Left(strAmount, 3) <> "000" And Left(strAmount, 4) <> "0000" And Left(strAmount, 5) <> "00000" And Left(strAmount, 6) <> "000000" And Left(strAmount, 7) <> "0000000" And Left(strAmount, 8) <> "00000000" And Left(strAmount, 9) <> "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then strAmountRinggetFilter = Right(strAmount, 12) strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 10) strAmountRinggetFilterJ = strAmountRinggetFilter1 strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal strAmountJ = strAmountCombine & " " Else strAmountRinggetFilter = Right(strAmount, 13) strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 11) strAmountRinggetFilterJ = strAmountRinggetFilter1 strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal strAmountJ = strAmountCombine & " " End If strFileOutputJ = strFileNo & strBenCdC & strBenNameJ & strBenICPass & strGuardNameJ & strGuardICPass If strFileOutputDB <> strFileOutputJ Then strARBFileContent = SeqNumJ & strFileNo & strTxnStatus & strBenCdJ & strBenName & strBenICPassJ & strAmountJ & strTxnYMJ Print #2, strARBFileContent ElseIf strFileOutputDB = strFileOutputJ Then Exit Do End If End If Next x RS.MoveNext Loop
How To Extract Records And Display Report Of Records That Are Blank
I am using the databound control in a new vb 6 program for a database of complaints. I have name , address, team, response number, open date, assigned date and completed date and some other fields that are all bound to a data2 control. I need a query that will return a report that lists all the records for a specified month (using a drop down box)with no Date_Complete (nothing entered into the field). So if the user selects March -- 2000 in two different drop down boxes. I need fields that have nothing in the Date_Completed field to print in a report. Only the records that have nothing in the Date_Complete fields should print or make the report. So the user can go back and make sure these complaints get completed. I would really appreciate the help; desperate to finish my program and it is overdue; Also, I have been using the data report feature but am open to suggestions. I don't know crystal reports at all so I need some exact and specific code here -- thanks!!!
even help w/ code using the drop down boxes to extract the data -- for example if the user select March in one drop down box and 2000 in another drop down box. I need the records that are not completed (Date_Completed) field to only show up in the report for March 2000.
How To Extract Records And Display Report Of Records That Are Blank
I am using the databound control in a new vb 6 program for a database of complaints. I have name , address, team, response number, open date, assigned date and completed date and some other fields that are all bound to a data2 control. I need a query that will return a report that lists all the records for a specified month (using a drop down box)with no Date_Complete (nothing entered into the field). So if the user selects March -- 2000 in two different drop down boxes. I need fields that have nothing in the Date_Completed field to print in a report. Only the records that have nothing in the Date_Complete fields should print or make the report. So the user can go back and make sure these complaints get completed. I would really appreciate the help; desperate to finish my program and it is overdue; Also, I have been using the data report feature but am open to suggestions. I don't know crystal reports at all so I need some exact and specific code here -- thanks!!!
even help w/ code using the drop down boxes to extract the data -- for example if the user select March in one drop down box and 2000 in another drop down box. I need the records that are not completed (Date_Completed) field to only show up in the report for March 2000.
How To Get Total Number Of Records Of Records Return By Recordset?
I wanna get the total number of records returned by the record set. I used RecordCount, but it always return -1.
some 1 plz help me out.
Dim cnn as ADODB.Connection
Dim ars as ADODB.Recordset
set cnn = new ADODB.Connection
With cnn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:MapData.mdb;Persist Security Info=false"
.Open
End With
Dim SQL as string
SQL = " Select * from Education"
set ars = cnn.Execute(SQL)
MsgBox ars.RecordCount 'NOR WORKING???
...---.....
Hassan Zia
Hassan_Zia@mathtech-pk.com
Only Need 8 Records From Table
I have an SQL statement selecting all records in a table. I then display them on a page. This worked fine for a couple of months, but now the table has grown too large to be practically displayed, though I want to retain the records (IOW, purging old records isn't an option right now).
Here's the query, followed by the code snippet that displays them...
str_qry_all = "SELECT week from tbl_schedule ORDER by week desc"
-----------------------
<%do until rs_scheds.EOF
for each x in rs_scheds.Fields %>
<td align="right"><input type="submit" name="sched_date" value="<%=x.value%>"></td>
<%next
rs_scheds.MoveNext%>
</tr>
<%loop%>
What I now need is only the last 8 records from the table. Someone told me that he thought a way exists to do that with the SELECT statement, but didn't know how off the top of his head.
I'd like to see that for future reference if anybody knows how, but I think the better thing to do would be a FOR x = 0-7 type loop on the recordset returned by the existing SQL statement. I've fiddled with it, but can't get it in the right place.
Can someone help me out here? Thanks
Records In A Table
I found out how to display how many records where in a table.
Code:
lblCountTotal = adoAuto.Recordset.RecordCount
Now I'm looking to find out how I can figure out which record I'm currently on in the table. Does anyone know the syntax for that?
Add Records From One Table To Another.
Hi
I have tables in access database. I want to add some records from one table to another on some specific condition. HOw to do this from vb form.
I wan tto select some records from my datafgrid and than want to add them in another datagrid. How can i do this???
eagerly waiting.
Select Statement Returns Records Marked For Deletion
I 'm using the connection object "Execute" method to get a recordset. My SQL query looks like this:
select * from orders where ordernumber = 99999
the problem is...when I use the execute method, it returns me the rows that marked for deletion too. (Database is DBASE IV)...I tried using recordset.open method, but that does not fetch any records at all.
I have no idea what am I doing wrong.
Any help would be greatly appreciated....
Joining Two Select....statement To Pull The Records Needed
Below is my code. I am trying to select only the records meeting the two criteria and then clone the data to a datagrid. I need helo joining the two select statements. I have try "and"...but it does not work.. it says data mismatch.
there are two text box for user input for the select"
polookup1
itemnolookup1
then a command button for click to execute the following:
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=J:purchaseorders.mdb;Persist Security Info=False"
Set CN = New ADODB.Connection
CN.Open strConn
stcnQL1 = "SELECT * FROM tpurchaseorders where pono=" & "'" + Polookup1.Text + "'" And "SELECT * FROM tpurchaseorders where itemno=" & "'" + Itemnolookup1.Text + "'"
Set RS1 = New ADODB.Recordset
With rsclone
.ActiveConnection = CN
.CursorLocation = adUseServer
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Properties("IRowsetIdentity") = True
.Open stcnQL1, , , , adCmdText
End With
Set Adodc1.Recordset = RS1
Set DataGrid1.DataSource = Adodc1.Recordset
Set rsclone = Adodc1.Recordset.Clone
Please help me joined the two select statements
thanks in advance
*** Resolved *** Trouble With My SQL Statement Not Returning All Data From Records
I am working with an Access database that has three tables. I have got my Select statement working great but I have found that I don't seem to be getting the primary keys from each of the tables. I get the primary key from my tblAppt but I don't get the secondary keys from this table and I don't get the primary keys from the other 2 tables. Here is my SQL statement:
Code:SELECT * FROM tblResident INNER JOIN (tblDoctor INNER JOIN tblAppt ON tblDoctor.Dr_ID = tblAppt.Dr_ID) ON tblResident.Resident_ID = tblAppt.Resident_ID ORDER BY Appointment_Date, Appointment_Time ASC
As you can see the tblAppt contains the primary keys from the other tables as secondary keys and yet when I try to display them in my extended listview control they aren't in the recordset. The statement I get is:
Quote:item cannot be found in the collection corresponding to...
Anyway if someone has a fix for this or a suggestion for me to try please let me know, Thanks.
Later,
Cliff R.
Mesa, AZ
Check out my website at http://crogers.org
Being an organ donor is giving the greatest gift that someone can give.
It is giving the gift of life. Please register to be an organ donor.
Edited by - crogers on 7/23/2005 10:00:18 PM
How To Loop Through All The Records In A Coloum And Perform A Check Statement?
I am trying to implement a code that loop throught the partID coloum of a table called tblExamDetails and check if the coloum contains the number 1, 2 and 3
so far I have coded the following:
Code:
Private Sub cmdSave_Click()
Dim db As Database
Dim rec As Recordset
Set db = CurrentDb()
Set rec = db.OpenRecordset(tblExamDetails)
Do While Not rec.EOF
If rec(fkPartID) = 1 Then
' How do I frame the if statment to check if the coloum contains number 1, 2, and 3
End If
rec.MoveNext
Loop
End Sub
can anyone advise me on how I can build the check statement to see if the coloum contains number 1, 2, and 3? The condition is only true if the coloumn contains all the number 1, 2 and 3 else false.
Thanks
Add New Records To A Table Created Using ADO
Hi All
I have, for the first time, been able to use ADOX technology to create a new Access database. I have also been able to create a table within that database and then add columns to that that table. Having done all this, I now want to add new records to this table. I would then also like to create a query on that table and read the data from this query in to an array. How do I do that? I did all the above using DAO's but thought I need to familiarize myself with the new ADO technology but am not able to find any help for ADO's in my Access 97 help, hence the query here!
Can someone please help me how to do the above and also point me to where I can read upon to familiarize myself with this ADO business - I was able to do all this with DAO's.
The code so far for the ADO that I have been able to get working is reproduced below for immediate reference :-
Code:
'Access has already been opened prior to this statement
Dim adoxCat As Object, tbl As New Table
Set adoxCat = CreateObject("adox.catalog")
adoxCat.Create "Provider='Microsoft.Jet.OLEDB.4.0';Data Source='" & Application.Path & "" & "TempDb.mdb'"
tbl.Name = "ArrayData"
colCount = UBound(startArray, 2)
With tbl
start = 0: wrkg = start
Do While wrkg <= colCount
thisFieldName = "Field" & wrkg
.Columns.Append thisFieldName, adDouble
wrkg = wrkg + 1
Loop
End With
cat.Tables.Append tbl
'I now want to add new records to the table created above. I was able to do that with DAO's as shown below :-
i = 0
Do While i <= UBound(startArray, 1)
With mydb.TableDefs("ArrayData").OpenRecordset
.AddNew
start = 0: wrkg = start
Do While wrkg <= colCount
.Fields(wrkg) = startArray(i, wrkg)
wrkg = wrkg + 1
Loop
.Update
End With
i = i + 1
Loop
Any suggestions?
Best regards
Deepak Agarwal
Delete All Records From A Table?
I want to delete all the records in a table in Access. How would I do this? Would I open the recordset then delete them?
This is what I have thought of:
Code:
Dim db As DAO.Database
Dim tblApp As DAO.Recordset
Set db = CurrentDb
Set tblApp = db.OpenRecordset("Appointments")
If tblApp.EOF And tblApp.BOF Then
With tblApp
'This is where I need the code to every record in every field
End With
End If
End Sub
What code would I use?
Replace Records In Table
Hi,
We are trying to make changes to a table using VBA using the replace method using the following code.
We consistently get an error missing two p[arameters at the db.execute statement. We are using Access 2000/2002. Can anyone help????????
private sub blahblahblah()
dim db as Database, rst as recordset
dim varnumber as variant,strsql as string, str as string
if isnull then
....details
end if
set db=Currentdb
strsql="clause 1"
strsql=strsql & "rest of clause"
db.execute strsql,dbfailonerror
cbocurrent-cbonew
cbonew=null
requery
end sub
How Do I Add New Records In A Related Table?
Hello everyone,
I thought this would be simple, but i think i am stuck. I have an Access 2000 DB with a few Tables, there is two that i am concerned with at the moment. The first Table, called "Sites", has a primary key field called SiteNumber It's format is 99-9. The second Table, called "Equipement" has a related foreign key called SiteNumber. One site can have many pieces of equipment. I am fairly new to database programing, i have just finished creating my Data entry form in vb to populate this database. I have two dao data controls, one for the site table and the other for the equipement table. So far i can add records to the site and i can display all related equipment for the current site that is displayed, but if i add a new record, i'm not sure how to add the record to the equipment table as well. For example: when the user adds a record and types in the new site number, how do i add that site number to the equipment table?
Hope i was able to explaine myself ok
Thanks in advance
Copy Records From One Table To Another In VB
I am writing a program that will be used for simple data entry, and will be save into a Access database. Also it will search the database for a person's last name and display all that person's info on the screen. Eventually this database will store over 3,000 records so I decided to split the alphabet up four ways with two of the four groups having 7 instead of 6 letters. Anyway I need to figure out how to copy the information the user has just entered to the correct database using their last name. What I was thinking would work was to enter it into one of the tables and then copy that record to the table that it is supposed to go to when they save their work. I can't figure out how to copy a record from one table to another in VB. Would that be the easiest way or is their some other way to do this? The problem with just changing the RecordSource property of the data control is that you have to do the AddNew method to add a new record in the new table and then clears out all the text boxes and entry fields.
Thanks
Adding Records To DB2 Table
I am new to Visual Basic and I'm trying to figure out how to add records to a DB2 table. What is the difference between addnew and the DB2 insert? Do I use both, or will addnew and update do what I need?
How To Add Records To A Table From A Combobox?
hi everyone! im a new VB user and im having problem on how to add record to a database table from a combobox...pls give me some tips on how to do it.
and also i want to ask how to create a report in VB? by the way im using VB6.0 in windows XP..
thanks...
leny
Inserting Records Into SQL Table
I wrote a vbscript that writes the output to a file. I woudl like it to instead insert the records to a SQL Server table. What am I doing wrong from the code below:
Dim cn As New ADODB.Connection
Dim connString As String
Dim rs As New ADODB.Recordset
Dim sql As String
connString = "Provider=SQLOLEDB;Data Source=master;Initial Catalog=Test;User Id=User;Password=yes;"
cn.Open connString
rs.ActiveConnection = cn
rs.Source = "wrk_test"
rs.Open, cn
For Arrayloop = 0 To ArrayCount - 1
sql = "INSERT INTO wrk_test (col1, col2) VALUES " & 1
"," & 2 & " "
rs.Open sql, connString,,,adCmdText
Next
rs.Close
cn.Close
TIA.
Delete All Records In A Table
How can I handle this ?.
Today I am using sql-query against my access-database for
getting data and update data in my tables.
But how can I simply delete all records I have in a specific table ?.
I can do it simply from access, but I need code for running this from my
VB application.
Add Records From One Table To Another If The Don't Excits??
I am stumped on this and can't figure it out. I am working with vb6 and a remote mysql databse. What I am doing is takeing infromation from one table and bring that into my vb6 program, add a few things to it then write them to another table. What I want to be able to do is to read in the infromation from table 1 (this part is no problem) add my information (still no problem) then compare it to what is in the second table and if lets say inumber is already in the second table then do nothing if the inumber is not in the second table then insert the infromation. This is what I have so far.
Read the information into vb6
Code:
Set rs = New ADODB.Recordset
rs.Open "SELECT inumber,name,price FROM products Where inumber>1", conn, adOpenStatic, adLockOptimistic
Do Until rs.EOF
Text1 = table2.Text + rs.Fields("inumber").Value
Text9 = rs.Fields("name").Value
Text2 = rs.Fields("price").Value
Add my information in vb6
Code:
pp.Text = pp1.Text + pp2.Text + em.Text + pp3.Text + Text9.Text + pp4.Text + Text1.Text + pp5.Text + Text2.Text + pp6.Text
Insert the infromation into the second table
Code:
conn.Execute "Insert Into " & table2.Text & "(inumber2,price2,paypal) VALUES('" & Text1.Text & "','" & Text2.Text & "','" & pp.Text & "')"
Timer1.Enabled = True
Timer1.Interval = 300
rs.MoveNext
Text1.Refresh
Text2.Refresh
Loop
The problem comes with the insert command, I do not no how to compare the inumber I am sending to the inumbers already in table2.
Finding Records In One Table That Are Not In Another One
Hello,
I need to find records and add them to a results table
The search will be done overnight everynight but I don't want to re-add records that are already in the results table.
Instead of finding a record, then looking up to see if its in the results table already is there somekind of join syntex I could use to check?
Thanks
Rhys
Finding Table Records
I have 3 tables that I need to search to pick up data which will populate another table.
I started on the first record of the table that I want to populate with data and want to search the other 3 based on specific criteria. I thought I could use the recordset.find to locate a record, but it is not working:
Code:
rs1.MoveFirst
rs2.MoveFirst
rs3.MoveFirst
rs4.MoveFirst
While (Not rs2.EOF)
rs1.Find (rs1!memo_id = rs2!User)
lcmailstop = rs1!dept_branch
rs3.Find (lcmailstop)
lccostcntr = rs3!cost_center
rs4.Find (lccostcntr)
rs2!Dept_Num = rs4!branch_num
rs2!Region = rs4!Region
rs2.MoveNext
Wend
I think I am using this Find incorrectly. Not sure how. But when I run this code, I get "Arguments of the wrong type, are out of acceptable range, or are in conflict with one another"
for the "rs1.Find (rs1!memo_id = rs2!User)" lines of code.
Any suggestions?
How Do You Determine What Records Are NOT In A Table?
I was setting up my relationships between 2 tables, Table1 (one) and Table2 (many). I set the "Enforce referential integrity" and "cascade delete related records". But I get the "can't enforce referential integrity" error because the MANY table has records that the ONE table does not.
Is there an easy way to build a query or something to show which records in the MANY table are not the ONE table? I have been trying to do this for some time but mr. brickwall keeps stopping by.
Thanks.
Records As Collumns For A New Table
Hi all,
I am trying to make a new table in access where the collumns are created out of records from an excisting table in the same Database.
I am trying to use ADOX to create the table as I loop through the excisting table to get the records for my collumn names.
when I try this I get the error:
No such interface supported
can anyone help me out?
........................
........................
With objMyTable
.Name = "cijfers"
While rs.EOF = False
.Columns.Append rs.Fields(1), adVarWChar, 20
rs.MoveNext
Wend
End With
objcat.Tables.Append objMyTable
Set objMyTable = Nothing
Set objcat = Nothing
End Sub
Copy Records From One To Another Table
hi all,
i want to know how can i copy the All records of one table to another table of the same structure
AND ALSO
i want to know how can i copy only selected fields of one table to another table.
please help.
INSERT INTO All Records In A Table
Hi all,
I want insert a number and a date into all the records in a particular table. I.E. There could be 100 records in a table and I want to insert the same week# and date into all the records. However, when I try to use the INSERT INTO function of SQL it is simply adding 1 new record to the table and adding the week# and date to this record.
How do I add the week' and date to all records in the table?
Thanks,
SQl Table Max Number Of Records
Hi
How many records can an SQL server table support?Because I am planning to send the daily production to an SQL server table "Production 2006" and the average production is 500 records/day, and we have 360 Productions,so 360*500 would be a great number of records,isn't it?
if so,what's the solution
Loop Through Records In A Table
Can anyone show me some code to select all the records from a table and loop through each record so I can change one field in each record to a different value? Each field value will be different number.
ADO with SQL Server 2000
Thanks
|
TRACKER
