Averaging Time from mulitple tables

im currently making a database that logs time for how long people are waiting
i want to make a query that averages all that infomation i have 1 table that
logs when people arrive and another one when people leave i also need to be
able to use it when there is more data added to it. i know nearly nothing
about queries in databases so can you type in plain english, thanks in
advance.

0
Phoenix2520
1/9/2008 11:42:06 AM
access.queries 6343 articles. 1 followers. Follow

8 Replies
770 Views

Similar Articles

[PageSpeed] 27

You will not be able to do this unless you have a way to tie your tables 
together.

Presumably you have a table of clients, with a ClientID primary key. Or 
perhaps you just assign a ticket number when the person arrives, and use the 
same ticket number when they leave. However you do it, you must have some 
way to match the arrival record in one table with the departure record in 
the other table--a common ClientID or TicketID number.

Step 1: Create a query that shows the arrival time.
You can do this with the query designer.
When you switch to SQL View (View menu), it will look something like this:
    SELECT Arrival.ClientID,
    Arrival.ArrivalTime
    FROM Arrival;

Step 2: Add a subquery to get the matching departure time.
You will have to type the new bit into SQL View.
It will be something like this:
SELECT Arrival.ClientID,
Arrival.ArrivalTime,
    (SELECT Min(Depart.DepartTime) AS MinDepartTime
    FROM Depart
    WHERE Depart.ClientID = Arrival.ClientID
    AND Depart.DepartTime > Arrival.ArrivalTime) AS DepartTime
FROM Arrival;

If subqueries are new, see:
    http://allenbrowne.com/subquery-01.html

Step 3: Calculate the difference.
Once you have the subquery returning the correct date time value, use 
DateDiff() to calculate the difference in minutes. It will be something like 
this:

SELECT Arrival.ClientID,
Arrival.ArrivalTime,
DateDiff("n", Arrival.ArrivalTime,
    (SELECT Min(Depart.DepartTime) AS MinDepartTime
    FROM Depart
    WHERE Depart.ClientID = Arrival.ClientID
    AND Depart.DepartTime > Arrival.ArrivalTime)) AS Minutes
FROM Arrival;

In the query, it's best to keep the duration as a number, i.e. in minutes or 
seconds, or divide by 60 for hours and fractions of an hour. If you need to 
display it as hours:minutes in a report, this article explains how:
    http://allenbrowne.com/casu-13.html

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Phoenix2520" <u40527@uwe> wrote in message news:7df2eddafbba1@uwe...
> im currently making a database that logs time for how long people are 
> waiting
> i want to make a query that averages all that infomation i have 1 table 
> that
> logs when people arrive and another one when people leave i also need to 
> be
> able to use it when there is more data added to it. i know nearly nothing
> about queries in databases so can you type in plain english, thanks in
> advance. 

0
Allen
1/9/2008 12:11:05 PM
Thanks For The Help,i just wanted to check if my code was right

SELECT Entry_Card.[Card Number],
 Entry_Card.[Arrival Time]
FROM Entry_Card;

(SELECT Entry_Card.[Card Number], Entry_Card.[Arrival Time]
FROM Entry_Card;
   (SELECT Min(Exit_Card.Time Exited) AS MinDepartTime
   FROM Exit_Card
   WHERE Exit_Card.Card Number = Entry_Card.Card Number
   AND Exit_Card.Exit Time > Entry_Card.Arrival Time) AS Time Exited
FROM Entry_Card;)

SELECT Entry_Card.Card Number,
Entry_Card.Arrival Time,
DateDiff("n", Entry_Card.Arrival Time,
   (SELECT Min(Exit_Card.Time Exited) AS MinDepartTime
   FROM Exit_Card
   WHERE Exit_Card.Card Number = Entry_Card.Card Number
   AND Exit_Card.Time Exited > Card Entry.Arrival Time)) AS Minutes
FROM Card Entry;


Allen Browne wrote:
>You will not be able to do this unless you have a way to tie your tables 
>together.
>
>Presumably you have a table of clients, with a ClientID primary key. Or 
>perhaps you just assign a ticket number when the person arrives, and use the 
>same ticket number when they leave. However you do it, you must have some 
>way to match the arrival record in one table with the departure record in 
>the other table--a common ClientID or TicketID number.
>
>Step 1: Create a query that shows the arrival time.
>You can do this with the query designer.
>When you switch to SQL View (View menu), it will look something like this:
>    SELECT Arrival.ClientID,
>    Arrival.ArrivalTime
>    FROM Arrival;
>
>Step 2: Add a subquery to get the matching departure time.
>You will have to type the new bit into SQL View.
>It will be something like this:
>SELECT Arrival.ClientID,
>Arrival.ArrivalTime,
>    (SELECT Min(Depart.DepartTime) AS MinDepartTime
>    FROM Depart
>    WHERE Depart.ClientID = Arrival.ClientID
>    AND Depart.DepartTime > Arrival.ArrivalTime) AS DepartTime
>FROM Arrival;
>
>If subqueries are new, see:
>    http://allenbrowne.com/subquery-01.html
>
>Step 3: Calculate the difference.
>Once you have the subquery returning the correct date time value, use 
>DateDiff() to calculate the difference in minutes. It will be something like 
>this:
>
>SELECT Arrival.ClientID,
>Arrival.ArrivalTime,
>DateDiff("n", Arrival.ArrivalTime,
>    (SELECT Min(Depart.DepartTime) AS MinDepartTime
>    FROM Depart
>    WHERE Depart.ClientID = Arrival.ClientID
>    AND Depart.DepartTime > Arrival.ArrivalTime)) AS Minutes
>FROM Arrival;
>
>In the query, it's best to keep the duration as a number, i.e. in minutes or 
>seconds, or divide by 60 for hours and fractions of an hour. If you need to 
>display it as hours:minutes in a report, this article explains how:
>    http://allenbrowne.com/casu-13.html
>
>> im currently making a database that logs time for how long people are 
>> waiting
>[quoted text clipped - 5 lines]
>> about queries in databases so can you type in plain english, thanks in
>> advance.

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

0
Phoenix2520
1/10/2008 5:46:55 AM
Sorry for double post but i also need a way to average the entire table not
just one entire does this code do this?

Phoenix2520 wrote:
>Thanks For The Help,i just wanted to check if my code was right
>
>SELECT Entry_Card.[Card Number],
> Entry_Card.[Arrival Time]
>FROM Entry_Card;
>
>(SELECT Entry_Card.[Card Number], Entry_Card.[Arrival Time]
>FROM Entry_Card;
>   (SELECT Min(Exit_Card.Time Exited) AS MinDepartTime
>   FROM Exit_Card
>   WHERE Exit_Card.Card Number = Entry_Card.Card Number
>   AND Exit_Card.Exit Time > Entry_Card.Arrival Time) AS Time Exited
>FROM Entry_Card;)
>
>SELECT Entry_Card.Card Number,
>Entry_Card.Arrival Time,
>DateDiff("n", Entry_Card.Arrival Time,
>   (SELECT Min(Exit_Card.Time Exited) AS MinDepartTime
>   FROM Exit_Card
>   WHERE Exit_Card.Card Number = Entry_Card.Card Number
>   AND Exit_Card.Time Exited > Card Entry.Arrival Time)) AS Minutes
>FROM Card Entry;
>
>>You will not be able to do this unless you have a way to tie your tables 
>>together.
>[quoted text clipped - 50 lines]
>>> about queries in databases so can you type in plain english, thanks in
>>> advance.

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

0
Phoenix2520
1/10/2008 5:58:15 AM
1. Use square bracket around names that contain spaces, e.g. [Exit Time]

2. In the Exit_Card table, is the field named [Exit Time] or [Time Exited]?

3. Your middle query won't show the TimeExited correctly.
The subquery is part of the SELECT clause, so before the FROM clause.

Try:
SELECT Entry_Card.[Card Number],
Entry_Card.[Arrival Time]
   (SELECT Min(Exit_Card.[Exit Time) AS MinExitTime
   FROM Exit_Card
   WHERE (Exit_Card.[Card Number] = Entry_Card.[Card Number])
   AND (Exit_Card.[Exit Time] > Entry_Card.[Arrival Time])) AS TimeExited
FROM Entry_Card;

Once that's working, you can work on the DateDiff() part.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Phoenix2520 via AccessMonster.com" <u40527@uwe> wrote in message
news:7dfc6679de974@uwe...
> Thanks For The Help,i just wanted to check if my code was right
>
> SELECT Entry_Card.[Card Number],
> Entry_Card.[Arrival Time]
> FROM Entry_Card;
>
> (SELECT Entry_Card.[Card Number], Entry_Card.[Arrival Time]
> FROM Entry_Card;
>   (SELECT Min(Exit_Card.Time Exited) AS MinDepartTime
>   FROM Exit_Card
>   WHERE Exit_Card.Card Number = Entry_Card.Card Number
>   AND Exit_Card.Exit Time > Entry_Card.Arrival Time) AS Time Exited
> FROM Entry_Card;)
>
> SELECT Entry_Card.Card Number,
> Entry_Card.Arrival Time,
> DateDiff("n", Entry_Card.Arrival Time,
>   (SELECT Min(Exit_Card.Time Exited) AS MinDepartTime
>   FROM Exit_Card
>   WHERE Exit_Card.Card Number = Entry_Card.Card Number
>   AND Exit_Card.Time Exited > Card Entry.Arrival Time)) AS Minutes
> FROM Card Entry;
>
>
> Allen Browne wrote:
>>You will not be able to do this unless you have a way to tie your tables
>>together.
>>
>>Presumably you have a table of clients, with a ClientID primary key. Or
>>perhaps you just assign a ticket number when the person arrives, and use 
>>the
>>same ticket number when they leave. However you do it, you must have some
>>way to match the arrival record in one table with the departure record in
>>the other table--a common ClientID or TicketID number.
>>
>>Step 1: Create a query that shows the arrival time.
>>You can do this with the query designer.
>>When you switch to SQL View (View menu), it will look something like this:
>>    SELECT Arrival.ClientID,
>>    Arrival.ArrivalTime
>>    FROM Arrival;
>>
>>Step 2: Add a subquery to get the matching departure time.
>>You will have to type the new bit into SQL View.
>>It will be something like this:
>>SELECT Arrival.ClientID,
>>Arrival.ArrivalTime,
>>    (SELECT Min(Depart.DepartTime) AS MinDepartTime
>>    FROM Depart
>>    WHERE Depart.ClientID = Arrival.ClientID
>>    AND Depart.DepartTime > Arrival.ArrivalTime) AS DepartTime
>>FROM Arrival;
>>
>>If subqueries are new, see:
>>    http://allenbrowne.com/subquery-01.html
>>
>>Step 3: Calculate the difference.
>>Once you have the subquery returning the correct date time value, use
>>DateDiff() to calculate the difference in minutes. It will be something 
>>like
>>this:
>>
>>SELECT Arrival.ClientID,
>>Arrival.ArrivalTime,
>>DateDiff("n", Arrival.ArrivalTime,
>>    (SELECT Min(Depart.DepartTime) AS MinDepartTime
>>    FROM Depart
>>    WHERE Depart.ClientID = Arrival.ClientID
>>    AND Depart.DepartTime > Arrival.ArrivalTime)) AS Minutes
>>FROM Arrival;
>>
>>In the query, it's best to keep the duration as a number, i.e. in minutes 
>>or
>>seconds, or divide by 60 for hours and fractions of an hour. If you need 
>>to
>>display it as hours:minutes in a report, this article explains how:
>>    http://allenbrowne.com/casu-13.html
>>
>>> im currently making a database that logs time for how long people are
>>> waiting
>>[quoted text clipped - 5 lines]
>>> about queries in databases so can you type in plain english, thanks in
>>> advance.
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200801/1
> 

0
Allen
1/10/2008 6:10:31 AM
ok my Exit_Card Table is Time Exited

and my new code is

SELECT Entry_Card.[Card Number],
 Entry_Card.[Arrival Time]
FROM Entry_Card;
  
( SELECT Entry_Card.[Card Number],
Entry_Card.[Arrival Time]
  (SELECT Min(Exit_Card.[Exit Time) AS MinExitTime
  FROM Exit_Card
  WHERE (Exit_Card.[Card Number] = Entry_Card.[Card Number])
  AND (Exit_Card.[Exit Time] > Entry_Card.[Arrival Time])) AS TimeExited
FROM Entry_Card; )

SELECT Entry_Card.[Card Number],
Entry_Card.[Arrival Time],
DateDiff("n", Entry_Card.[Arrival Time],
   (SELECT Min(Exit_Card.[Time Exited]) AS MinDepartTime
   FROM Exit_Card
   WHERE Exit_Card.[Card Number] = Entry_Card.[Card Number]
   AND Exit_Card.[Time Exited] > [Card Entry].Arrival Time)) AS Minutes
FROM [Card Entry];

so is that all i need and what do i do with it just paste it into one SQL
view Queries page

Allen Browne wrote:
>1. Use square bracket around names that contain spaces, e.g. [Exit Time]
>
>2. In the Exit_Card table, is the field named [Exit Time] or [Time Exited]?
>
>3. Your middle query won't show the TimeExited correctly.
>The subquery is part of the SELECT clause, so before the FROM clause.
>
>Try:
>SELECT Entry_Card.[Card Number],
>Entry_Card.[Arrival Time]
>   (SELECT Min(Exit_Card.[Exit Time) AS MinExitTime
>   FROM Exit_Card
>   WHERE (Exit_Card.[Card Number] = Entry_Card.[Card Number])
>   AND (Exit_Card.[Exit Time] > Entry_Card.[Arrival Time])) AS TimeExited
>FROM Entry_Card;
>
>Once that's working, you can work on the DateDiff() part.
>
>> Thanks For The Help,i just wanted to check if my code was right
>>
>[quoted text clipped - 77 lines]
>>>> about queries in databases so can you type in plain english, thanks in
>>>> advance.

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

0
Phoenix2520
1/10/2008 6:26:16 AM
Yep: looks like time to start testing it.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Phoenix2520 via AccessMonster.com" <u40527@uwe> wrote in message
news:7dfcbe7b3a721@uwe...
> ok my Exit_Card Table is Time Exited
>
> and my new code is
>
> SELECT Entry_Card.[Card Number],
> Entry_Card.[Arrival Time]
> FROM Entry_Card;
>
> ( SELECT Entry_Card.[Card Number],
> Entry_Card.[Arrival Time]
>  (SELECT Min(Exit_Card.[Exit Time) AS MinExitTime
>  FROM Exit_Card
>  WHERE (Exit_Card.[Card Number] = Entry_Card.[Card Number])
>  AND (Exit_Card.[Exit Time] > Entry_Card.[Arrival Time])) AS TimeExited
> FROM Entry_Card; )
>
> SELECT Entry_Card.[Card Number],
> Entry_Card.[Arrival Time],
> DateDiff("n", Entry_Card.[Arrival Time],
>   (SELECT Min(Exit_Card.[Time Exited]) AS MinDepartTime
>   FROM Exit_Card
>   WHERE Exit_Card.[Card Number] = Entry_Card.[Card Number]
>   AND Exit_Card.[Time Exited] > [Card Entry].Arrival Time)) AS Minutes
> FROM [Card Entry];
>
> so is that all i need and what do i do with it just paste it into one SQL
> view Queries page
>
> Allen Browne wrote:
>>1. Use square bracket around names that contain spaces, e.g. [Exit Time]
>>
>>2. In the Exit_Card table, is the field named [Exit Time] or [Time 
>>Exited]?
>>
>>3. Your middle query won't show the TimeExited correctly.
>>The subquery is part of the SELECT clause, so before the FROM clause.
>>
>>Try:
>>SELECT Entry_Card.[Card Number],
>>Entry_Card.[Arrival Time]
>>   (SELECT Min(Exit_Card.[Exit Time) AS MinExitTime
>>   FROM Exit_Card
>>   WHERE (Exit_Card.[Card Number] = Entry_Card.[Card Number])
>>   AND (Exit_Card.[Exit Time] > Entry_Card.[Arrival Time])) AS TimeExited
>>FROM Entry_Card;
>>
>>Once that's working, you can work on the DateDiff() part.
>>
>>> Thanks For The Help,i just wanted to check if my code was right
>>>
>>[quoted text clipped - 77 lines]
>>>>> about queries in databases so can you type in plain english, thanks in
>>>>> advance. 

0
Allen
1/10/2008 6:54:41 AM
when i try to save it it said Characters Found after end of SQL statement?

Allen Browne wrote:
>Yep: looks like time to start testing it.
>
>> ok my Exit_Card Table is Time Exited
>>
>[quoted text clipped - 48 lines]
>>>>>> about queries in databases so can you type in plain english, thanks in
>>>>>> advance.

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

0
Phoenix2520
1/10/2008 7:32:18 AM
Okay: back up and start building the query a bit at a time, testing each 
part, instead of trying to build it all together.

I'm not sure if you realised that I did change your SQL statement in the 
post before last.

You need to do your own debugging here. We don't have your tables, so can 
only offer examples.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Phoenix2520 via AccessMonster.com" <u40527@uwe> wrote in message 
news:7dfd523d78d73@uwe...
> when i try to save it it said Characters Found after end of SQL statement?
>
> Allen Browne wrote:
>>Yep: looks like time to start testing it.
>>
>>> ok my Exit_Card Table is Time Exited
>>>
>>[quoted text clipped - 48 lines]
>>>>>>> about queries in databases so can you type in plain english, thanks 
>>>>>>> in
>>>>>>> advance. 

0
Allen
1/10/2008 8:09:43 AM
Reply:

Similar Artilces:

Operation timing out when collecting POP mail
Dear all, When my Outlook 2003 tries to collect mail, i get the following error: Task 'Useraccount - Receiving' reported error (0x8004210A) : 'The operation timed out waiting for a response from the receiving (POP) server. If you continue to receive this message, contact your server administrator or Internet service provider (ISP).' This is a stand alone machine that collects over my ADSL line. The following has been done: 1) Checked with ISP that account is still active 2) Reinstalled Outlook 2003 as well as Office 2003 3) Used outlook express from another machine and mai...

Pivot Tables #45
I need to ask few question on pivot table: First is when i make file workbook protected and hide master sheet on which table is based, file gets corrupted... Secondly, can i hide pivot functions and toolbars for users. Like when user uses Pivot table, he can know what are the fields in source, formatting changes etc, Can i disables these functions and allow users only drill down or options select or filtering functions.. Please advice Hi Sandy Firstly, hiding the sheet upon which the Pivot Table is based before saving shouldn't corrupt your file. There must be some other reason. ...

Linked Table not showing all data
I have a linked table in my database because I was getting and error everytime I tried to import the table. Now I have about 4 columns of data in the linked table missing completely. The data in the excel file is perfectly fine, its just not showing up in the access linked table. What is going on. Thanks On Tue, 18 Mar 2008 13:49:03 -0700, Ayo <Ayo@discussions.microsoft.com> wrote: >I have a linked table in my database because I was getting and error >everytime I tried to import the table. Now I have about 4 columns of data in >the linked table missing completely. The da...

Ribbon run time error
I am using Office 2007 Entreprise. I USED to have access to an add-in named "Access Outlook Add-in for Data Collection and Publication". That has now disappeared and I can't get it back (I have posted to other forums about this issue but with no satisfactory responses). So, assuming I can no longer use this add-in I am now faced with a two-fold problem. a) The add-in created a subfolder in my Outlook 2007 Inbox. Can I now delete that as I can't get the add-in to work and can't find out how to re-install it)? b) EVERY time I now double-click a mess...

user can't receive external mail, times out
I'm trying to figure out why one of my users isn't receiving external messages on exchange 2003. Not sure where to start. is there an NDR received by the sender? does the user have a valid smtp address? "jln" <jln@discussions.microsoft.com> wrote in message news:DB9C8732-D093-4DCA-834C-1E948FC7CB51@microsoft.com... > I'm trying to figure out why one of my users isn't receiving external > messages on exchange 2003. Not sure where to start. ...

how to retain data in table only but do not graph of that data
i want to prepare chart from my data table.there are year wise different field along with the total of each field. how to retain total of each field in the table the chart but I don't want the total of each field in chart. kindly respond me at the earliest. New Delhi If I understand the question correctly - just select the required data with the mouse before you make the chart. best wishes "sovaran singh" <sovaran singh@discussions.microsoft.com> wrote in message news:4AF1EBDB-ADEA-42AE-8929-580BE51A1EF7@microsoft.com... > i want to prepare cha...

How to import plain text into workable tables/cells?
I have several rather extensive text files (extracted from an antiquated local database) that I wish to import into either Access or Excel as functional databases. So far I've had the best results importing into Excel, as this at least manages to divide the info into several fields somewhat, although still not nearly as well as I'd like. Several elements get crammed together in one cell when I choose to import seperately. In Access, the results are even worse and I can't even manage to make decent cells out of the info. Most of the time the info gets imported into one c...

Function of colon between table names when query in design view
I am looking at a field from a query that is in design view. Inside the field are two field names from the same table which are separated by a colon. For example, Old and New are both fields from tblReplace and appear like this Old:New in design view. I believe the colon concatenates the field Old and New into a new field when you change the query view to datasheet view. Am I correct? Also, in a completely separate question if you are looking at a query in design view and there is a field in the (Field:) row, a table in the (Table:) row, and Group By in the (Total:) row wh...

Can I view separate worksheets at the same time?
I need to view two worksheets in the same workbook at one time, how can I go about doing this? Hi MarieMc, > I need to view two worksheets in the same workbook at one time, how can I go > about doing this? > From the menu: Window, New window. Then: Window, Arrange. Select an arrangement and check "Windows of active workbook" Regards, Jan Karel Pieterse Excel MVP www.jkp-ads.com Goto menu Window>Arrange..., a few options there. -- HTH RP (remove nothere from the email address if mailing direct) "MarieMc" <MarieMc@discussions.microsoft.com> ...

Pivot Table Drill Down Number Formatting
When you double-click a cell in the data area or a pivot table, Excel places the detail data summarized in the cell on a new worksheet. Unfortunately, the number formatting on the new worksheet doesn't seem to follow the formatting of the source data. I can use the Format, Style, Number to make the formatting the same for each numeric field, but I don't want the same format for each numeric field. For example, I want some of the columns to be comma with 3 decimal places and others to have no decimal places just like they are in the source data for the pivot table. Any sugges...

Football League Table Spreadsheet #2
Barbara, That is exactly what I am after. I have downloaded the file but I can' seem to locate the additional maintenance tab on the toolbar fo amending it! Regards...Gracus -- gracus ----------------------------------------------------------------------- gracust's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1591 View this thread: http://www.excelforum.com/showthread.php?threadid=27395 Gracust, When I loaded it I looked under tools in the menu and there was an additional item at the bottom 'League results' with other options from there. If you...

re: new rows are not refreshed in Pivot table
I have a new sheet to pivot the source sheet. When the source shee refreshs to have new rows, I can't see the new rows in the pivot shee after refresh. Why? Thanks!! -- Message posted from http://www.ExcelForum.com You most likely need to extend the range used by the pivot table to include the new rows. Click in the pivot table and select Data, Pivot Table report and then the back button. You can then change the pivot table range. Bob Flanagan Macro Systems http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "ewang1205 >" <<...

LockBox Table
Does anyone know which table writes the LockBox Invoice type format configuration too? Thanks ...

2002 to 2003 No Pivot Table?
Running my Access 2002 mdb in Access 2003 Pro SP1. I want to create a Pivot Table but don't have the PivotTable View Button. How do I create a Pivot Table? Can't find the answer in Acess Help, and my Access 2002 book is clearly of no help. Help, please. Traderv -- --------------------------------------------------------------------- "Are you still wasting your time with spam?... There is a solution!" Protected by GIANT Company's Spam Inspector The most powerful anti-spam software available. http://mail.spaminspector.com ...

Access 2007 Table Templates
I am working on an assignment for school and it is requiring me to generate a table from scratch and then create a contacts table from the templates. When I click on the create tab and then under the table group, I click on the table templates, then contacts. When the table comes up it is a blank table with no predefined fields. Out of curiosity, I also clicked on the other table templates (tasks, issues, events, and assets) they all came up the same way. I have tried to “repair” MS Office Enterprise 2007 from my disk, and it is still the same. Is there another step I need t...

'formula 1' appears in Pivot Table after grouping
Hi, when I use 'Group Selection' inside a pivot table, the words 'formula 1' suddenly appear inside the data I am trying to group. and then if I am trying to group say 10 rows of data, it only highlights 9 of them plus the row beneath with the word 'formula 1' as the 10.. and the 10 data value appears in the row below, but is excluded from the group. Any guidance please? Thank you. What is in the field you are trying to group and what type of field is it - row, column or data field? Usually when you apply the Group Selection command the word Group1 appears ...

date selections from one table to another
Is there a way I can have table1 with only 2 fields (Begin Date) and (End Date) and have a query that asks for all the records from table2 where field (Date of Service) is between (Begin Date) and (End Date) from Table1?? If so, how?? The query won't be updateable but you could try: SELECT Table2.* FROM Table2, Table1 WHERE Table2.[Date Of Service] BETWEEN Table1.[Begin Date] And Table1.[End Date]; -- Duane Hookom Microsoft Access MVP "Schon" wrote: > Is there a way I can have table1 with only 2 fields (Begin Date) and (End > Date) and have...

Quotes update problem
I'm using Microsoft Money 2004 Standard Edition, Canada. Today is Oct 3rd, I ask for a quotes update. Everything appears fine EXCEPT the reported summary states "your call was successful 9/22/05 2:27PM" - the last quote I have is for almost 2 weeks old. I bought this program Jan 20th 2004 so don't appear to have not consumed my 2 years of online services. Any tips as to whats going wrong here. PS, how do I confirm how much time is left on my subscription? Thx On 2005-10-03, Green <user@domain.ca> wrote: > Any tips as to whats going wrong here. Don't k...

After XP sp2 upgrade, Outlook 2003 crashes every time a Contact is saved or links to other contacts added to it
The subject line says it all. On my Toshiba Tablet PC, "After XP sp2 upgrade, Outlook 2003 crashes every time a Contact is saved or links to other contacts added to it" (via the Contact's "Contacts..." link button). The error signature is always the same: AppName: outlook.exe AppVer: 11.0.6353.0 AppStamp:408f2937 ModName: mso.dll ModVer: 11.0.6360.0 ModStamp:40d147de fDebug: 0 Offset: 0001f0e3d And I always send a report to Microsoft (about 115K each), but there is no response giving a fix as there sometimes is. With www.Microsoft.com -> Windows Update, I ha...

Offline Terminal DataBase + Custom Table
Hello, I use the terminal database offline withe my POS (rms 1.3R). I create my database with Pos administrator, specifie the server for offline database. When I generate Z report , the database synchronize items and customers. But my problem is : I have a custom Table with Price connected to the Item's table, and this table doesn't synchronize with the offline database. Is it a solution to my problem ? or a tips ? Thanks Sorry for my english. Math ...

Access - Macro
Hello, I have 2 databases. DB1 and DB2. DB2 is more or less like a rater. I give the input to the DB2 and DB2 produce the output back to me. In DB1, I have a macro (plus calling vba code) that will 1. send the input file to DB2 2. Run the macro in DB2 (part of the macro in DB2 will run a Make table query which will make an Output table in DB1.) 3. Update the result from Output table into another table in DB1. The problem I have is the macro failed at step3. it said the table doesn't exist... I ignored the message, and run the query of step 3 manually and it works f...

League table automatic sort/update
Hi All I'm just getting up to speed with Excel and as an exercise I'm trying to implement a simple league table. What I'd like to know is how to create 'dynamically' a ranking table like the one shown below. Pos Player Points 1 Tom 124 2 Linda 122 3 Harry 107 4 Jayne 100 4 Bob 100 5 Steve 89 6 Mark 88 7 John 80 8 Angie 77 9 Andrew 71 The table is sorted on the points column, the values for which are referenced from another sheet. Ideally I'd Like the table to be sorted automatically as players points total change(based on calculations In another sheet). ...

1 job, multiple customers. How do I make an accurate table?
My company is a supplier, so we submit our bid numbers to multiple contractors for a single job. There are about 70 jobs a month. Example. Job: Hostpital... Customers... apple, orange, and kiwi How can I accuratly input this information (multiple customers for 1 job) per job without messing up all my other jobs that I bid for that month? I will gladly email my database Your target paradigm is a many-to-many relationship. To model it requires a junction table which will have the Primary Keys from tblCustomer and tblJob as Foreign Keys. You might name the new table ~tblJobsCustomers. Th...

High CPU utilization 97-100% all the time with outlook.exe
Running W2K with SP3 and Office 2000 with SP3. The CPU is high all the time no matter what I am doing. I am not sure when it started, but there is nothing obvious that I have done different on the system. Thanks, Larry Larry, I am having the exact same problem! I am running Office 2K on a Dell desktop w/ 129MB RAM running Win XP. I have used the "detect and fix" function in Outlook, but it only improved the CPU utilization of Outlok from 98% to 96%. If you get a tip, please forward to me. Best regards, Chris >-----Original Message----- >Running W2K with SP3 and Offi...

what is the time source of domain member server when ntpserver defined
Experts: I know by default member server syncs time from domain hira. Now with hklm\....w32time\parameter\type set to [AllSnyc] and config\ntpserver set to [a manual list], would the member server still try getting time from DCs? If yes before or after the manual list? "strongline" <johnlan@gmail.com> wrote in message news:d80001c0-8090-4750-ad00-f02384ec1cf5@e27g2000yqd.googlegroups.com... > Experts: > > I know by default member server syncs time from domain hira. Now with > hklm\....w32time\parameter\type set to [AllSnyc] and config\ntpserver > s...