Hello! i have created three tables and inserted different records in them. I want only "genes" records from table 1,2 and 3 to be shown. The problem I am having is that when I design the query and ran it, it shows me all the possible combinations of records instead of showing only 1 record.
Table 1 - Number - Population - Genes
Table 2 - Number - Genes
Table 3 - Number - Genes
Table 1 - Records
==============
Number 1 - India - AB48
Number 1 - Pakistan - AB35
Number 1 - South korea - AB48
Table 2 - Records
=============
Number 1 - BD34
Number 1 - GF45
Table 3 - Records
=============
Number 1 - HG65
Number 1 - LK98
SQL
=====
SELECT Table1.Number, Table1.Population, Table1.genes AS Table1_genes, Table2.genes AS Table2_genes, Table3.genes AS Table3_genes
FROM Table3, Table2, Table1;
Now the question is that either I need to use Criteria or Filter to show the each record from the table. The problem I am having is that when I run this query it shows me all the possible combinations of each record from the table.
I want to show only the records which are in those fields and nothing more or less.
Could you please help.
SIZE="1"][Please note: The database I have shown here is an example and does not reflect my personal database however I have made an example to show what i am trying to achieve here][/SIZE]
I have been working on a Query to sort through a list of companies. I am trying to query the company name to include ALL entries that have the include key(from another table) anywhere in their company name... It is supposed to exclude all entries with ONLY the exclude key(from another table)... If it has the exclude key AND the include key or neither key then the entry should be included... Then it is supposed to use all of the entries added from the previous sorts and include ONLY the entries where the zipcode matches one from a ZIPCode list.
Below I have listed the tables with their variables and my attempt on the query:
Include Table (tblinc) ID Inckey
Exclude Table (tblex) ID exkey
List Table (list) ID Company Address City State ZIPCode …
ZIPCode Table (ZIPCode) ID ZIPCode
Query withZips <--- my attempt... doesnt work 100% ALOT OF REPETITIVE ENTRIES
SELECT DISTINCT tblinc.inckey, tblex.exkey, ZIPCode.ZIPCode, * FROM ((list LEFT JOIN tblinc ON list.company Like "* " & tblinc.inckey & " *") LEFT JOIN tblex ON list.company Like "* " & tblex.exkey & " *") INNER JOIN ZIPCode ON list.ZipCode Like "*" & ZIPCode.ZIPCode & "*" ORDER BY inckey DESC , exkey;
This has become urgent... If you can help, please respond asap...
I am needing a database that will help to manage employee leave (holiday) requests. I have one but the current design is a bit...... well, you know.
Leave Limits are (presently) applied by one table:- tblLimit ------- payWeek workType (twentysix work types some common to all sites, some apply only to one or two sites) LimitSiteA (three sites) LimitSiteB LimitSiteC LimitAllSites
So, several "faults" are immediately apparent:
LimitAllSites is ALWAYS the sum of LimitSiteA+LimitSiteB+LimitSiteC so clearly shouldn't be there, and I don't even want to talk about that....
Also , no primary key, repeating columns... uh-oh! Therfore.... tblLimit ------- limitID (PK) payWeek (date/time - always a Wednesday, our payWeek begins on a Wednesday) workTypeID (FK to new table tblWorkType) siteID (FK to new table tblSite) Limit (integer)
However....with just a brief investigation, it transpires that of the 1600+ rows in this (current version) table, there are only 69 unique sets of limits (ran a Unique Values query, excluding the PayWeek).
So, what I do want to talk about is not having to add in a table row for each WorkType for each PayWeek (for each site- with the improved design). I want to apply a general set of limits, with effectiveFrom and effectiveTo dates, per Site/workType combination, which should automatically populate out 80 weeks into the future (historical records are not required - it is just to help plan future leave) and then cater for the exceptions by "adjusting" the general limits in one of two ways: 1) every day over a date range (e.g. every day between 15NOV05 and 15DEC05, decrease the limit for worktype Z in Site A by 10 percent of the BASE* limit). 2) every nominated weekday over a date range (e.g. every Friday in December 2005, increase the limit for worktype Z in Site A by 20 percent of the BASE limit).
*the two adjustment types need to be independent, overlappable, and have a cumulative effect.
e.g. for the above, if the general limit for type Z of site A were 100 14 NOV --> limit 100 = 100 (no adjustment) 15 NOV --> limit 100-10 = 90 (adjustment per #1) . . - repeat concept as above . 30 NOV --> limit 100-10 = 90 (adjustment per #1) 01 DEC --> limit 100-10 = 90 (adjustment per #1) 02 DEC --> (Friday) --> limit 100-10+20 = 110 (adjustment per #1 & #2) 03 DEC --> limit 100-10 = 90 (adjustment per #1) . . - repeat concept as above . 15 DEC --> limit 100-10 = 90 (adjustment per #1) 16 DEC --> (Friday) --> limit 100+20 = 120 (adjustment per #2) 17 DEC --> limit 100 = 100 (no adjustment) etc
It gets dirtier ! There are two broad types of leave - general - for which the limit is determined/applied at the (site+workType) level, and "long service" for which the limit is to be applied at just the site level (workType has no bearing).
I was OK up until it got "dirtier" - now I am not so sure....
I was thinking...
tblLimit (base limits) ------- limitID (PK) workTypeID (FK to new table tblWorkType) siteID (FK to new table tblSite) limit (integer) effectiveFrom (date/time) effectiveTo (date/time)
Does my base limit table store WorkTypeID, but as null when the record relates to "long service" leave, and as a valid WorkTypeID when the record relates to "general" leave ? If so, is it of sound design to derive the broad leave type from the "nullness" of the workTypeID, or is it recommended to store that separately?
Similar questions relate to the adjustment table, for both WorkTypeID and WeekdayID...
I have been working on a Query to sort through a list of companies. I am trying to query the company name to include ALL entries that have the include key(from another table) anywhere in their company name... It is supposed to exclude all entries with ONLY the exclude key(from another table)... If it has the exclude key AND the include key or neither key then the entry should be included... Then it is supposed to use all of the entries added from the previous sorts and include ONLY the entries where the zipcode matches one from a ZIPCode list.
Below I have listed the tables with their variables and my attempt on the query:
Include Table (tblinc) ID Inckey
Exclude Table (tblex) ID exkey
List Table (list) ID Company Address City State ZIPCode …
ZIPCode Table (ZIPCode) ID ZIPCode
Query withZips <--- my attempt... doesnt work 100% ALOT OF REPETITIVE ENTRIES
SELECT DISTINCT tblinc.inckey, tblex.exkey, ZIPCode.ZIPCode, * FROM ((list LEFT JOIN tblinc ON list.company Like "* " & tblinc.inckey & " *") LEFT JOIN tblex ON list.company Like "* " & tblex.exkey & " *") INNER JOIN ZIPCode ON list.ZipCode Like "*" & ZIPCode.ZIPCode & "*" ORDER BY inckey DESC , exkey;
This has become urgent... If you can help, please respond asap...
In an Access 2010 form is it possible to export select records and fields in those records to a specific location?
Code: Set objDialog = Application.FileDialog(4) With objDialog .AllowMultiSelect = False .Title = "Please select a File" .InitialFilename = "C:" .Show If .SelectedItems.Count = 0 Then MsgBox ("Action Cancelled") Else
[code]....
The user can select the directory using the code above, but can specific fields in records be exported to a excel workbook in that selected directory?For example, if the are 5 records in the database can the fields LastName,FirstName,BirthDate in records 1,2,3 be exported to Setup.xlsx in that selected directory?
I have a table (tbl Team Info) which contains names and codes for teams within my business (>400 records) and another table (tbl Process) which contains a list of high level tasks (30 records).
I need to create something where for each team name 9in tbl Team Info) I can map them to the tasks that they undertake (in tbl Process) and assign a percentage of time then spend on each task. Each team could map to several different tasks.
In my simple database (attached), I need to mass duplicate Tasks and their Notes.
I have three tables: tbTasks (PK: Task_ID), tbNotes (PK: Note_ID), jtbTaskNotes (FKs: Task_ID and Note_ID). jtbTaskNotes is my many-to-many junction table that ties Tasks to Notes.
The main form (fmTasks), bound to tbTasks, has a subform (sbfm_TaskNotes) that displays notes associated with each Task. On themain form,you select which Tasks you want duplicated via a checkbox. The append query (quCopyTasks) will duplicate all tasks that have the checkbox checked. All good there. However, I can't figure out how to also duplicate each task's Notes.
I found Allen Browne's solution [URL] ....., but that only handles duplication of one record at a time, whereas I need to duplicate many records at a time (sometimes 10+ records). How do I go about duplicating multiple Tasks and their associated Notes?
Before you ask "why are you duplicating records?": There are times when tasks need to be re-accomplished and therefore need to have a new record. It's easier to duplicate records than it is to hand-jam everything again.
One shows my form with the Transporters Subform with 3 entries, and 1 entry.The three line items that say "Transporter" are in one subform. I used this code
Code: Private Sub Form_Current() If Me.RecordsetClone.RecordCount >= 3 Then Me.AllowAdditions = False End If End Sub
to limit the number of records I can add to 3 or less.My issue is that I lost the blank text box that allows you to add another record. So, if I only have one Transporter listed, there's no box to let me add a second or third.I have the following properties for the Transporters Subform set to "Yes":
Data Entry Allow Additions Allow Deletions Allow Edits Allow Filters
I have built a qry that initially shows the correct information. For example.
tblContent has 289 records with a Type = Class.
I built a Query to select from tblContent Type = Class and I get 289 records. I add additional criteria of Progress <>"Not Scheduled", I then get 206 records. I then add additional criteria Last Name <>"Demo" And <>"Care" And <>"Support". This brings up 200 records, but the query appears to duplicate each record 3 times. I do not have 3 of the same types of records.
The SQL Statement is below
SELECT tblProfile.LoginName, tblProfile.FirstName, tblProfile.LastName, tblProfile.Organization, tblProfile.CostCenter, tblContent.Title, tblContent.Type, tblContent.Code, tblContent.[Date Assigned], tblContent.[Date Started], tblContent.[Last Accessed], tblContent.Progress, tblContent.[Date Completed] FROM tblProfile INNER JOIN tblContent ON tblProfile.LoginName = tblContent.LoginName WHERE (((tblProfile.LastName)<>"Demo" And (tblProfile.LastName)<>"Care" And (tblProfile.LastName)<>"Support") AND ((tblContent.Type)="Class") AND ((tblContent.Progress)<>"Not Scheduled"));
The qry is named qryPhysical Class. I have provided the link to view the database. Can you help me?
All seemed to be working well, however, I noticed that all my subtable records in the database are exporting with each Primary table record. In my output, I'm looking to see each primary table record followed by one or more subtable records from a one to many relationship.
(Office 2010) Access/Word
Private Sub cmdPrint1_Click() Dim objWord As Word.Application Dim docm As Word.Document Dim db As DAO.Database Dim rstLandSales As DAO.Recordset
Dim dbs As DAO.Database, sql As String, rCount As Integer Set dbs = CurrentDb sql = "DELETE * dbo_InvPrice Inner Join (dbo_InvPrice Inner Join UpdatedPricing on dbo_InvPrice.StockCode = UpdatedPricing.StockCode ) ON on dbo_INvPrice.PriceCode = UpdatedPricing.PriceCode " dbs.Execute sql, dbFailOnError
I have a form which needs update ever month. When the form is opened, the end user can see the old records and data, and also a new record is added for any new data. I want to protect the old saved data and the user can only add, edit, or delete the new data in the newly added record. The problem is once a user adds the new data and moves to another record or another form, then he/she cannot edit or change the new data in case if there is any mistake or need to change something after couple of minutes.
I changed the Form Data Properties "Allow Additions", "Allow Edits", and "Allow Deletions" many different times and situations to solve this problem but with no success. I tried the following with NO success too: One of the Fields of the Record is (Month). In the Data Properties, I set a Default Value for this field as(December 2014) for instance. I set the Data Properties "Allow Additions", "Allow Edits", and "Allow Deletions" to (Yes). Then I put the following code in the Form's Current Event:
Private Sub Form_Current() If Me.month.Value = "December 2014" Then Me.AllowAdditions = True Me.AllowEdits = True Me.AllowDeletions = True
So looking at the tables, C1 = 2 C2 = 4+6 = 10 B2 = C1 + C2 + D4 = 20 A1 = 20 + 10 = 30
Here, there are 4 levels that I have to go into to get the grand total. If I did not know how many levels there were, how would I be able to do this through code? through queries if possible??
If anyone could help, that would be great! Thank you in advance.
I want to find duplicate records based on FirstName and LastName and delete the duplicate. Also, I want to delete any records which have a blank FirstName and LastName.
I have form setup with two cascading combo boxes (facility and date) to select a record (other navigation, including mousewheel, is disabled). There is a button to open another form which allows addition/deletion of records by facility and date. When this form is closed, the combo boxes update accordingly on the initial form. However, if I select a new record whose facility/date I have just entered, it seemingly shows the wrong values for several of the other fields. If I check the corresponding table, the values are actually at default, though, and when I reopen the form they have reverted to default. Any ideas on how to solve this? I'm tempted to simply have the first form close and reopen when the second is closed, but couldn't figure out the syntax to close a form with a button on a different form. Let me know if there's any more information I need to provide. Thanks for any suggestions.
I have a mainform with 4 fields which are marked with 4 primary keys: soldtoId, shiptoID, PlantId and formulaID. PlantID is a long integer number field and the other 3 are text fields. there is no default values.
I have a subform1 that is linked by PlantID and FormulaID. These 2 field names are in the linked child fields.
I had a handful of records established already. Today, i added a command in the after update section of the PlantID field in mainform by adding Me.Refresh. So when i change the plantID in the mainform, the subform gathers different plantID and formulaID data automatically. works nicely.
Here is the rub. I next tried adding NEW records to the mainform. that's the one with 4 primary fields.
i enter soldtoID and shiptoID fine. they have no refresh need. however, after the enter a PlantID, the me.refresh kicks in before i can get the the formulaID field. i get a debug error. something about a NULL. which i suspect is the formulaID that i didn't get a chance to enter to. FYI, later, i want to put a refresh on formulaID too.
so how can i make some kind of Refresh work for both New and existing records?? penwood
I have a user login code am using for users to login and only access their records in a subform according to their user ID and access level (user or admin). On the main form I have a user name field, the password field and two other fields: one to hold userID and the other to hold access level.
I also want if the person logged in has "Admin" access level the subform should return records from all users. For now what is happening is that even when I assign myself with "Admin" access level, the subform is not returning all records from other users, but my own records only. The original demo where I found the code worked well but I cannot find it and I am totally lost on this.
Here is the code I have so far:
Code: Private Sub cmdLogin_Click() Me.Hold_User_ID = Nz(DLookup("User_ID", "T_Users", "Username='" & Me.UserName & "' and pword='" & Me.PWD & "'"), -1) ' check to see if we have a good login If Me.Hold_User_ID = -1 Then MsgBox "Invalid username or password."
I have a database with a table with company names, then a relationship to another table that shows that companies' address, but I also want there to be an address 2 and 3 and so on, and some of our companies have multiple sites.So what Im asking is how would you be able to show multiple data, on a sing record.
I'm not sure what I did, but when I enter data into a form, say it's record 10 and I click record 11 to start a new record. I can then go back to record 10, and it's still has the data I entered in the form. As you know this part is normal.
The problem is when I save and close the form and reopen it, record 10 now has the data from record 9, and the data I had entered for record 10 is moved to record 1, which is in front of my other records. It continues to do this, and I am now at record 4, which should be record 14 and I do not understand why it's doing this. At first I thought it was deleting my new records until I found them in the front of my already created records.
I could just leave it, since all my records are still there, but it's frustrating expecting your last record you enter to be at the end, instead to find it somewhere else.
I'm still learning, so I hope it is just a setting or entry I need to change.
I currently have a database that I had to copy over each field from another databse to create the one i have now. When i copy the records from one field over it appears they all move up three spaces for some reason is there a equation i can enter to make them all move down three records in the table?
How can I get a Query Criteria To Select All Records or specific records in query design section.
I have a table that shows many departments with credit card transactions. I like to run a query to see specific department, or have an option to see all the departments when the query is run.
I am looking for a query that will return records from a table that have related records in another table. Opposite to the Unmatched Query Wizard.I have two tables: tblSupplier and tblSupplierProducts.The two tables are related by the field "SupplierId".I need the query to only return Suppliers that have Products.
I have a survey database that I've been using for the last year for monthly auditing of employees files. I need to be able to get monthly audit scores for each employee but grouped by their manager. The problem I'm having is employees have moved between managers throughout the year, so employees that are listed under Manager 2 now were actually working for Manager 1 when the audits occurred.
ie. Audits occurred Jan - April for Employee 1 while they were assigned to Manager 1. Employee 1 moved to Manager 2s team in May. So when running monthly reports for the year Employee 1 audits should fall under Manager 1 for Jan-April and Manager 2 for May-Dec.
I do have a history table set up like: tblEmployeeHistory ID (PK) EmployeeID (FK to Employee table) ManagerID (FK to Manager table) MoveDate (date employee assigned to manager)
The Employee table is set up like: Employee ID (PK) EmployeeName ManagerID (FK to Manager table)
The Manager table has the ManagerID and ManagerName.
That's the employee side of things; then I have the tables that store the audit results:
tblAudit AuditID FileNumber AuditDate EmployeeID (FK to employee table)
tblAuditResults AuditID; QstnID (Composite PK, QstnID is FK to tblQuestions) Answer
How can I use AuditDate and MoveDate to relate audits to the managers the employees were under when the audits occurred?
After a bunch of messing around with my database I found out that it seems to be a table link issue. When opening a form (frmAddDevices) it is supposed to go to a new record. I messed with the code and the allow additions and all that stuff. After a while I realize if I am opening the table in the front end part of the database I cannot add any new records or edit any records. One the backend of the database the table works as it should.
