I have two table with the same key field. However, in one table it is a text field while the other is a number.
I created a query from the table with the numeric key and converted it to text using the str() function. When the query is run, the values appear, left justified as if they are text.
However, when I create another query using the first query and the other table, I get no records out even though there are matching key field values in both tables (there are no leading zero problems).
I am trying to combine two peculiar tables in Microsoft Access and have been unable to do so even after doing a lot of brainstorming and searching on the internet.
The two tables are spend and export Spend +-------------+--------+-------+-------+ | Country | Metal | Month | Spend | +-------------+--------+-------+-------+ | China | Iron | Jan | 100 | | China | Iron | Feb | 200 | | China | Iron | March | 300 |
Now that i have read this again, i think it could be summed up into one question...if i have a form based off a query with an outer join that has various duplicate records, is there a way to use the recordset in an if statement that says something like if this recordID = that recordID then dont show one of them...hence not showing the duplicate field data in the form.If you want a more specific description of the problem, read on, otherwise don't read on.Hi All,So I hope I can explain this ok....here goes....I have a search using dynamic queries: I have a form where the user can put in various information he wants to search to find a record. In this case it is searching for Hotels. So the user can search a country to see all of the hotels in that country. Also, the user can search an interest like Beach or Nature to see those hotels that apply. Obviously each hotel may have more than one interest so I have a 1-many relationship with a table called Hotels_Interests.The kicker, and you can likely already see why, is that the user does not have to fill out every search field. He may search Country&Interest, or just one or the other, or leave everything blank to see all hotels in the database. The results are simply ordered by HotelID or something like that in a form that is based off the dynamic query. The dynamic query is of course just based off the query i explained, but with criteria added in.The problem is with the query that i am basing this search off of. Right now it has the main Hotels table as well as the 1-Many table Hotels_Interests and even another that is 1-Many Hotels_HotelTypes (say All Inclusive, Resort, etc.). So this query has various 1-Many tables as well as the main Hotels. Now, if i fill in all of those fields in the search form, there will obviously not be any duplicates returned, which is super. But if i leave Hotel_HotelTypes search field blank, i will be returned with the same hotel twice or more times, which is my problem, because i want nice search results.I have heard of people using Union queries to get rid of duplicates but this obviously does not solve my problem as i do not want to just get rid of these entries. What i think i want is some VBA method or whatever of showing in my search results each HotelID that meets the search criteria only one time.Right now i have it working with If statements that say if the user has left a specific search criteria blank then base the search off a different query. This is obviously crazy and is only a temp fix. Now that i want three or more 1-many tables in my query, i would be talking about if statements for like 6 or more queries, insane.I apologize for the length of this, but i wanted to be perfectly clear. I feel like it should be not too hard, like using a record set for the form and not showing certain records or something, but i am not sure how to do it.Thanks so much. Dillon
I know there is no more than 4 dups of each record.
what i want from this is a table that will give me a record of how many dups for each record then all the dates that they were added and the date between each record entry.
I've been toiling with the issue of WHERE clauses on the "Right" side of Left Joins. I'm aware that you need to use JOIN ON......AND.... rather than JOIN ON....WHERE.... if the WHERE relates to the Right Hand table.
I've even got an example in my DB where the above works, but now am struggling to use the same theory for other tables. Therefore, I went and created two Mickey Mouse tables to test the logic but am getting an error.
I have Table 1 with one field called Field 1 - values A, B, C Table 2 as follows
Field 1.....Field 2.......Field 3 A.............100 C.............200..........XXX
I hoped to have a query that finds all records on Table 1 and records on Table 2 where Field 1 matches on the two tables and Field3 = XXX
My SQL is SELECT Table1.Field1, Table2.Field1, Table2.Field2, Table2.Field3 FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1 AND Table2.Field3="XXX";
And then called this join as a symbol or variable, and then have it use to select the items from these joined tables, can this be done in Access? Here is an example of a code that I created, but it has an error message saying the FROM syntax is incorrect.
Code: SELECT firstJOIN.trainID, firstJOIN.trainName, firstJOIN.stationID, firstJOIN.stationName, firstJOIN.distance_miles, firstJOIN.time_mins FROM (trains INNER JOIN ((station INNER JOIN lineStation ON station.stationID = lineStation.stationID) INNER JOIN bookingLeg ON bookingLeg.startID = station.stationID or bookingLeg.endID = station.stationID ) ON trains.trainID = bookingLeg.tid) as firstJOIN
Can Access do something similar to this, in the FROM statement I joined 4 tables, because each unique fields are in each table and I have to joined them to get those fields. I called this join firstJOIN and in the SELECT statement, I list those columns in the table by calling it firstJOIN.trainID. Can Access do something like this, but syntax it differently?
Hey, all! Thanks for helping, here is my situation.
I have a table with about 70,000 records that have duplicate Address field values. The rest of the field values for those records are different. When I do a find duplicate querry I get the result that 17,000 records have the same address. However, when I do the append qurrey as instructed here: http://support.microsoft.com/?kbid=209183 I get a total of only 600 records in the new table. I have tried deleting all of the indexes for both the new and old table, with no luck.
I'm using Access 2000 on XP Pro.
If anyone could help with this I would greatly appreciate it!
I have an Access table with an indexed key that is currently set to duplicates OK. There are not supposed to be any duplicates in that field. But it is possible than a small number have crept through.
Two questions:
1. What would happen to those duplicate records if I changed the setting from duplicates OK to no duplicates?
2. Is there any way to ferret out those duplicate records first and change them manually? The table currently has 48000 records so it would be a pain to go through them page by page. (I know how to export them to Excel, where the dups can easily be found; I was just wondering if something could do it within Access.)
I am trying to do an inner join with a left join. The only problem is, I want to inner join the table that is being joined. This is how I thought it would work below, but it doesn't work.
SELECT * FROM ((Submissions LEFT JOIN Candidates ON Submissions.`Candidate Code` = Candidates.`Candidate Code`) INNER JOIN `Type Candidate Status` ON Candidate.Status = `Type Candidate Status`.`Status ID`) WHERE Submissions.Status <> 7 ORDER BY `School Interest` DESC;
I want to get the Candidate.Status to inner join with the `Type Candidate Status`.`Status ID`.
i am trying to run an append query in SQL which appends a calculated value into another table 50 times incrementing the day by one day each time. When I run it it asks me for the parameter [NewDay] each time. It is obviuosly not picking up the variable.
Can anyone tell me why ?
Dim NewDate As Date Dim n As Integer
For n = 0 To 50
NewDate = Date + n
DoCmd.RunSQL "INSERT INTO InventoryEvolution ( SAP, Stock, [Date] ) SELECT UK_Product_Estimate_Live.[RE SAP Code], ((Sum([Estimate01])+Sum([Estimate02]))/50)*-1 AS Stock, NewDate From UK_Product_Estimate_Live GROUP BY UK_Product_Estimate_Live.[RE SAP Code] HAVING (((UK_Product_Estimate_Live.[RE SAP Code])=513450))"
Hi all, I get this error "You tried to execute a query that does not include the specified expression 'SITENAME' as part of an aggregate function."
The query is:
SELECT o1.name AS SITENAME, o1.vertical_loc/10000000 AS LAT, o1.horizontal_loc/10000000 AS LON, c.CELLGLID AS CELLID, 'traffic_total' AS Expr1, sum((MEBUSTCH_HR+MEBUSTCH_FR)*period_duration)/sum(period_duration) AS Traffic, 'traffic_hr' AS Expr2, sum(MEBUSTCH_HR*period_duration)/sum(period_duration) AS Traffic_HR FROM objects AS o1, objects AS o2, c_bts AS c, p_generic_cell AS p WHERE c.int_id = o2.int_id and o2.parent_int_id = o1.int_id and p.bts_int_id = o2.int_id GROUP BY SITENAME, LAT, LON, CELLID, Expr1, Expr2;
I know the problem is with the group by clause. In MySQL it just works using "group by p.bts_int_id. I learnt in access i have to include all non-aggregated fields in the group by clause, and that's what I've done!! WHY!!Please help me!!!
Set cmd = New ADODB.Command Set cmd.ActiveConnection = CurrentProject.Connection cmd.CommandType = adCmdText
cmd.CommandText = "SELECT par1 FROM tblparameters Where tblparameters.gcnf = 'XMLexp' AND ((tblparameters.ccnf) Like 'ExpTijd*')" Set rec = cmd.Execute()
Do While rec.EOF = False MsgBox rec("par1").Value rec.MoveNext Loop
I don't get any result back. If I changed it likt the following: tblparameters.ccnf = 'ExpTijd1' , in the query, I get one record back. So my conclusion the query is right but the Like doesn't work in these circumstances?
hi folks, I am designing an application for an assignment for college, all seems to be going ok except that I have to teach myself access from a book. When I update a record on a form and go to another page it works fine, but if I attempt the same action ( for example creating an appointment then moving to a new form to confirm they have arrived) a second time I am getting an error which tells me I can't save the record. I think it is to do with updating the recordset when the new form is opened. I have an update record button on the form (created by the wizard) but it wont update on the second attempt. Could someone please tell me the code to update a recordset on the formload procedure. Ive tried me.recordset.refresh and me.recordset.requery but I still can't solve the error. Please help I've got to hand this assignment in in 2 weeks. thanks in advance Chris
I'm trying to display a choosen record from a table with a record id that is a TEXT data type. A variation of this same code works correctly in another form where the record source is a table with a record id that is a AUTONUMBER data type.
The below code (Access 2000) displays the first record in the table.
Private Sub cmdTypeCodeSearch_Click() On Error Resume Next
Dim strtyTypeCodeID As String strtyTypeCodeID = GettyTypeCodeID
'Just put this in to verify record id MsgBox "You selected " & strtyTypeCodeID & " for your record key"
If Not IsNull(strtyTypeCodeID) Then Me.RecordsetClone.FindFirst "[tyTypeCodeID] = " & strtyTypeCodeID If Not Me.RecordsetClone.NoMatch Then Me.Bookmark = Me.RecordsetClone.Bookmark Else MsgBox "Record Not Found" End If Me.Refresh End If
I have a table with approximately 700 records. This table consists of a location field. I want a user to be able to select a location from this table via a form. Listboxes and Combo Boxes hold 255 rows. Plus, what user would want to use one of those to search through to find the location they are looking for. Any body have any ideas on a better way to do this? They have to stick to the list, otherwise I would just have a text box for them to type in.
Hi! I'm relatively new to Access (2003) . I had to modify a shared network app so I copied it off and renamed it, made my mods and everything is fine. I then went to another app that connects to the original app to obtain data for display and synchronization, modified the call to open the new app including the new mdw. When it hits the new mdw, it can't open it because it says it's being used exclusively or the workgroup file is missing. It's not being used exclusively and it doesn't appear to be missing. The network path is correct. If I use the mdw from the original source app, it works fine. Please advise! :(
I'm sorry if my thread is in wrong section, cause I'm first time on this great forum :(! I hope you'll undrestand me ;)
My problem is with my database for my coffee bar, man who made database is still, and I don't have somebody to repair my database unfourtanetly :(
I formatted my C:, cause I installed new OS Windows XP PR SP2, and everything is deleted, but I save this .mdb file, but I don't have instalation CD for this program, cause man who installed me this database didn't give me instalation file :(!
And I saved this .mdb file, and now I try to run this POS.mdb,I can open "POS KASA" in english POS CASH, and I see articles - (ARTIKLI), and value of them it account values of them, just program doesn't count for me like before "sume" or "total" of EX. one count, for example coca cola 2.5 KM, fanta 2.5 KM = 5 KM, and now he doesn't count sume - total in program total is "UKUPNO", if somebody to know to repair this, or something another what is importing to work this database like before, please help me, database is on bosnian, if you need translating just ask me, cause I don't know to edit this file, and to have access to change words :(!
1'st colone in program interface (bosnian - english)
Sifarnici -something like codes
Artikli = Articles Ulazi = Inputs Storno racuna = storno count Pregled storno racuna = review storno count
2-nd colone in program interface
Dokumenti - Documents
POS kasa = POS cash register Dnevni promet = daily exchange Trenutni saldo kase = current saldo of cash register
3-rd colone in program interface Reports
Printanje prometa po broju = Printing exchange by number Printanje prometa od datuma do datuma = Printing exchange from date to date Printanje trenutnog prometa = Printing current exchange
IZLAZ = EXIT :)
Actually for me the most important is POS CASH REGISTER, cause it doesn't count total value of one count , total = ukupno doesn't work, sume doesn't work, and I can't give on my guests count, and I have problem with inspection cause I don't have counts :(, please help me and if you can repair this; IN ADVANCE TNX MUCH!
Greetings to all, from Sarajevo, Bosnia and Herzegowina!
I'm sorry really on my bad english, and I hope you'll understand me :(!
I want to execute a delete query on a table of account records. The query contains two tables linked on account number, one with a list of unique records, linked to the other with "many" records to be deleted. For each unique record from table A, I want the delete query to delete all records with a matching account number in table B. However, I get a message stating that I "could not delete from the specified table." Huh???
I can manually delete from table B, or I can get the query to work if I don't try using table A as the control table and instead specify literal criteria. I have tried all three join types but still no luck. Any ideas?
I have an access query that I run on a regular basis but that doesn't always sort. It sorts most of the time, but once in a while it won't, which makes it really annoying and impractical.
I'm using the following code to autofill the city and state on my form. I can't figure out how to capture if there is no matching record in the table. If there is no matching record, I will:
1) inform the user with a msgbox then if the user wants 2) open a form to add the record to the table
Dim rst As DAO.Recordset Dim db As DAO.Database
Set db = CurrentDb Set rst = db.OpenRecordset("tblZipcodes", dbOpenTable)
rst.MoveLast rst.MoveFirst
Do Until rst.EOF
If strZipLookup = rst!ZipCode Then Me.txtCoCity = rst![ZipCity] Me.cboCoSt = rst![ZipState]
End If
rst.MoveNext
Loop
rst.Close End If
How can I capture if there is no matching record in the table?
Have a number of tick boxes on different forms. When the form is opened the tick box appears to be greyed out however it is enabled. Is there any way that these tick boxes can appear enabled???
I have a subform (based on table "Source") that allows the user to create a new record in another table "Events" (it copies some entries to facilitate entering new data). The data should then be dispayed in another subform. However, the newly created redord is does not show in the subform although I use Me.Refresh. When I close the form and then open it again it works - but there has to be an easier way.
How can I reload the data from the table. I have looked but am unable to find the right method.
The code:
'Create New Record in Table Events
Call AddEvent(EventDate, Country) 'This function opens the table and adds a record
On one of my Access2000 forms, I have the following sub that takes the user to a particular record whose value is passed to the sub as a variable. It frequently results in EOF being true because the recordset does NOT get all the records in it during rs=RecordsetClone. I set a msgbox to tell me the RecordCount, and even though there are almost 3000 records in the underlying table, the rs.RecordCount was anywhere between 300-750 each time I ran the code. Therefore, rs.find won't find the right record most of the time, because that record won't be in the recordset. What causes this and how can I fix it?
Here's the code -- I've got MoveLast and MoveFirst both in there to help ensure that all the records get grabbed, but that had no effect; I still get EOF:
***************************** Public Sub GotoOrder(pOrdID As Long) On Error GoTo Err_GotoOrder
Dim rs As ADODB.Recordset Set rs = Me.RecordsetClone
