Lots Of Repetitive Entries... What Am I Doing Wrong?
Sep 24, 2007
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...
View Replies
ADVERTISEMENT
Sep 24, 2007
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...
View 9 Replies
View Related
Sep 25, 2006
Please be patient with me.. really a beginner here. Thanks. :)
Here is my question:
I have multiple tableS (and by multiple, I mean a LOT of tables.) They are all similarly designed, with mailing addresses of potential customers. They were all entered at different locations and different timing. What I want to do is to take out all customers from a particular country at one go. The simple and tedious way is of course to merge ALL the tables then run a query. But is there a simpler way?
Somebody wrote me a SQL thingy, that helps me remove all the duplicates, so that is not a problem. But the merging of the tables is too tedious considering the number of tables involved. I myself have around zero knowledge of SQL, but if the solution is in that direction, please let me know, and I will find someone who can help figure it out.
THANKS!
View 6 Replies
View Related
Nov 25, 2007
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]
Many Thanks
Jay
View 4 Replies
View Related
Oct 11, 2005
Hoping for some assurance and assistance....
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...
tblLimitAdjust
-------------
limitAdjustID (PK)
workTypeID (FK)
siteID (FK)
adjustFactor (double)
WeekdayID (FK)
effectiveFrom (date/time)
effectiveTo (date/time)
I was sort of hoping that writing this out may help clarify my mind on some of the points.... which it did, but only to a limited extent so....
Suggestions / corrections / Pointers ???
Regards
John.
View 2 Replies
View Related
Nov 21, 2006
I have a table similar to this
table
-----
ID
field1
field2
field3
'ID' is the primary key but I want MS-Access to have an eye on field1-3 to avoid entering the same information into field1-3.
How can I do this?
(Using field1-field3 as primary keys is not desirable)
Please help.
Thanks in advance.
xxyy
View 4 Replies
View Related
Dec 15, 2006
In this database, there is a table of users (name, address, phone, etc.) There is also a table of 35 groups. Each user may be in any combination of 1 or all of the groups. The list of groups is not static. At any point in time, I need to be able to show that Bob is in groups 1, 19 and 35. All I can think to do is list all 35 groups in the user table, then make each one a yes or no field. HELP! There’s got to be a better, more efficient way.
View 3 Replies
View Related
Nov 9, 2005
I only have one table and I read so often that its better to have multiple tables. For what I have, it seems to work fine.
Will this cause problems down the road?
Thank you once again
John D
View 14 Replies
View Related
Dec 20, 2006
All,
I have a search form (continuous) which filters results from the main table. I can then double click on one of the filtered results to open a new form (single) showing all the fields for that entry. Fine so far.
My question - There are ten different types of operation that can be displayed, all with slightly different relevant fields.
Which of these methods is more efficient:
1. Make ten custom made forms (the correct one displayed depending on the selected operation). - Easy to do but needs ten forms.
or
2. Have one form with all the fields added (only the correct fields made visible by code). - Complicated code.
What do you think ?
View 2 Replies
View Related
Jan 29, 2005
I have an input form that has many fields, how do I go about fragmenting the data collection.
I thought I might group related data fileds together ie personal details, contact details and have a separate form for each data grouping or one form and make the fields invisible after they are filled in and then make the new data group fileds visible ie overlaying them so it appears to be a new form. I am not sure and would appreciate guidance.
regards
Peter
View 3 Replies
View Related
Apr 22, 2014
I'm trying to find the best way to organize a report that will have ALOT of fields for an individual record. Because it is for an individual record not a group of records (and I want it to be dynamic) I'm thinking of doing a filter & query to fill in report with necessary details. This report is going to contain ALOT of fields, even the wizard that I used based on the query said that there were too many fields, but I can't cut down.
View 4 Replies
View Related
Jan 9, 2007
Hi,
I wonder if someone can help with what must be an easy solution to this problem.
I have a table with 350,000 records, but without a unique identifier field. I just want a simple incrementing number field to become the primary key but dont know how to add this in. I cannot add an autonumber as there are two many records - it falls over.
Ive tried exporting it all to excel, but as well as being cumbersome, something went wrong and so I'm back to square one.
I think there must be a way to add an incrementing number either via a query or in VBA but I dont know how. Please can somebody provide a solution.
Thanks,
Adam.
View 2 Replies
View Related
Mar 13, 2014
I have a table in access which is updated weekly; I need to create two tables from this updated table.
1st table will consist of all the new entries for the current week
2nd table will consist of all the entries from the previous week - an amalgamation of all the entries which are not from the "current week" (table) For example; the table below shows the two entries from last week.
ID
Name
1
Adam
2
Ben
This week I have three new entries New entries
ID
Name
3
Charles
4
Richard
So when I run the same query next week I will get something like this.
Old Entries
ID
Name
1
Adam
2
Ben
3
Charles
4
Richard
[code]....
How do I get a query /queries which divides up the weeks new entries and also all the old entries.
View 11 Replies
View Related
Sep 21, 2005
Ok Guys,
Here goes...
all of a sudden now, when i open my form, its not displaying any of the previous data that i stored in it!! All the data is still in the table, and when i add data it goes into the table!! I checked properties and the record source and everything is ok there, what have i done wrong guys...HELP!!!!
View 3 Replies
View Related
Jun 9, 2006
I purchased a copy of Office 2003, on Ebay, for less than half the price it normally sells for. I created databases, which work well on my computer. However, when I copy them over to the office computer from my computer, via Flash memory stick, the program does not run. The message seems to indicate a problem with registration. When I installed Access, it asked me for a registration key, which I did provide. After that it worked-not activation process. Does this mean that you can only use the computer you created the database on to run it, or that I do not have a proper version of Access 2003? I cannot transfer, from my home computer, to the office computer. Is this Microsoft paranoia again?
Subsequent uninstalls and reinstalls of the program did not ask for the installation key.
What should happen? I would like to resolve this major problem and your help will be greatly appreciated. The program is flaky. Also, Switchboard does not work-must use Option Box.
These questions most user must know about initial installation and copying mdb files to other computers -please reply.
View 2 Replies
View Related
Oct 16, 2006
Hi,
Can anyone tell me what's wrong with this:
DoCmd.RunSQL "INSERT INTO tblPersoaneJuridice (Denumire,Localitate,CodUnicInregistrare,NumarInre gistrareRegComertului,Telefon,Fax) VALUES ('" & Forms!frmPrincipal!subfrmTipVersus!txtDenumire & "','" & Forms!frmPrincipal!subfrmTipVersus!txtLocalitate & "','" & Forms!frmPrincipal!subfrmTipVersus!txtCUI & "','" & Forms!frmPrincipal!subfrmTipVersus!txtNumarInregis trare & "','" & Forms!frmPrincipal!subfrmTipVersus!txtTelefon & "','" & Forms!frmPrincipal!subfrmTipVersus!txtFax & "')"
cui = DLookup("[CodUnicInregistrare]", "[tblPersoaneJuridice]", "[NumarInregistrareRegComertului]='" & Me!subfrmTipVersus!txtNumarInregistrare & "'")
i have a string variable denoted by cui and i need it to take value from table tblPersoaneJuridice from field CodUnicInregistrare. CodUnicInregistrare and NumarInregistrareRegComertului is text.
View 3 Replies
View Related
Jun 14, 2007
Have not coded for about 5 years and need a little refresher. Can't remember what I need to do. Thanks
Dim SelectedCompany As String
Dim rs As ADODB.Recordset
Dim Cn As ADODB.Connection
Dim sSql As String
Set rs = New ADODB.Recordset
Set Cn = New ADODB.Connection
SelectedCompany = Me!cboCompanyName.Text
sSql = "select Customername, AddressLine1, AddressLine2, AddressLine3, AddressLine4, AddressLine5, AddressLine6 from customers where "
sSql = sSql & "CompanyName = '" & SelectedCompany & "'"
rs.Open sSql, Cn, adOpenStatic, adLockReadOnly
this last line fails with error "No value given for one or more required parameters."
The code is executed when someone selects something in a combo box on a form in an access database.
Thanks
Pete
View 4 Replies
View Related
Oct 4, 2007
I think I'm just tired, and not seeing what to do here.
I have my main table.
MainID PK
bunch of other fields...
I also have a 'created by table'.
Createdby_ID PK
User ID
Main ID
Date_created.
I also have a user table, with a list of all the users.
User_ID PK
User_Name.
Is this the right way to setup these tables?
Only 1 person can create any paticular record.
I want it to be required tha whoever creates the record selects their name and dates it.
View 11 Replies
View Related
Dec 13, 2005
strSQL = ("SELECT onespace2(MeetingMinutes!MinutesID, '.' ) & " - " & [MinutesTitle] AS Item, MeetingMinutes.MeetingCode, Minutes.*, MeetingMinutes.MinutesCode, MeetingMinutes.MinutesID ")
strSQL = strSQL & ("FROM Minutes INNER JOIN MeetingMinutes ON Minutes.MinutesCode = MeetingMinutes.MinutesCode ")
strSQL = strSQL & "WHERE MeetingMinutes.MinutesCode= " & MinutesCode & " AND MeetingMinutes.MeetingCode= " & [Forms]![MinutesMFnew]![ComboMeetingNav] & ";"
cant get it running.
i got the same query running made in the querybuilder, but somethings change, so i need to adjus it in vba. But it doenswork, and get a type error
please advice
View 1 Replies
View Related
Jul 13, 2007
DoCmd.RunSQL "INSERT INTO tblAccessLog " _
& "(DateLoggedOut, TimeLoggedOut) VALUES " _
& "(#" & Date & "#,#" & Time & "#) WHERE " _
& "(LogID = '" & LogsID & "');"
Could anyone possibly tell me why this isn't working? Probably something stupid i've done bus still.
When i run it, I am getting the error message "Query input must contain at least one table or query" yet i've told it to use tblAccessLog so i'm just confused.
Thanks
View 8 Replies
View Related
Aug 14, 2007
StrSQL = "INSERT INTO LV (Directory_Name:,Filename:, Total_Files,Total_Records:,Total_Batches:, Total_YH) VALUES ('" & DirectoryName & "', '" & FileName & "', TotalFiles , TotalRecords , TotalBatches , TotalYH);"
Have tried loads of combinations of the " ' and ' " to get it working but just keeps giving me the damn syntax error!!! :mad:
What's wrong with it? Thanks
View 2 Replies
View Related
Oct 11, 2007
strSQL = "INSERT INTO TBL_History (Filename, Agent, SentRecieved, Accepted, Notes) VALUES ('" & [Forms]!FRM_ImportSubmission.submission_filename & "','" & [Forms]![FRM_ImportSubmission]![cboAgent] & "','Recieved','True','');"
CurrentProject.Connection.Execute strSQL
i've posted it as the sql string is built in the code
i get a 'catastrpohic error' when running it from vb like this and when i build put the string into a query and run it through the access user interface it worrks
View 14 Replies
View Related
Oct 23, 2007
I made a number of changes to a DB that was working fine (normalised further). So what's wrong? I have a number of forms (for my sample DB I have stripped down to just one, if I can resolve for this I can apply to others).
qryENTRY_COMPUTERS
ENTRY COMPUTER FORM
The user adds a new computer then selects the Computer Make (qryLOOKUP_COMPUTER_SUPPLIER) & Computer Model (qryLOOKUP_COMPUTER_MODELS).
This now returns error 'recordset is not updateable'.
The query on which the form is based is incorrect (qryENTRY_COMPUTERS). Previously I was able to add tblSupplier & tblSupplierProducts to the query then add field ProductDescription fom tblSupplierProducts to the query. This allows users to select a value from cboComputerModelID (ProductID), field ProductDescription then displays the related descriptive value for the ProductID selected (value remain on screen when user naviagtes between records.
I have attached a sample database (access 2002-2003 format).
I am sure the answer is staring me in the face but I have now spent hours trying to resolve!!
Help very much appreciated.
Phil.
Update: One difference I can see is that on the query 'qryENTRY_COMPUTERS' where i have added tblSupplierProducts & tblSupplier the join properties are in reverse i.e. Left Table Name = tblSupplierProducts Rigth Table Name = tblComputers Left Column Name = ProductID Right Column Name = ProductID ????
I can't edit this.......................
View 14 Replies
View Related
May 6, 2005
i cant seem to get this to work.
any help would be greatly Appreciated
=sum(iif([SADate]>=DateSerial(Year(Date()),Month(Date()),[p]))
thanks in advance
View 3 Replies
View Related
Jul 20, 2005
I am trying to create a form that has an unbound text box which row source is a field in a table. I have created a subform that gets the records from another table.
Both tables are linked in a one to many relationship. When a user selects a record in the cmbo box i want the table to show only those records which are related.
This obviously didnt happen. So i changed the subform instead of a table as its source but a query, then in the criteria of a query just set it to look at the cmbo on the form. After the cmbo was updated i got the subform to requery.
This worked fine, but i was unable to make any changes or updates but with no particular error.
When inserting a subform using the wizard i do remember coming across a section of a wizard that asks me if i would like to tie in the subform with something on the form. However this part of the wizard just doesnt show. Maybe i was using a different version of access for this wizard?? i am currently using Access XP.
Am i really going MAD!!?
View 3 Replies
View Related
Aug 30, 2005
I'm trying to mess with a combo box where when one user selects a value a form opens up. This is the code I'm typing in but I keep getting a syntax error
DoCmd.OpenForm(Phone,[View As AcFormView=acNormal],[DataMode As AcFormOpenDataMode=acFormAdd],[WindowMode As AcWindowMode = acWindowNormal],[OpenArgs])
View 5 Replies
View Related