In access I have two tabels: both contains 2 series of equivalent data sets and each tabel have different values for this series.
With equivalent data I mean DATE and TIME which are in different columns so when I link this sets with second table and run a query I get all values which have same date and also all values which have same time.
The thing is that I would only like to get all values from one table and values with the same time and date from another table.
Is there a chance to connect or merge DATE and TIME? Or is my problem just a matter of settings? I`ve tried all relationsheep options and also set query to unique values but still don`t get the correct outcome.
One more thing DATE values are in number format.
I got two tables, one with custumers and one with resellers. Both got almost same fields and I would like to put them into one table. First I know I have to fix fieldnames so they got same fieldnames and I have to add on field for info if it's a reseller or a custumer. I also have to add one field with new counter.
But my question are, how to put those two tables together after those preperations. I also would like to update those referensnumers in other tables.
Sorry for my bad explination but I hope someone can help me.
I've made 2 tabels tblIn and tblOut, Now I want to make a query which get the differences between the number of product A in tblIn and the number of product A in tblOut the same for product B,.... It's possible that there is nothing in tblOut from product X so the query just has to show the number of tblIn of product X, I've tried a few things but i can't make the query can anybody help me???
I have a database with a number of linked tables that are linked to tables in different databases (not a back-end).for example, I have table1 that is linked to table1 in K:databasedb1.mdb.table2 linked to table2 in S:datadata.mdb.and so on...
However, recently we have moved all our databases to a new location.
K:databasedb1.mdb is now residing in O:masterdatabase and S:datadata.mdb is now residing in O:masterdata and so on...
I'm now in charge of relinking all those tables to point to the new location.I would do this in linked table manager one by one but we have 100s of tables linked to multiple different databases in different location.is there a way to create a VBA code that will automatically do this re-linking process?
so, 1. find unlinkable tables 2. search its new location under O:master 3. re-link it to the new location
Database names and tables names have not been changed. Just the location of databases.
I'm trying to write a query in Access 2003 based on two 'linked' SQL tables (from different databases). The problem I'm having is that I can not link directly on any one field.
The first table holds values between 4 and 9 chars and is alphanumeric. The second table contains this same value but with up to 3 additional chars.
I've tried the following join to try and get the first x number of chars from the second table based on the length of the field of the first table.
Obviously this isn't working (otherwise I wouldn't be asking for your expert help). Is there any way I can join these two tables based on these fields where 95% of the records don't directly link up?
I am trying to create a query based on two tables with a many to many relationship.
The basic sketch of this relationship is one table has our employee information and the other has the phone information. Employees share some phone numbers so it has to be many to many and I have an employee_phone linking table.
When I create a query, I have no records in my phone or my employee_phone tables, but my employee table has a full list of employees. I want to use the query to enter the phone numbers in for existing employees, however when I put in the employee's name and then add the phone information, it creates a new record in the employee field. I just want it to tie new phone information to existing employee records.
If anyone can help me with this, I would be very greatful! Thanks
Just wondering, is it possible to have an excel file, which is a export data from an Access db query, and whenever I open this excel file, it'll automaticallly run the query and get the most updated data? If so, What do I need to do in order to link the excel file to the query?
I am trying to generate a query from a series of linked tables in a "linear" arrangement. My central table is an employee table. I then have two branches for phone data and office data, each in a many to many relationship with the employee table, so I have a linking table between.
I am trying to generate a directory that lists for each employee their phone number and their office number. Whenever I try to generate my queries however, I get each phone record for each office record. If I have 4 phones and 2 offices for an employee, I then get 8 rows in the query. Is my table setup just awful (my attempt at normalizing my data as many offices and phones are shared) or is there a clever way to generate a query I haven't yet discovered. Any help would be great!!! (lest I start )
I have been searching the forum for this scenario but could not find similar case, which is as follows: I have a Search Form with many Text and Combo boxes, linked to search criteria in a query. There is also a sub-form showing the search results. I am trying to replace one of the combo boxes on the form, with a List Box, so I can make multiple selections. For example instead of showing only "buy" transactions via the combo, I want to see both "buy" and "sell" transactions, which I believe could be done via a List Box.
For the combo box I have the following: In the Field section I have: [TransactionType]=[Forms]![frmSearchTransHist]![cboTransType] Or [Forms]![frmSearchTransHist]![cboTransType] Is Null
For Criteria I have - "True"
So, my question is what should I put on the query in order to make it work with a list box.
I also want to give credit here to Jon K who has an example of database with combos and texts - very helpful example! http://www.access-programmers.co.uk/forums/showthread.php?t=103312
Hiya, I currently have a query that looks up staff names and then if they click a staff name from the combo box on a form they are then able to preview a report showing their performance. I want the combo box to have the ability to be left blank or the option to select all so that I can preview all their performance in the report.
I have a delete query where i want to delete only the row that contains the max value of the IDnum field from the table STM, where it links two tables on CellTell
I currently have this:
DELETE DISTINCTROW STM.*, STM.IDNum FROM dpl_00c_tbl_StmCellDups INNER JOIN STM ON dpl_00c_tbl_StmCellDups.STM_CellTel = STM.CellTel WHERE (((STM.IDNum)=(select max(IDNum) from `STM`)));
It doesn't want to throw out any values when i run it, or view it.
I have attached an image of the 2 tables concerning my question.
The main "transaction" table is the tblAssessments and a linked table tblRisks drives a subform - showing multiple Risks per assessment.
The field "OccupantID" identifies the facility where Assessments are done (there are other tables, of course).
I am using an OccupantID "00000" to store templates - pre-filled assessments with most common options selected.
I am trying to develop code and/or query or SQL that would do this:
copy all templates (records from OccupantID 00000) and corresponding sub-records from tblRisks into the same tables, but under a different (selected by user) OccupantID.
I have no problem just using an Append Query (actually a SQL statement in VBA with variable parameters), but that only lets me copy into 1 table - so I can copy just the tblAssessments records.
but how do I then copy the tblRisks related records and make sure I attach them to the correct AssessmentID?
I am a relative newby to Access, Can someone please offer a solution to my problem.
I have a DB which lists current order references, I also have a linked spreadsheet from a customer which references their Ref number to our S/order no & Item no. I have set up the relationships for the common data between both tables.
if I create a Query from either table individually the data can be edited but when a query is created from both sources the data cannot be edited. this also happens if the spreadsheet is imported to its own DB.
I would really like to create a form which displays both sets of data for editing, marking progress etc.
I've been looking around and have found some posts that pointed me in the right direction, I just can't get it to work. What I have is 37 excel files of competitor cross references. There are 2 columns in each excel file, our number and their number. I have inserted them as a linked table in the db. What I want to do is create a form that has a field for every part number and make all of those fields a search field. That way they can type in any number and get all numbers back. I have created a query but once I get past 4 linked excel sheets then I get errors about a type mismatch in expression. Also I can't get the form fields referencing the query to show up when I open it. If I leave the query at 2 or 3 fields and use [Forms]![CrossRef]![txtItemNumber] in the criteria of our number in the query, it works. I get a window that pops up when I just open the query asking for a number, I type it in and the query returns the number and competitor numbers. Am I doing this the hard way?? Thanks for any ideas...
Please can you help me find the issue to my problem that is probably hitting me in the face.
I have an unbound form to which i use an append query to post the data from the fields on the form to a table. The table is set up very simple and has a primary key which i will never duplicate as you would expect.
Once i have inputted one lot of data into the form the command button runs the query and the data is posted to the table fine. The problem i am having is that the second lot of information i go to append the query refuses to add. I get the validation rule violations error and it won't post the data.
If i close out of the form, open it again and input the same information the query adds it fine and again the second lot you get the error message.
i had this problem before but for the love of me cannot remember how i resolved it. I have had to keep closing the form after each entry to post the information.
Please could you advise me if tables have to be linked in order to perform a query on multiple tables? For example searching for a date and matching records on more than one table at the same time.
I am using Office 2007 and have a form that is linked to a query, also I have a sub form linked to the same query. I cant understand why both forms say in their properties that they are linked to a query but when I try and add a text box to show customer ID it tries to show data from a table
Can we have two tables linked to one search query? I've some data that is confidential and woul like to have them in two different table. When I search for it using query form, only the relevant table results will be shown?
Table1 has a record of unique tasks. Table2 has a list of people to do those tasks
They are linked by a Unique_ID (one to many - Table1 to Table2).Sometimes there can be an instance where there are Unique_ID's in Table2 but the reference Unique_ID is missing from Table1.
I have a select query that can show me these like so.
table2 table1 Unique_ID Unique_ID Like * Not Like *
I can open the query and simply delete the records fine however i wish to automate this.What I want to do is delete the unrefenced Unique_ID's from table2.I have tried to just convert this select query into a delete query but it asks me which table I wish to delete and I cannot see where to enter this information.
I have a make table query which creates the new table on the local front end.I have now split the DB, so need to know how I can get this query to make the table on the BE i.e. how do i link it.
I have a number of linked tables that vary depending on use. How to delete them or drop them all from the database? I produced/stole some code that deleted all tables - but it didn't delete the linked ones.
Hi everybody. I got a query that displays object name and object type of access 2000 db. Unfortuenly it dose not display the linked tables objects(linked to tables in acccess 2000 db). could an expert tell me how i can fix this query so it displays linked tables object as well.Thanks
SELECT MsysObjects.Name AS ObjectName, IIf([type]=1 Or [type]=6,"Table","Query") AS ObjectType FROM MsysObjects WHERE (((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND ((MsysObjects.Type)=1 Or (MsysObjects.Type)=5 Or (MsysObjects.Type)=6) AND ((MsysObjects.Flags)=2097152 Or (MsysObjects.Flags)=128 Or (MsysObjects.Flags)=0 Or (MsysObjects.Flags)=16)) ORDER BY MsysObjects.Name;
I use an Excel interface to retrieve data from an Access file that has approximately 1 million records. I use an MS Query from Excel to run an Access Query to retrieve the data.
I have 16 "Criteria" fields (columns) in Excel that I change with drop-downs. Each criteria column has 2 to 9 values set; a total of 75 values. I pass these criteria fields to Access via a table link in Access.
The criteria of the fields in the query that is run in Access to pull the data based on the table link is updated by using the Values In A List (IN) option.
When I refresh this query in Excel, it pulls the data fine, but may take 10 minutes to run. (I am using MS Office 2003, 1Gig memory). I have limited VBA knowledge. Is there a better/faster way to pull this data?
1. I have linked a table which does not allow me to update any entries. 2. In this linked table I have to update one column that contains all null values 3. I have another table that contains the values required for that one column in the linked table
When I try running an update query after doing an Inner Join, i get the Operation must use an updateable query error...is there ANY work around at all ? Please let me know . any help would be greatly appreciated.
Hi everybody. I got a query that displays object name and object type of access 2000 db. Unfortuenly it dose not display the linked tables objects(linked to tables in acccess 2000 db). could an expert tell me how i can fix this query so it displays linked tables object as well.Thanks
Code:SELECT MsysObjects.Name AS ObjectName, IIf([type]=1 Or [type]=6,"Table","Query") AS ObjectTypeFROM MsysObjectsWHERE (((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND ((MsysObjects.Type)=1 Or (MsysObjects.Type)=5 Or (MsysObjects.Type)=6) AND ((MsysObjects.Flags)=2097152 Or (MsysObjects.Flags)=128 Or (MsysObjects.Flags)=0 Or (MsysObjects.Flags)=16))ORDER BY MsysObjects.Name;
