how to combine two tables in access?
924206
to display the records from Sales table and Purchase table, do like Monalisa said, but he/she missing 'WHERE' query.\n\nassume :\nCreate Table Sales (Sales_Date date, total integer);\nCreate Table Purchase (Purchase_Date, total integer);\n\nSelect Sales_Date, Total from SALES where\nSales_Date>='10/03/2006' and Sales_Date<='10/09/2006'\nunion all\nSelect Purchase_Date, Total from PURCHASE where\nPurchase_Date>='10/03/2006' and Purchase_Date<='10/09/2006'\n\nNotes :\n'UNION' will eliminate the same records from PURCHASE table.\nto get all records we must use 'UNION ALL' .\n\nTo sum sales and purchase in 3rd to 9th oct  :\n\nSelect sum(A.Total) as TotalSales, sum(B.Total) as TotalPurchase from SALES as A, PURCHASE as B where A.Sales_Date>='10/03/2006' and A.Sales_Date<='10/09/2006'\nand B.Purchase_Date>='10/03/2006' and B.Purchase_Date<='10/09/2006'\n\nps :\nmy query format maybe have a little different with ms Access query format, so if my query can't run in ms Access, try to change it to ms Access query format.
948329
It sounds like you are using a linked table that does not have a primary key. Without a primary key, I think Access looses track of which row is which, and gets this error. Even if you don't want to use a primary key, you could add a primary key column to the backend table (for example, with an auto-increment value) just for Access to use. Then you can leave that column out of your queries in Access.
112245
Microsoft - Its called Access (Database)
544272
File.. Get External Data.. Link Tables .. browse to the xls file    would set up a permanent link to the excel data and give the spreadsheet a kind of table name. Then it can be queried. You want to use several columns over time and you can use an 'Append query' to append data to a table; but experiment with a crosstab query. In that way, your Access table would always have 2 fields plus maybe a ColumnNumber field where you could have a default value of 1 the first week, 2 the next week, etc.. A crosstab query would present that data as several several columns (one for each ColumnNumber value). When your Access skills increase, you might be very glad that you used a small number of fields because it makes the querying of the data simpler to maintain, in my opinion.\nAs for VLOOKUP, you can research "query table joins" and see how a query in design view lets you choose the field which 2 tables have in common. One table is really the excel spreadsheet presented as if it is a table; the other table is the real access table.
426878
write a vb6 procedure open a new recordset using your ado connection and write the sql statemtn to select the data from the 2 tables that you want. then save this into a module and then call it from your data report.
283150
Here's a code snippet that you should be able to paste into a form module and give it a try ... there are obviously many ways to tackle the issue ... this is just one of them.  To use this code you'll need a reference in your VB6 project to a Microsoft ActiveX Data Access Library (like 2.6).  Good luck!\n\nDim rs As Recordset\nDim cn As Connection\n\nDim DBPath As String\nDBPath = App.Path & "&#92;customer.mdb"\nSet cn = New Connection\ncn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath\n\nSet rs = New Recordset\nrs.CursorLocation = adUseClient\nrs.Open "SELECT * FROM tblCustomers WHERE CustomerID=" & 1, _\n    cn, adOpenStatic, adLockOptimistic
61405
Convert the parameters in your "where" clause to the same case, upper or lower.\n\nselect * from tbl \nwhere upper(fielda) = upper(searchparameter)\n\nI've not worked with Access (.mdb) dbs much, but there may also be an Access configuration to disregard Case.  (At least that is an option in MS Sql Server.)
967929
Best way is to have 2 tables.  A header and details table.\n\nThe header table will have one row per sale.  Key field would be invoice number.\n\nThe detail table will have details of each sale.  One row per item.  Key field would be invoice number and item number\n\nJoin field would be invoice number.
1183198
1. Simply open up the database.\n\n2. Make sure that you're looking at the database window (F-11)\n\n3. Hit file / Get External Data / Import\n\nA wizard will open up and you can select the other database that you want to import.\n\nThis works well, but does not import database properties, and you might have some trouble if you have different version of MS Access databases
578192
answer is here http://www.access-programmers.co.uk/forums/archive/index.php/t-30557.html
1333
Sorry in advance if Yahoo eats my indentation, but I think you are looking for something like this (tested under Access 2000):\n\n\nPublic Sub FiddlingWithQueryObjectsViaVBA()\n\n    Dim dbs As DAO.Database\n    Set dbs = CurrentDb\n    Dim qrydef As QueryDef\n    \n    Dim sSQLstring As String\n    Dim sQueryNames As String\n    \n    For Each qrydef In dbs.QueryDefs    ' iterate through all the Access query objects\n    \n        sSQLstring = UCase$(qrydef.SQL) ' Get the SQL for the current query\n        \n        If InStr(sSQLstring, "SOMETHING_I_AM_LOOKING_FOR") > 0 Then   ' I found what I'm looking for?\n        \n            sSQLstring = Replace$(sSQLstring, "SOMETHING_I_AM_LOOKING_FOR", "REPLACEMENT_TEXT") ' replace the text\n            qrydef.SQL = sSQLstring     ' update the query object with the new SQL\n        \n            sQueryNames = sQueryNames & qrydef.Name & vbCrLf    ' Maybe make a list of queries modified?\n        End If\n    \n    Next\n    \n    MsgBox "Modified the following queries:" & vbCrLf & sQueryNames\n    \nEnd Sub
459913
Excel has that limitation.\n\nLook into using MS Access instead.
852680
Well don't know the version of access you are using...\n\nFor Access 2003\nhttp://office.microsoft.com/en-us/access/HP051874901033.aspx
974290
I don't know of any tools to allow you to work the same way you do in Access, but if you want to do your queries in SQL, see your tables, etc, there are some tools available.\n\nYou have Entreprise Manager and Query Analyser for MS SQL Server (at least in version 2000, the one I use), for MySQL you have Control Center and Query Browser. \n\nCheck this link on query browser:\nhttp://www.databasejournal.com/features/mysql/article.php/3459931\n\nand this one on Control Center:\nhttp://www.lesbell.com.au/Home.nsf/438ebfdb9993616d4a2565d0001a90a1/2c5183e8974ccef8ca256e2900250697?OpenDocument
1054967
certain databases like mysql achieves this by using the dot operator "."\nsay db1 has table1(field1) and\ndb2 has table2(field2)then \n\nselect a.*,b.* from db1.table1 a,db2.table2 b where\na.field1 = b.field2
1012904
I would suggest keep it simple.  You need a plain database ... use Microsoft Access, or even Excel.\n\nIf Excel, you need to be careful while structuring and entering data.  Since Excel is freeform (flexible), some discipline is required for good results.
1102824
I think a pivot table will do you what you are asking.  Follow the source link provided for a tutorial to see if it will get you what you want.