Report on 3 Queries by Date

Hi I have 3 queries which I would like to join into one based on the
servicedate/taskdate so that for any given date I can see the totals of each
type of task we log for work. I would have joined these using a simple query
however my issue is that not everyday I log an event in any one table (Each
query based on only one table).



Query1
  ServiceDate
  CleanTimeTotal
  QATimeTotal
  ESTTimeTotal

Query2
 ServiceDate
 RepairTimeTotal

Query3
 TaskDate
 TaskTimeTotal

Now I suspect I need to rename TaskDate to ServiceDate in Query3 above which
I am okay to do but have no idea how to set up the union query or infact if
it is the best way to do this. The format of the data I would like to output
is as follows where DateDone is equal to ServiceDate and or TaskDate.

DateDone   CleanTimeTotal   QATimeTotal   ESTTimeTotal   RepairTimeTotal
TaskTimeTotal  

Any guidence on where I need to start would be appreciated.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200709/1

0
naigy
9/1/2007 9:20:06 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
789 Views

Similar Articles

[PageSpeed] 32

I suspect what you are trying to do could be accomplished if you were able
to start with a list of ALL services dates, and join the three queries you
have to that list, using a "directional" join (LEFT or RIGHT).

If you open the query in design view and join two [ServiceDate] fields
between two tables, you can right-click the join line and change it from
showing where [ServiceDate] matches in both, to showing ALL of one and ANY
matching in the other.

The reason for starting with ALL service dates is because none of your three
existing queries may have all the dates already.  You need to start will all
possibilities, then find any matches in the other queries.

-- 
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

"naigy via AccessMonster.com" <u17625@uwe> wrote in message
news:7795812165956@uwe...
> Hi I have 3 queries which I would like to join into one based on the
> servicedate/taskdate so that for any given date I can see the totals of
each
> type of task we log for work. I would have joined these using a simple
query
> however my issue is that not everyday I log an event in any one table
(Each
> query based on only one table).
>
>
>
> Query1
>   ServiceDate
>   CleanTimeTotal
>   QATimeTotal
>   ESTTimeTotal
>
> Query2
>  ServiceDate
>  RepairTimeTotal
>
> Query3
>  TaskDate
>  TaskTimeTotal
>
> Now I suspect I need to rename TaskDate to ServiceDate in Query3 above
which
> I am okay to do but have no idea how to set up the union query or infact
if
> it is the best way to do this. The format of the data I would like to
output
> is as follows where DateDone is equal to ServiceDate and or TaskDate.
>
> DateDone   CleanTimeTotal   QATimeTotal   ESTTimeTotal   RepairTimeTotal
> TaskTimeTotal
>
> Any guidence on where I need to start would be appreciated.
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200709/1
>

0
Jeff
9/2/2007 1:26:45 PM
Thanks Jeff,

This sounds like a logical step however how can I construct such a list of
service dates. 

Jeff Boyce wrote:
>I suspect what you are trying to do could be accomplished if you were able
>to start with a list of ALL services dates, and join the three queries you
>have to that list, using a "directional" join (LEFT or RIGHT).
>
>If you open the query in design view and join two [ServiceDate] fields
>between two tables, you can right-click the join line and change it from
>showing where [ServiceDate] matches in both, to showing ALL of one and ANY
>matching in the other.
>
>The reason for starting with ALL service dates is because none of your three
>existing queries may have all the dates already.  You need to start will all
>possibilities, then find any matches in the other queries.
>
>> Hi I have 3 queries which I would like to join into one based on the
>> servicedate/taskdate so that for any given date I can see the totals of each
>[quoted text clipped - 25 lines]
>>
>> Any guidence on where I need to start would be appreciated.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200709/1

0
naigy
9/3/2007 10:34:49 AM
If this were mine, and if there was NOT an already existing list of service
dates, I'd probably use a union query to derive all possible dates from the
three queries!  You could use something like (untested psuedo-SQL follows):

    SELECT [ServiceDate] FROM Table1
    UNION
    SELECT [ServiceDate] FROM Table2
    UNION
    SELECT [ServiceDate] FROM Table3;

This would give you all/any service dates in the underlying data.  (Do you
actually have three separate tables you are storing service dates in?  If
so, why?)

Then use the "directional" joins from the results of this union query to
each of your existing queries.

This is not "elegant", but I suspect it will get the job done...

-- 
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/





"naigy via AccessMonster.com" <u17625@uwe> wrote in message
news:77a9041e6d3ae@uwe...
> Thanks Jeff,
>
> This sounds like a logical step however how can I construct such a list of
> service dates.
>
> Jeff Boyce wrote:
> >I suspect what you are trying to do could be accomplished if you were
able
> >to start with a list of ALL services dates, and join the three queries
you
> >have to that list, using a "directional" join (LEFT or RIGHT).
> >
> >If you open the query in design view and join two [ServiceDate] fields
> >between two tables, you can right-click the join line and change it from
> >showing where [ServiceDate] matches in both, to showing ALL of one and
ANY
> >matching in the other.
> >
> >The reason for starting with ALL service dates is because none of your
three
> >existing queries may have all the dates already.  You need to start will
all
> >possibilities, then find any matches in the other queries.
> >
> >> Hi I have 3 queries which I would like to join into one based on the
> >> servicedate/taskdate so that for any given date I can see the totals of
each
> >[quoted text clipped - 25 lines]
> >>
> >> Any guidence on where I need to start would be appreciated.
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200709/1
>

0
Jeff
9/3/2007 2:34:07 PM
Thanks for your reply Jeff. I am using 3 tables with the following data and
my understanding was it is best to split data across several tables
particularly when some data is irrelevant to the other information.

Ie. Table 1.
This Table is used to log repair information and logs information such as
servicedate, technician, reportid, device serialnumber, repair location,
fault information & time to do repair.

Table 2.
This logs what units were cleaned, what were QA'd & what were electrically
safety tested and the dates. I work for a medical firm that rents out units
but most units do not come through our repair section and therefore do not
get info stored in Table1. However most records that have data in Table1 will
also have data in table2.

Table 3.
This covers any other work that we do like adhoc duties, stocktake, training
staff, deliveries etc.

So these 3 tables pretty much don't relate to each other except for the
servicedate.

I have a report which contains multiple sub reports which gives totals on all
the above over a specified time period however my manager has recently
decided he wants a very basic report which details daily figures.

Thanks for your assistance with this and I will give it a go when I am next
in the office.

Jeff Boyce wrote:
>If this were mine, and if there was NOT an already existing list of service
>dates, I'd probably use a union query to derive all possible dates from the
>three queries!  You could use something like (untested psuedo-SQL follows):
>
>    SELECT [ServiceDate] FROM Table1
>    UNION
>    SELECT [ServiceDate] FROM Table2
>    UNION
>    SELECT [ServiceDate] FROM Table3;
>
>This would give you all/any service dates in the underlying data.  (Do you
>actually have three separate tables you are storing service dates in?  If
>so, why?)
>
>Then use the "directional" joins from the results of this union query to
>each of your existing queries.
>
>This is not "elegant", but I suspect it will get the job done...
>
>> Thanks Jeff,
>>
>[quoted text clipped - 19 lines]
>> >>
>> >> Any guidence on where I need to start would be appreciated.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200709/1

0
naigy
9/3/2007 8:38:11 PM
Reply:

Similar Artilces:

Lost Active Directory for Microsoft CRM 3.0
Hi, We recently had a server failure on our Domain Controller which was unrecoverable and our backups for active directory failed to restore. The CRM database and application are on a seperate server so is fully intact. But we have lost the AD, which means no access. My question is what is the best method if any to recover the CRM system? I was hoping in version 3.0 this is possible as i know 1.2 was a nightmare due to the security descriptors. Am i able to reinstall and attach to existing database and apply CRM customization? Thanks, Pete Pete, You're in luck, this shouldn'...

[ANN] Office 11.3.8 update
Hi All, A new security update just came out. I suspect it requires 11.3.7 to install. Anyway, time to run the Microsoft Updater or to grab it from <http://www.microsoft.com/mac/downloads.aspx> Corentin -- --- Mac:MS MVP http://www.cortig.net/wordpress/ --- http://www.mvps.org - http://mvp.support.microsoft.com MVPs are not MS employees - Les MVP ne travaillent pas pour MS Remove "NoSpam" to e-mail me - Retirez "NoSpam" pour m'�crire I just let the Microsoft AutoUpdate do its thing. The only Office...

conversion #3
I am trying to upgrade from Outlook 2000 to Outlook 2003 on a new computer. When I attempt to export the data in my email files (inbox, sent,etc.)I do not have the "received" information exported - when mapping the fields I am unable to map several of the email fields; therefore, they are not exported. I have been unable to find the normal email fields to use in my mapping. How do I map normal email fields in an export operation? If I do not export this information my new computer has no idea when I received my old emails/ In the old days, the easiest way to keep all da...

BackUp #3
how i can take backup of all my company emailboxes and addresses? ...

Date display in Excel
Format column of cells as Date, display as mm/dd/yy. Date entered into cell, shows up correctly in the text entry field at the top of the screen, but the data on the worksheet displays as "33747", or similar number. Only happening on one workbook. Try tools|options|View tab|uncheck Formulas. Clark wrote: > > Format column of cells as Date, display as mm/dd/yy. Date > entered into cell, shows up correctly in the text entry > field at the top of the screen, but the data on the > worksheet displays as "33747", or similar number. Only > happening on one...

Reporting IRA distributions
I have an IRA account with corresponding cash account. I am trying to get the distribution from the IRA to show up as "Retirement Income" in the Tax-Related Transactions report. How to do it? The distributions are handled as a transfer from the IRA Investment cash acct to a checking account. Thanks for any help. Money 99, BTW. Do I need to upgrade to a later version? ...

Queries and Charts
Does anyone know why the expressions in queries work fine for reports but not charts? ...

Pass parameter from FORM to QUERY
I have an append query that I trying to call from a cmd btn...but it prompts me for the parameter(ie QuoteID)... How do I tye it into my call: Dim stQueName As String stQueName = "Quote Query" DoCmd.OpenQuery stQueName, acNormal, acEdit On Apr 13, 12:56 pm, jlt...@hotmail.com wrote: > I have an append query that I trying to call from a cmd btn...but it > prompts me for the parameter(ie QuoteID)... > How do I tye it into my call: > > Dim stQueName As String > stQueName = "Quote Query" > DoCmd.OpenQuery stQueName, acNormal, acEd...

C# Adding Days to a Date
Hello, I have 2 objects: objContract.activeon and objContract.expireson. I am trying to add 364 days to objContract.activeon and assign it to the value of objContractexpireson. ---------------------------------------------------------- // Contract Start Date DateTime dt = DateTime.Now; objContract.activeon = new CrmDateTime(); int iFound = 0; string sTemp = ""; if (objAccount.paymenttermscode.Value == 1) // due on receipt - use Todays Date { objContract.activeon = objInvoice.CFDinvoicedate; } else // ...

Separating Date and Time in a cell
I have a column of cells in the format "11/01/02 06:21". I would like to separate the text into 2 cells - one with the date and the other with the time. My attempts with LEFT and RIGHT have been unsuccesful. Thanks for your help Sameer --- Message posted from http://www.ExcelForum.com/ For the date use =INT(A1) replace A1 with the first cell of your range for time =MOD(A1,1) you probably have to reformat the first to mm/dd/yy (or whatever the setting is) and hh:mm Note that you can do this by just using format but if you want to compare to other cells with just pure d...

Changing query execution sequence
Hi all, I got a spreadsheet which would execute a bunch of queries. It's noted that the queries are executing in the sequence of when it was added to the spreadsheet. Does anyone out there know of a way to switch the order without deleting and recreating them? Thanks! Wing ...

3 Levels in Extended Pricing
Is it possible in Extended Pricing to have 3 levels of discounts. I assume yes because the max level in price trace is 3. I set up my Base Price Book to be all my List Prices. I then set up another price book and set it to be 50% of Base (Dealer Pricing) and then I want another price book to be "10% off of Dealer" I would then assign customers to this special price sheet/book. So doing a price trace I would have: List: $100 Dealer Original $100 Percent off 50% Unit Price $50 Special Original: $50 Percent off 10% Unit Price $45 Under my "Price Sheet Detail Maintenance...

Report Can Grow not aligned horizontally
I have a report where I have 1 row of text, memo, number, and date fields. The memo field can contain up to 4-5 lines of wrapped text. I have set all teh rows to "can grow". However, on my report, the conditional formatting doesn't grow. For instance, the fill (which I have set to a gray color) in the memo field is about 3 lines deep when there is a lot of text in the memo field, but the text and number fields associated with that record are actually only 1 line. This essentially looks like a mess, small gray filled boxes for the text fields and a large gray ...

date function #3
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C73005.0FA093A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a cell A1 with date 5/2/07. If in cell A2 I do month(A1), I get = 5. Is there a way to return May instead? either through a command or = formatting? I can reference cell A2 with the 5 with a if(cell =3D 5, = "May", ) in cell A3, but the date in cell A1 varies from Jan to Dec and = the 12 embedded if's in cell A3 are too long (that is the error I get = when trying to do so), plus i...

Custom reports #3
Hi, I want to pull a report on how many customers I am getting every hour of the day. Does anyone out there know how to do that? I can try writing a query if someone can tell me which table I can find this information in (timestamp, transaction etc). Any help is appreciated. Regards JD This info is given at the end of the Z report. If you want to use it in a query open the Z report recipt file using the Notepad and see the fields that are used... Report.Hour.HourSales etc... Hope this helps... Carlos "JD" wrote: > Hi, > > I want to pull a report on how many cust...

ANN: Optimizing the Performance of Microsoft Dynamics CRM 3.0 white paper
Hi, the White Paper "Optimizing the Performance of Microsoft Dynamics CRM 3=2E0" is now available: http://go.microsoft.com/fwlink/?LinkID=3D80916=20 Regards Nicolas F=FChrs ...

multiple iterations of same query
Running Access 2003 on Vista. I have a query that chooses 15 records at random. This works fine. What I want to do is run the same query for approx 110 different users, each with their own randomly selected 15 records. Obviously, I want to avoid manually running the same query that many times. How can I get around this? Post your query SQL. -- Build a little, test a little. "Nathan" wrote: > Running Access 2003 on Vista. > > I have a query that chooses 15 records at random. This works fine. > > What I want to do is run the same query fo...

how to run onhand value report
I get the message enter parameter when entering the zoom feature On Sat, 6 Mar 2010 17:36:01 -0800, junebugg <junebugg@discussions.microsoft.com> wrote: >I get the message enter parameter when entering the zoom feature You'll have to give us some more context than that, junebugg. What's the "onhand value report"? What's the "zoom feature"? You can see your database; we cannot! -- John W. Vinson [MVP] ...

how do I change date format in the header in Excel XP ?
I need to chage my date format, in the header to Day; Month DD, YYYY ie. Saturday; May 23, 2005. Woudl you please help me out ? Thank you. Regards, Hesam Shakourian Check this out. http://support.microsoft.com/?kbid=213742 but change the format to "dddd, MMMM dd, yyyy" "Hesam" <Hesam@discussions.microsoft.com> wrote in message news:93495F75-4196-4208-9C0D-E800BCAE3A89@microsoft.com... > I need to chage my date format, in the header to Day; Month DD, YYYY > ie. Saturday; May 23, 2005. > > Woudl you please help me out ? > > Thank you. > &g...

cannot send e-mail #3
I am getting the following message: message from Outlook 2000, you may receive the following error message: 0X800CCC78 error: The message could not be sent because the server rejected the sender's e-mail address It gradually has disabled all of my 3 accounts on Outlook. I can send, but not receive. I think there is some software corruption or a virus. Deleting and re-creating the account does not work Mario D'Avirro <mariod@mdainsurance.com> wrote: > I am getting the following message: > > message from Outlook 2000, you may receive the following > error mes...

Report: Cell #1, Cell #2, Cell #3, Cell #4
I am stuck again and would love som help :( I would like to repeat all words found inside ~25 cells, separated only by ", ", ignoring empty cells. Data: A1: [Apple ] A2: [Orange] A3: [Banana] A4: [Tomato] A5: [Syrup ] A6: [ ] A7: [ ] A8: [ ] The result should be something like: [Apple, Orange, Banana, Tomato, Syrup] -- JemyM ------------------------------------------------------------------------ JemyM's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26945 View this thread: http://www.excelforum.com/showthread.php?thre...

Option trades with past expiry dates not showing up in Portfolio M
Hello: Money 2006 Portfolio Manager does not show closed option trades that have expiry dates in the past, even when the "show closed positions" is checked. The transactions are still there in the investment accounts, just not visible in portfolio manager. I just called microsoft support and have notified them of the issue and hope that this issue is fixed as an update. If not, it pretty much makes the portfolio manager (and Microsoft Money 2006) useless for option trading investors. "MumbaiBabu" <MumbaiBabu@discussions.microsoft.com> wrote in message news:1...

How to show query parameters on an Excel page header or worksheet?
Is it possible to display query parameters on a page header or on a worksheet? I have a worksheet that uses a query to retrive data from an ODBC database. The Query prompts for the Start Date and End Date. I would like to be able to print the worksheet and display the Start Date and End Date the user typed in. Thank you for your assistance. ...

Tricky ComboBox / Filter query
Here's one On Sheet1, from A1:A2931 I have dates, every day from 01.01.02 to 31.12.2009 (A1="01/01/2002", A2="02/01/2002", etc). On a Userform I have 2 ComboBoxes: 1 for month, one for year. Is it possible to use these to filter Sheet1 and leave only those dates chosen in the dropdowns (eg, December 2004 only)? TIA Alan ...

How do you sort a date range by month?
We are trying to find out how many birthdays fall with in a given month using excel. You could use a helper column, with something like = A2. Format this (Format - Cells - Number - Custom: mmm. Now you can use Data - Subtotals, "At each change in: Month" and "Use function: Count." -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "Brewisc13" <Brewisc13@discussions.microsoft.com> wrote in message news:44E02AAC-8216-43F5-846F-E981E978E44B@microsoft.com... > We are trying to find out how many birthdays fall with in...