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

Repetitive Entries...

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

Lots And Lots Of Similar Tables

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

Repetitive Records

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

Generalities And Exceptions, Or Repetitive Data?

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

Avoid Repetitive Values In Several Fields

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

Lots Of Choices

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

Is My Table To Big? Lots Of Data Fields...

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

Lots Of Slightly Different Forms Or Code ?

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

How To Handle Lots Of Input Fields

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

Sort Out Report For Individual Record With Lots Of Fields?

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

Adding A Unique Identifier Field To A Table With Lots Of Data

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

Creating Two Tables - Old Entries And Weekly New Entries?

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

Now Its All Gone Wrong!!!

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

What's Wrong?

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

What's Wrong

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

Would Someone Tell Me What's Wrong With This

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

Am I Doing This Wrong

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

What Is Wrong Please Help

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

What Is Wrong With This SQL?

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

What Is Wrong With This SQL?

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

Anything Wrong With This Sql

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

Where Have I Gone Wrong!!

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

Could Someone Tell Me What I'm Doing Wrong

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

What Is It I Am Doing Wrong??

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

Please Tell Me What Is Wrong

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







Copyrights 2005-15 www.BigResource.com, All rights reserved