Query Criteria

[MrMitch]

Hi all,

I have a table I am trying to query with certain criteria, and I am just not getting how I should be setting this up.

Table Fields
[Name] [Date] [Site] [Task] [Cost Code] [Category] [Hours] [TMUP]

I basically want it to return only records where the Site field is the same on the same date.

Thanks.

 

[Steve R.]

The code below pulls all records for a particular YEAR, to retrieve a specific date, such as March 31, 2009, you will need to adapt the code. TEXT2 is the text box for entering your date. List0 is simply a listbox so that you can see that the code worked.

Dim strCriteria As String
strCriteria = "Select Site FROM tblTableName WHERE year([date])= " & Me.Text2  
Me.List0.RowSource = strCriteria

Below is a modified version that would let you enter 3/31/2009 as a specific date.

strCriteria = "Select Site FROM tblTableName WHERE [date] = #" & Me.Text2 & "#"

 

[MrMitch]

I'm sorry, I may be confused. Do I put that in my sql statement?

Also, I don't want to enter a date, I just want a return of all records that have the same date and have the same site.

So if the date is the same, and the site is the same, I just want to see that data.

My Site Field =
Mid([PRT_CURRENT__TIME.Job],4,6) AS Site

My Date Field =
PRT_CURRENT__TIME.Date

Here is the SQL Statement right now without any conditions...

SELECT PRM_MASTER__EMPLOYEE.Employee_Name, PRT_CURRENT__TIME.Date, PRT_CURRENT__TIME.Job, PRT_CURRENT__TIME.Extra, PRT_CURRENT__TIME.JC_Cost_Code, PRT_CURRENT__TIME.JC_Category, PRT_CURRENT__TIME.Units, QRates.Unit_Price, Mid([PRT_CURRENT__TIME.Job],11,2) AS TMUP, Mid([PRT_CURRENT__TIME.Job],4,6) AS Site
FROM (PRT_CURRENT__TIME INNER JOIN PRM_MASTER__EMPLOYEE ON PRT_CURRENT__TIME.Employee = PRM_MASTER__EMPLOYEE.Employee) INNER JOIN QRates ON PRT_CURRENT__TIME.JC_Category = QRates.Category;

 

[MrMitch]

Sorry, I forgot to mention I also want to see where the Employee_Name field is the same as well, so it would be...

If the date, employee name, and site are the same.

 

[Steve R.]

Regretfully I have not done an SQL statement that is as complex as yours. It seems that you may need a UNION query with a WHERE clause. I think you will also need the "AS" statement to make Mid([PRT_CURRENT__TIME.Job],4,6) As EXPR1 and PRT_CURRENT__TIME.Date as EXPR2 so that your WHERE clause can be EXPR1 = EXPR2

Below is sample code lifted from www.tek-tips.com I hope that the sample code, could lead to a Eureka moment.

SELECT Contributors.FirstName, Contributors.LastName, Donations.DonationDate, Donations.DonationAmount as Amount, Donations.Loan, Donations.DonationReported, "Donation" AS Type
FROM Contributors INNER JOIN Donations ON Contributors.ID = Donations.ContributorID
Where Donations.DonationAmount>0
UNION SELECT Contributors.FirstName, Contributors.LastName, Donations.DonationDate, Donations.DonationInKindValue, Donations.Loan, Donations.DonationReported, "In Kind"
FROM Contributors INNER JOIN Donations ON Contributors.ID = Donations.ContributorID
Where Donations.DonationInKindValue>0;

 

[MrMitch]

Thanks for the info Steve. Unfortuantly, I am not versed when it comes to SQL statements, so I don't know if I will have one of those moments.

Regardless, thanks for the effort in trying to educate me.

I guess, I'll try and do some reading and see if I find out how to use that for what I'm trying to do.

In the mean time, if anyone else has some insight, I would greatly appricate it.

 

[Steve R.]

Regardless, thanks for the effort in trying to educate me.

Its a case of the blind leading the blind.

 

[WIS]

Hi all,

I have a table I am trying to query with certain criteria, and I am just not getting how I should be setting this up.

Table Fields
[Name] [Date] [Site] [Task] [Cost Code] [Category] [Hours] [TMUP]

I basically want it to return only records where the Site field is the same on the same date.

Thanks.

This will only work if you have a unique fld ie Primary key fld set in the table. eg CodeID, Autonumber. As a rule tables should have a unique fld - Primary key (except if they are very simple lookup tbls with only a couple of records in them).

You can do this with 2 (cascading) qrys, ie the 2nd qry uses data from the 1st.

Go to Queries->New->Find Duplicate Qrys Wizard. Select Date and Site flds as the Duplicate Value flds and then select CodeID as the Additional qry fld. This will create a qry with 3 flds CodeID (unique fld) and Date and Site (with duplicate values).

Now create a new qry using the qry above and the original tbl. This will identify all the records you want.

 

[jdraw]

MrMitch,

I looked at the query from Post #3. It breaks down to this for clarity

SELECT 
PRM_MASTER__EMPLOYEE.Employee_Name
, PRT_CURRENT__TIME.Date
, PRT_CURRENT__TIME.Job
, PRT_CURRENT__TIME.Extra
, PRT_CURRENT__TIME.JC_Cost_Code
, PRT_CURRENT__TIME.JC_Category
, PRT_CURRENT__TIME.Units
, QRates.Unit_Price
, Mid([PRT_CURRENT__TIME.Job],11,2) AS TMUP
, Mid([PRT_CURRENT__TIME.Job],4,6) AS Site
FROM 
PRM_MASTER__EMPLOYEE
,PRT_CURRENT__TIME
,QRates
WHERE
PRT_CURRENT__TIME.Employee = PRM_MASTER__EMPLOYEE.Employee
AND
PRT_CURRENT__TIME.JC_Category = QRates.Category

You have no criteria concerning same Date or same Site field?
You may have issues since Date is a reserved word in Access.
In which tables do the "matching fields" exist?
Could you show us the fields in each of the tables?

Can you state in english exactly what you are trying to do?

 

[WIS]

Hi all,

I have a table I am trying to query with certain criteria, and I am just not getting how I should be setting this up.

Table Fields
[Name] [Date] [Site] [Task] [Cost Code] [Category] [Hours] [TMUP]

I basically want it to return only records where the Site field is the same on the same date.

Thanks.

Can you dump the db in a zip file on this forum so that we can see exactly what is going on - there appears to be 3 tbls which need to be joined in a qry. If the tbl structures are satisfactory ie no 2 of the tables are giving a many to many relationship, then what you want should be easy via the Find Duplicates Wizard.

 

[editolis]

Hi all,

I have a table I am trying to query with certain criteria, and I am just not getting how I should be setting this up.

Table Fields
[Name] [Date] [Site] [Task] [Cost Code] [Category] [Hours] [TMUP]

I basically want it to return only records where the Site field is the same on the same date.

Thanks.

FIRST OF ALL you have to change the name fields you have.

The Name field Rename txtName. The Date field rename txtDate
The Cost Code rename CostCode etc...