8 Querry Inside 1 - Can I Simplify?

Mar 19, 2006

I have a table with the following fields:

Record_No <--- This is an autonumber field
Est_COE --Date Field Type
DATE_DOCR
DATE_Followup
Progress

I then have used multiple querries that just count records based off of Status:

Example of one of the querry's:
SELECT Count(borrower.Record_Number) AS Close_Of_Escrow
FROM borrower
WHERE (((borrower.Est_COE) Between Date() And (Date()+7)) AND ((borrower.Progress)="active"));

Another Example of one other querry:
SELECT Count(Borrower_Journal_Notes.ID) AS Followups_Not_Done
FROM Borrower_Journal_Notes
WHERE (((Borrower_Journal_Notes.Follow_Up_date)>Date()) AND ((Borrower_Journal_Notes.Followed_Up_Complete) Not Like -1));

Then I put both these querry's into a another querry so i can get the data put into a single form using one record source. (The master querry)

View Replies


ADVERTISEMENT

Is There A Way To Simplify This?

Jun 14, 2007

So I have an access application to keep track of the status of small programmable PCBs that we use..
every time one of these pcbs is relocated or reprogrammed, an entry is made into a table in my db (tblLocation) with a unique ID for that action, the ID of the PCB (SD), the location it is going to, the new owner, the firmware version it's been programmed with and the date. Hence, multiple (dated) entries for each board are possible.

what I need to try and do is retrieve the most recent set of data from this table for every board we own... so far I have come up with a method that uses 2 queries, but I would like to combine this into one SQL statement that I can pass to the access db from an excel front end using ADO...

here's my 2 queries:
#1 (qryLastEntry) finds the date of the the most recent entry for each board:

SELECT DISTINCT tblLocation.SD, Max(tblLocation.Date) AS LatestDate
FROM tblLocation
GROUP BY tblLocation.SD;


#2 uses the result set from the above to retrieve only the most recent set of data for each board:

SELECT tblLocation.SD, tblLocation.Date, tblLocation.Owner, tblLocation.Location, tblLocation.Firmware
FROM qryLastEntry INNER JOIN tblLocation ON (qryLastEntry.LatestDate = tblLocation.Date) AND (qryLastEntry.SD = tblLocation.SD)
ORDER BY tblLocation.SD;


If there is a way to combine the 2 into one statement, that would be very useful, as although this method works just fine with access and using the built-in 'get external data' feature within excel, I'd like to be able to manipulate the SQL pragramatically (and also, I may want to migrate to mysql so a cross-platform solution would definitely be preferable in the long run).
I've had a look at using subqueries to achieve this but with no luck so far..

thanks in advance,
Bogzla

View 1 Replies View Related

Is It Possible To Simplify This Query?

Jul 10, 2007

This query works, but is really slow. Is there a better way to write it? I have 2 tables, Card and Shipped. For all cards, I am trying to insert a record into the Shipped table if it they are not already there:

INSERT INTO SHIPPED (CardID, ShippedFrom, ShipDate, QtyShipped)
SELECT Card.CardID,'Wpg',#1/1/2007#,0
FROM Card
WHERE Card.CardID NOT IN
(
SELECT Shipped.CardID
FROM Card LEFT JOIN Shipped ON Card.CardID=Shipped.CardID
WHERE SystemID= 10 AND ShippedFrom='Wpg' AND ShipDate=#1/1/2007#
)
AND SystemID=10

View 3 Replies View Related

Please Help Me Simplify This Query

Feb 21, 2008

i have created a query that solves for the difference of the debit and credit of the accounts receivables of a certain member or person..

SELECT DISTINCT (
(SELECT SUM(Amount) FROM (SELECT tblJournal.Amount, tblJournal.AccountAction
FROM tblJournal
WHERE JournalEntryNumber
IN ( SELECT JournalEntryNumber
FROM tblAccountsReceivableFromMembers
WHERE MemberID = '2008-0001')
AND AccountNumber = 120
) WHERE AccountAction='Debit')
-
(SELECT SUM(Amount) FROM (SELECT tblJournal.Amount, tblJournal.AccountAction
FROM tblJournal
WHERE JournalEntryNumber
IN ( SELECT JournalEntryNumber
FROM tblAccountsReceivableFromMembers
WHERE MemberID = '2008-0001')
AND AccountNumber = 120
) WHERE AccountAction='Credit')
) AS ['TotalAccountsReceivables']
FROM (SELECT tblJournal.Amount, tblJournal.AccountAction
FROM tblJournal
WHERE JournalEntryNumber
IN ( SELECT JournalEntryNumber
FROM tblAccountsReceivableFromMembers
WHERE MemberID = '2008-0001')
AND AccountNumber = 120
);

i need help on this so that there would only be one (WHERE MemberID = ?) to use. thanks so much! i hope to read your replies as soon as you can!

View 8 Replies View Related

Forms :: Recordset Of Subform Inside Another Subform Which Is Inside A Mainform

Dec 16, 2013

I'm having a problem with the syntax of a recordset of a Datasheet inside a subform which is also inside a Main Form.

Main Form - frm_1_0_LMS
Subform - frm_1_4_0_TeamApprovals
Subform(Datasheet) - frm_1_4_1_TeamApprovalsList

Here is my code:

Code:

Dim rs As DAO.Recordset
Set rs = Forms!frm_1_0_LMS.frm_1_4_0_TeamApprovals.frm_1_4_1_TeamApprovalsList.Form.Recordset
If Not (rs.EOF And rs.BOF) Then
Forms!frm_1_4_2_ApproveDeclineUserLeave.Controls("lblFiledDateLeave").Caption = rs!Leave_Date
End If

I am getting this error: Object doesn't support this property or method

View 1 Replies View Related

Linking Tables How To Simplify

Mar 20, 2006

I have an Analysis_Database which reads tables and performs calculations in a Project_Database. There are multiple Project_Databases which reside together in a folder. To run my queries and analysis I link my Analysis_Database to each of the other databases and run my reports.

Is there a way to set up these links in a table or something to eliminate the Tools/Utilities/Linked Table Manager...etc

Any siuggestions to make this process more streamlined would be appreciated. (I can not make any changes to the Project_Database).



Thanks

View 1 Replies View Related

How To Simplify This Expression In A Query?

Oct 13, 2005

I have a form field whose value is as follows:

=Nz(DSum("Capital","Capital_Act","DatePart('q', CVDate([Capital_Month] & ' 1')) = 1 And [projectid]=" & "'" & [projectid] & "'"),0)

Since I am using Domain lookup function in this field, it takes considerable time for this field to get populated on the form when I move from one record to another.

Can anyone tell me the syntax for me to move this expression inside the Form's Query so this can execute faster?

Please note that I need this query to be updateable as I have some other form fields that needs to be edited.

Thanks!

View 1 Replies View Related

I Need To Simplify My Report_launch Forms

Jul 5, 2005

***this has already been posted on the report formus...i posted it here also due to its relation to forms***

**********************DO NOT GET MAD**************************

GOOD MORNING...

I currently have a form to launch a set of pre-desinged reports. The main function of the form is that it allows the user to sort or filter the data that appears in the report based on their selection (from combo boxes). I currenty have it set up so that the user can choose a specific client, facility, or region. The problem is the following:

THE FORM IS WAY TOOOO BUSY AND NEEDS TO BE SIMPLIFIED. EX:

CLIENT (combo box)
Microsoft
nortel
dell
ibm
.
.
.
btnLaunchRPT1 btnLaunchRPT2 btnLaunchRPT3

REGION (Combo box)
North
South
East
west
north-west
.
.
.
btnLaunchRPT1 btnLaunchRPT2 btnLaunchRPT3


etc.

each btnLaunchRPT'n' has the following code
DoCmd.OpenReport "rptECM Numbers", acViewPreview, , "[Region] = " & "'" & Me.Combo299 & "'"

but I change the [region] to whatever it is I am sorting by. Can I reduce this down to only having one set of btnLaunchRPT buttons that will detect what the user has selected and applies the proper sorts

HELP

View 2 Replies View Related

Super Complicated Query - Can Anyone Simplify?

Jul 25, 2005

Well Super Complicated to me. I have setup two queries as follows,

Query 1

The function of the query is basically as follows.

1. If Field 1 is blank do nothing.
2. If Field 2 is blank then Add 7 days to Field 2.
3. If this falls over a weekend then Add another two days.

The expression I have added to do this is,

IIf(IsNull([Field1]),"", IIf(IsNull([Field2]),DateAdd("d",DateDiff("ww",[Field1],DateAdd("d",7,[Field1]),7)*2,DateAdd("d",7,[Field1]))))

Query 2

This query check whether the result of query 1 is a Weekend and if so adds another two days to it,

IIf(Weekday([Field2]= 1 Or Weekday([Field2])=7,DateAdd("d",2,[Field2]),[Field2])

My biggest problem is if I try and do any filters on this information then I get "Expression to complex" errors.

Am I over complicating things here?

Any ideas would be greatly appreciated.

JC3

View 1 Replies View Related

Simplify Entry Of Date/time

Jun 29, 2006

Hi, hope someone has an idea of where to start on this.

Our company offers projects to multiple contractors and we have to enter when we spoke to them and their response. I put a textbox with default of Now(), thinking these would be recorded in real time and it would be no effort at all. But the offers are frequently entered days later, and keying in dozens of specific dates/times in the long format is very irritating for users.

Does anyone know of something similar to the DatePicker or calendar popups that will let you use arrows or comboboxes or something to quickly enter a time? We record date, hour & minute (no seconds) because it updates the contractor's order on the rotation list (i.e., next call is made to the contractor who has been waiting the longest for an offer). No ActiveX please, we're in secured network environment.

Thanks in advance.

View 3 Replies View Related

AND/OR Querry Help

Sep 16, 2007

Guys,


Could you please help me to make a query with the below conditions from the attached test.mdb file?

1. Enq Received Date 10 days before the search date AND/OR
2. Technical Sub Date 4 weeks in advance of the search date AND/OR
3. Potential Date 4 weeks in advance of the search date

The querry will display all the records which satisfies the above conditions.

Thanks in Advance.

Prajith

View 11 Replies View Related

Querry!!

Oct 17, 2006

I have a table, [temp_indicateur] which has fields [id,libelle_court,description,calcule,resitute,orig ine,seuil_min,seuil_max]
[number,text,text,boolean,boolean,boolean,number,nu mber]

My querry is this: insert into temp_indicateur (id,libelle_court,description,origine) values (" & rs!id & ",'" & rs!libelle_court & "','" & rs!description & "'," & rs!origine & ")"

But it gives me an error: Too little parameters

The table has no primary keys and no foreign keys. rs!id is the value of an earlier querry which I am running. Independently, I get the values of rs correctly but I can't get them to execute in this querry.

View 2 Replies View Related

Use Count In Querry

Jul 19, 2005

My current table

ClassName PropertA
ClassA 1
CassA 2
ClassB 2
ClassA 3
ClassA 2
ClassB 2

THis is my sample table with two columns. Property A can have 3 values 1,2 and 3. I want TO SELECT 4 columns such as classname, CountOf1, CountOf2, CountOf3. I just want ClassA in one row and at the same time get the number of 1's, 2's and 3's for that classA in seperate columns in the same row.
I appreciate your time. Please Let me know how to
write this querry.

View 1 Replies View Related

Update Querry

Nov 28, 2005

Hello,
This may sound too common a question, but I really need help on it. Prior querries in the same do not seem to solve it.

I have two tables, table1 and table2 with the following fields
table1 ( ACID, name, address, Salary, MStatus,Age)
table2 ( ACID,salary, Age)

ACID is the common field in the two tables ( primary key in table1)

Required

I need to update table1 with the colums in table2 for all the similar ACID in in table1.

Basically, after teh update, table1 will have all the information it had before the update, but a few fields will have changed coz of teh new information coming from table2.

Please help with the code. I'm using access, and the table structures are all the same.

Thanks in advance

View 1 Replies View Related

Update Querry

Feb 21, 2006

I have these three tables A, B, C and D. IN table A, SSN is the primary key. Tables B, C, and D have other columns, but the also have SSN as one of the columns. Also SSN is NOT required in other tables.

What I need to do is to be able to run an update querry that will update the value of the SSN in table A, and in any other table that will have that SSN.

I use Access 2000

View 1 Replies View Related

Simple Querry Help

Oct 25, 2006

I Have A Querry That Adds The Amount Of A Certain Light Bulbs I Have Order Since 2003. I Then Subtract The Amount Of That Light Bulb I Have On Hand Now...it Gives Me The Amount I Have Used Since 2003. Now I Want The Querry To Take That Number And Divide It By The Numberber Of Months Its Been When The Querry Was Open.

Example...i Open The Querry 10-23-06
It Tells Me The 60 Watt Bulb
Has Been Ordered 900 Times Since 12-2003
I Have 300 In Stock On 10-23-06
I Have Used 600 Since 12-2003

The Querry Needs To Give Me This Answer
You Use 17 60 Watt Bulbs A Month

View 6 Replies View Related

Up-date Querry?

Nov 20, 2007

hello all,
i would very much like anyones help with this,, i'm hoping its not too much to ask,, the story is a sfollows,,, i have two tables both conatining the same fields,, table 1 has approx 1k entries, where table 2 has approx 400 entries,, the data in table 2 is the newer data. what i would like to do is run a querry that would enable me to 'post' the data from table2 into table1,, for example table 1 has activity jc111 at 10% where table2 has jc111 at 20%,, if i could simply run this querry that would generate a new table or just up-date table1 my life would be very much better,, i really appreciate anyones help
- please remember that i'm quite new to this
thank you very very much in advance
best regards

View 3 Replies View Related

Querry By Primary Key? Is It Possible?

Mar 14, 2008

Hi,
I am just starting with access and dbs and already have a problem

i am working on a db for managings goods being retured

my db has particular tables:

GOOD
GUpc (PK)- unique product code
GDescription

PERSON
PReference (PK)
PName
PSurname
PTel
PMail

COMPANY
CReference (PK)
CName
CAddress
CTown
CPostcode
CCountry

RGA
RGAReference (PK)
CReference (FK)
PReference(FK)
GUpc(FK)
RGADate
RGASatus
RGACostOfRepair

I managed to get a query working so that i can search the PERSON table searched by surname

PARAMETERS [PSurname] Text ( 255 );
SELECT PERSON.PReference, PERSON.PName, PERSON.PSurname, PERSON.PTel, PERSON.PMail
FROM PERSON
WHERE (((PERSON.PSurname)=[PSurname]));

but i cant get it to work i wanted to search it by PReference

PARAMETERS [PReference] Text ( 255 );
SELECT PERSON.[PReference], PERSON.[PName], PERSON.[PSurname], PERSON.[PTel], PERSON.[PMail]
FROM PERSON INNER JOIN RGA ON PERSON.PReference=RGA.PReference
WHERE (((PERSON.PReference)=[PReference]));

I get a msg that The expression is typed incorectly or is to complex to be evaluated. For example a numeric expression my contain too many complecated elements. Try symplyfing the expression by assigning parts if the expression as variables.

AND THIS IS NO HELP FOR ME... francly

The same situation is with all the other tables- its possible (query- search) for everything apart for the primary keys.

I prepared a querry gathering data from all those tables. I managed to get it work with parameter like the one below with surname

PARAMETERS [PSurname] Text ( 255 );
SELECT COMPANY.*, RGA.*, PERSON.PName, PERSON.PSurname, COMPANY.CName, GOOD.GDescription
FROM PERSON INNER JOIN (GOOD INNER JOIN (COMPANY INNER JOIN RGA ON COMPANY.CReference = RGA.CReference) ON GOOD.GUpc = RGA.GUpc) ON PERSON.PReference = RGA.PReference
WHERE (((PERSON.PSurname)=[PSurname]));

but the same situation again, i cant get it to work if i want to get it for RGAReference, CReference, PReference,GUpc

I don't know what i am doing wrong?
have I designed it all wrong or is it just a small thing...

Thank you for all your help







My problems started when I tried to run a querry that will return the GDescription (this tables store data about 3000 products)

View 2 Replies View Related

Search Querry

May 6, 2005

I have created a search form made up of a number of different boxes. In my querry for the search I am using the following statement:

Like "*" & [Forms]![Search]![ExerciseName] & "*"

I a have a number of theses all saying similar things. At present I have to put something in each of the search boxes. Is there a way that I can leave a box blank and still search by other criteria. If I leave a box blank at present it returns no results.

View 11 Replies View Related

Querry Problem

Sep 20, 2004

Hello All
This might be an easy problem but I am not quite sure how to go about solving it. Presently I have a querry that is linked to a form so that when a person selects a role from the drop down list and you click search it uses the elment selected in the drop down list to show the coreesponding users. My probelme now is that I want to be able to select two different roles and use this querry to give me the users corresponding to these 2 roles. I would really appreciate your help what I have tired to do is create a new combo box and named it role 1 (the first one was named role) and then in the query in the criteria i tired to put the following
[Forms]![Users To Role Search Engine]![Role, Role1]but that give me nothing I am sure there is a flaw in my logic but I am not sure how to solve this. Thank you for your help

View 2 Replies View Related

Weird Querry!!

Oct 13, 2006

Hi,

I have this querry but when I execute on a button click it dosen't give me any error....but no data is saved in the table, why????

strSQL = " UPDATE DISTINCTROW projet set id='" & txt_id.Value & "',code_NOP='" & txt_code_NOP.Value & "',client='" & txt_client.Value & "',libelle_projet='" & txt_libelle_projet.Value & "',id_clarity='" & txt_id_clarity & "',technologie_projet='" & txt_technologie_projet.Value & "',em='" & txt_em.Value & "',date_debut=#" & txt_date_debut.Value & "#,date_fin=#" & txt_date_fin.Value & "#,site_mandataire='" & txt_site_mandataire.Value & "',charge_totale=" & txt_charge_totale & ",ca_total_vendu=" & txt_ca_total_vendu.Value & " WHERE id='" & Form_projet.id.Value & "';"

View 7 Replies View Related

Problem With Querry?

Dec 1, 2006

I have a string which I want to use as a querry, but its giving me an error saying "object required" even before executing the querry..

strSQL = "INSERT INTO projet (id,code_NOP,client,libelle_projet,id_clarity,tech nologie_projet,em,date_debut,date_fin,site_mandata ire,charge_totale,ca_total_vendu,no_sous_fon,no_ec rans_s,no_ecrans_m,no_ecrans_c,no_traite_s,no_trai te_m,no_traite_c,no_impression_s,no_impression_m,n o_impression_c,prov_risque,prov_garantie)" & _
" values('" & txt_id.Value & "','" & txt_code_NOP.Value & "','" & txt_client.Value & "','" & txt_libelle_projet.Value & "','" & txt_id_clarity.Value & "','" & txt_technologie_projet.Value & "','" & txt_em.Value & "',#" & Format(txt_date_debut.Value, "mm/dd/yyyy") & "#,#" & Format(txt_date_fin.Value, "mm/dd/yyyy") & "#,'" & txt_site_mandataire.Value & "'," & txt_charge_totale.Value & "," & txt_ca_total_vendu.Value & "," & txt_sous_fon.Value & "," & txt_ecrans_s.Value & "," & txt_ecrans_m.Value & "," & txt_ecrans_c.Value & "," & txt_traite_s.Value & "," & txt_traite_m.Value & "," & txt_traite_c.Value & "," & txt_impression_s.Value & "," & txt_impression_m.Value & "," & txt_impression_c.Value & "," & txt_prov_risques.Value & "," & txt_prov_grantie.Value & " );"

Can anyone solve it?

View 1 Replies View Related

Querry Not Working!!

Dec 6, 2006

Hi,

I am running a querry, but it is not selecting any data...
Now I have checked all the stuff..everything is fine except the date part.

strSQL = "select periodicite.libelle as pl,indicateur.libelle_court as il,valeur.valeur,valeur.date_valeur,valeur.id_proj et" & _
" FROM valeur,indicateur,periode,periodicite" & _
" WHERE valeur.id_indicateur=indicateur.id " & _
" AND periodicite.id= " & cmb_periodicite.Value & "" & _
" AND periodicite.id=periode.id_periodicite" & _
" AND periode.id=valeur.id_periode" & _
" AND valeur.date_valeur BETWEEN #" & txt_date_debut.Value & "# AND #" & txt_date_fin.Value & "# "

I am printing the querry too, so that I can check whether it is giving the correct values from the txt_date_debut and txt_date_fin. I found out that everything is fine. I just can't figure out the problem !!! Can anyone help please????

View 2 Replies View Related

Querry About Sales Commisions

Oct 1, 2005

Hi There
I Need A Querry For Commission Sales.
I Have 10 Items 7 Items At 2% Of Commission 3 At 4%. Each Salesman Have To reach A Min Of 5000 USD Of Sales Per Month Then All Sales Above That Amount Commission Will Be Calculated.
I Need A Querry To Calculate Sales And If Salesman reached The Min Sales Commission Is Calculated .

Any Help ???????
Thanks

View 1 Replies View Related

Update Table Querry

Oct 18, 2005

I have a table in access. The columns are State, FICO, LTV, DTO,LBI and Grade need to update the column (Grade) so that it gets the values A, B, C or D based on the following conditions

If FICO >525 and LTV <100 AND DTI <50 then grade = D
IF FICO >600 AND LTV BTWN 90 -100 AND DTI <50 Grade =c
If FICO >600 AND LTV <90 AND DTI<45 Grade =B
IF FICO >680 AND LTV <90 AND DTI<45 grade =A
IF FICO >720 AND LTV <100 AND DTI<50 Grade =A

Please help me figure out the ACCESS select querry for this.

View 2 Replies View Related

Help Pls! Calculated Grouped Querry

Jul 30, 2007

Hi Guys,

can anybody help me on the below?

i need to generate a query contains

name, project, total_Hours_worked (only if total is >45) for a week by each employees. the query should contain total hours for all the weeks.
The table contains week_ID column and dyas worked column.
i can create a query which gives me sum of day_hours worked using selecct sum(day_hours) as Total.

but i badly need total sum of week hours worked by each employees. so that i can generate overtime report grouped on weekly who has woked more than 45 hours per week.

the table is attached. please see and help me if you can.

Thanks
Kiran Konsaai

View 2 Replies View Related







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