Is it possible to set the criteria for field in a query as domain of all values of another field?

[jackg6]

I am trying to set up a query to build a report off of that has multiple data sources. I am using two tables in my database, (PROJECTS and ALLOCATIONS) and one linked spreadsheet called TIMESHEET kept in Sharepoint that will be replaced each week with data from the previous week.


The query contains the Following Fields:

• Project_Title from PROJECTS
• Contract_Hours from PROJECTS
• Allocated_Hours from Allocation (joined with PROJECTS by Project_ID)
• Worked_Hours from TIMESHEET (joined with PROJECTS by Project_ID)

Both the Allocated_Hours and the Worked_Hours fields need to be sums grouped by Project_Title.

In order to generate this report the user is going to have to go online and download and replace the TIMESHEET file which will automatically update the linked table in my database. When the user does this they will have to input a date criteria into the website that generates the timesheet in order to filter out results for previous weeks and get the total for only the week we want to look at. The issue is that after this is all done and the user wants to run the report, they have to reenter the date criteria into the database in order to filter the Allocated_Hours field. My goal is to eliminate this step. I want to do this by checking the domain of the date field of the TIMESHEET and setting it as the criteria for Allocated_Hours, so that the data is always for the same period of time. If I can figure out how to check the domain of an entire field of a table I believe it will be simple enough to use it as a criteria for my query, however I don't know how to go about this, nor has any of my research provided me with an answer so far.

So I suppose the first step in accomplishing my goal, and my primary question is: How can I check the Domain of a date field in a linked table in order to apply it as a criteria for another field? But moreover I would just like to make sure what I am attempting is actually possible.

[June7]

What do you mean by 'Domain' - the range of dates, beginning and ending dates?

Maybe:

SELECT * FROM table/query WHERE datefield BETWEEN DMin("datefield", "Timesheet") AND DMax("datefield", "Timesheet");

or

SELECT * FROM table/query, (SELECT Min(datefield) AS MinDate, Max(datefield) AS MaxDate FROM timesheet) AS Range WHERE Allocation.datefield BETWEEN Range.MinDate AND Range.MaxDate;

[jackg6]

>DMin("Date","timesheet")-2 And <DMax("Date","timesheet")

Just figured this out right after I posted of course. It was a pretty simple solution but I posted the criteria I used in my query in case anyone else is looking into this. This is in the criteria box for the date field of my allocations table in my query.

[jackg6]

Thanks I didn't even see this until after I posted my solution but yes that is what I was looking for and you have essentially the same solution.

[June7]

Date is a reserved word. Should not use reserved words as names. This can cause issue. Enclose in [] but even that can fail.

[jackg6]

Date is a reserved word. Should not use reserved words as names. This can cause issue. Enclose in [] but even that can fail.

I know but the timesheet comes from the reporting feature on an external website and I thought it would be worth it in order to not have to change the field name every time we update it.

[June7]

Options are to enclose in [] like [Date] or to build a query that creates an alias name for the field and use that query instead of the table.

You might never have an issue but it is a possibility. I have encountered issue one time because of reserved word as name and even the []s did not resolve, had to use alias name.

[jackg6]

Options are to enclose in [] like [Date] or to build a query that creates an alias name for the field and use that query instead of the table.

You might never have an issue but it is a possibility. I have encountered issue one time because of reserved word as name and even the []s did not resolve, had to use alias name.

Ok thanks for the advice I'll see if I can get a query with an alias name to work.