Comparing Information

Heya im pretty new to access.

I have a table which is a list of names (all the people in my office) Called 
'Names'
I have another table which has a list of dates for a month. Its filled in 
with people who are absent on what days for that month. This is called 
'Absence'

I want to create a way where it subtracts the names that are absent on that 
day, for the whole to leave me who isn't absent..... 

E.G.
   Table 1                                     Table 2 
'Names'                           'monday 1st' 'Tuesday 2nd'
A                                            A                 B
B                                            B 
C
D


So i would get another table that shows 

Monday 1st , Tuesday 2nd
    C                       A
    D                       C
                             D

Is it possible , if so how?
0
Utf
11/9/2007 8:30:02 AM
access.queries 6343 articles. 1 followers. Follow

1 Replies
573 Views

Similar Articles

[PageSpeed] 6

If table2 is like:

table2                             ' table name
TheDate            Who     ' fields name
monday 1st,        A
monday 1st,        B
thuesday 2nd,     B        ' data


Just to be on the safe side, we also need a table with all the dates covered 
by the exercise:


table3        ' table Name
theDate    ' field name
monday 1st
thuesday 2nd
wednesday 3rd    ' data



Then


SELECT a.Name, a.theDate
FROM  (SELECT name, theDate FROM table1, table3) AS a
        LEFT JOIN table2
        ON a.name=table2.name  AND a.theDate=table2.theDate
WHERE table2.name IS NULL
ORDER BY a.theDate


should do. The ORDER BY clause is just to 'regroup' the data in order of 
date.



Hoping it may help,
Vanderghast, Access MVP



"Tobes" <Tobes@discussions.microsoft.com> wrote in message 
news:103428FD-2104-4974-A533-151E8A5F3C3F@microsoft.com...
> Heya im pretty new to access.
>
> I have a table which is a list of names (all the people in my office) 
> Called
> 'Names'
> I have another table which has a list of dates for a month. Its filled in
> with people who are absent on what days for that month. This is called
> 'Absence'
>
> I want to create a way where it subtracts the names that are absent on 
> that
> day, for the whole to leave me who isn't absent.....
>
> E.G.
>   Table 1                                     Table 2
> 'Names'                           'monday 1st' 'Tuesday 2nd'
> A                                            A                 B
> B                                            B
> C
> D
>
>
> So i would get another table that shows
>
> Monday 1st , Tuesday 2nd
>    C                       A
>    D                       C
>                             D
>
> Is it possible , if so how? 


0
Michel
11/12/2007 9:03:04 PM
Reply:

Similar Artilces:

Compare and align rows of multiple but differing columns of data
I have a table of 4 columns. One column has county names, one population and the other two have data relative to the county name for the given year. Each year a new set of 4 columns is generated. However, some but not all of the county names are in the subsequent year. Same thing for each successive year. How can I: 1) Match the counties from year to year so they align in a row with corresponding data? What happens if year being used for comparison doesn't have county appearing in later year? 2) Make master list of counties (in left column) for all that have appeared in the data sets...

Data Verification: Comparing Two Identical Tables in MS ACCESS
I am using Access for entering a large number of questionanaires and other tests all by hand and in order to reduce errors I am using manual first entry and second entry. I am looking for a way to compare the records in each table against one another and be able to view and edit the discrepancies. It would also be nice if there was a way to limit the results to a specific record. Does anyone have an idea on how it would be possible to make this happen in Access? I have tried the VBA approach using some complicated code I found but it is limiting and I would like to find a way to do it with mi...

No free/busy information could be retrieved #2
Recently migrated from 5.5 to exch 2003, once the 5.5 server was turned off, user's free/busy information in the calendar is not available, if the 5.5 machine is turned back on it is there. Does anyone know what I need to so that the info is available when the 5.5 server is off? -- Thanks ...

Compare & replace?
In Excel 2007, We have an entry screen for our bus work orders. It enables someone to put in the information and by a macro, it gets transferred to a database on another sheet in the same workbook. That entry screen is called “DataEntry” and in it, is cell (D4) for the current odometer reading. The cell to the right of it (E4) shows the last odometer reading for that bus using an =LookUp macro referring to another worksheet in the same workbook called “BUSINFO” , =LOOKUP(D3,BUSINFO!AA2:AA108,BUSINFO!AB2:AB108) The next cell to it’s right (F4) in the “DataEntry” screen will ei...

Additional queue Information
Hi, When I click on one of the SMTP queues in my exchange 2003 server it display in addition queue information as destination server does not exist. And it was not able to deliver mails to public folders. I was not able to understand what is this problem and how to solve it. Can some one please help me out in this. Quick Response is appreciated. Thanks in advance, Mallika. "Mallika." <Mallika.@discussions.microsoft.com> wrote: >Hi, > >When I click on one of the SMTP queues in my exchange 2003 server it display >in addition queue information as destination ...

Comparing integer lists
I have to compare two columns of integers in excel, and count how man of the integers in column 1 also appear in column two. The columns are sorted in decending order, and no number will appea twice in the same column. I can achieve this using a long macro but am trying not to do this. I have =COUNTIF(J12:J111,D12) working so that if the value in D1 appears within J12-J111 then a count takes place, but I have no ide how to make this now go onto comparing D13,D14,D15 etc... Thanks for any help! E -- Message posted from http://www.ExcelForum.com Ed =COUNTIF($J$12:$J$111,D12) and drag...

Compare and merge two workbooks?
I have two workbooks. Each contains a column of data (social security numbers.) I need to merge the workbooks and include data from one workbook (scores) to the other workbook. If there is no score for a particular ssn, the score needs to return a zero. I'm using Excel 2002. I figured I could use compare and merge but that option is grayed out. Any suggestions? Thanks. Heather ...

Compare (2) fields in (1) table
Grettings, I am working on a database analysis and need some assistance on building a specific query. I have an input table that was formed at a previous query that is composed of a few 100 records. In one column field I have "Meter_Number_1" and in the same table in a seperate column field I have "Meter_Number_2" What I need to do is for each row, to identify where the number for "Meter_Number_1" and "Meter_Number_2" are the same. In addition to that, a sperate query will be where they are not the same. I am starting off smal...

Can't Send from OWA
We have exchange 2003 enterprise edition. On campus we can send/receive email with outlook 2003. When off campus, we use OWA. We can receive email and can open and read our email, but when we try to send, it stops the Information Store and does not send. I have the store.exe to restart automatically no matter what and it does with no problem, but still can not send from OWA. Any ideas? Thanks in Advance, Carlis Burns CIO That's not good. I've never seen that problem. Is there anything in the event log? Sending mail causes an "OWA Scratch Pad" temporary message to...

deleting duplicate information within a wksheet
...

Import TIAA-CREF information
I notice that TIAA-CREF now states that they have financial statement output for Quicken 2002 and above. I decided to try to import that information into Money 2004 but found that it does not work. The file format is qfx which has an OFX section burried inside it. However when I import it the only theing that happens is MS Money figures out what the current balance is. It does no import any of the transactions, nor does it issue any error messages. What is a qfx file and does Money 2004 recognize and import information from one? Yes I do know about an ofx file and am not confusing the...

Protection/hide information
Good morning, If anyone has any useful web links for information regarding protection/hide information for 2000 & 2002 generally to broaden my knowledge that would be great. Thanks. Not really sure what you're looking for, but here's some info on worksheet/workbook protection: http://www.mcgimpsey.com/excel/removepwords.html In article <O1SJMW$VEHA.4064@TK2MSFTNGP11.phx.gbl>, "Kassie Alcorn" <marketing@qld.apollopatio.com.au> wrote: > If anyone has any useful web links for information regarding protection/hide > information for 2000 & ...

Formating cell by Comparing two cells for value
I have a column of data and I want to use conditional formating to compare the current cell with the one above. If the cell above is the same as the current cell I want to fill in the cell with a color. I have tried using an IF function in the condition formating but it does not seem to work. I tried =if(p9=p8) and set formating to fill the cell but it say invalid formula. This has to be simple but I am missing something. Thanks for the help. Hi Almost there. Just use =P9=P8 -- Regards Roger Govier AJK wrote: > I have a column of data and I want to use conditional...

Overly large public folder information store
I have an exchange 2000 server (SP3, all patches applied, only one server in the organization) that seems to have a serious problem in regards to the size of the public folder store. The total size of all public folders on the server (according to both the System Manager and Outlook's "folder size" command) is about 3.3GB. However, the actual size of pub1.edb is 5.1GB, and pub1.stm is 8.4GB. Deleted item retention is set to 1 day. Message journaling is enabled (and is what compromises the bulk of that 3.3GB) Quarterly, I copy the data from the public folder containin...

Using Office 2k3 functionality via Information Bridge Framework
Hi all, Firstly, I have overcome many obstacles by reading these newsgroups. Keep up the good work! My CRM installation is ready and I was confronted with the lack of front-end application coupling. Functionality like typing letters should be coupled to Word, as well as making a quote. In my opinion that is. Now I found the IBF (Information Bridge Framework) which should make my life easier. Although during install, I get the following error: "Microsoft CRM is not installed on this computer" Which is absurd, because it is installed and working properly! I followed the diffe...

Structure information for Publisher
Publisher should allow one to create a structure, just like Word. InDesign and QuarkXpress have this feature. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=ca8c46c5-be5b-4be0-8fa4-1c7b235d80f3&dg=mi...

Want to retrieve contact information from Windows Address Book
Hi, I want to retrieve contact information like First Name, LastName, City, etc. from Windows Address Book. Can any one help me out with some sample code/links/references to get me started with it. Thanks, -- Regards, Amar Kumar. Hi, Search in MSDN or search in Microsoft msdn site with keyword Q220600. Rgds, kas "Amar Kumar" <akumar@extentia.com> wrote in message news:#Mm7J41WDHA.888@TK2MSFTNGP10.phx.gbl... > Hi, > > I want to retrieve contact information like First Name, LastName, City, etc. > from Windows Address Book. Can any one help me out with...

Hyperlink
I am using a Excel Spreadsheet to track news releases. Within the spreadsheet I have hyerlinks that I can click on to go to a web site and review any new press releases. Today none of my hyperlinks work and I am getting the error message "cannot download the information you requested". Can anyone give me a hint on if there is a fix for this? I have more than 400 links that are not working. I've had that problem before and have no idea why hyperlinks become inactive after awhile. I'm afraid the only thing I can suggest is to select each hyperlink, press F2 and the...

Comparing and Deleting rows
I have two spreadsheets. One is like this: _Computer_|_Date_ The other is like this: Computer | Last Used[ The problem is that the two lists are different, but some computer exist in both. The first list is a complete list, the second one is subset of the first. How do I extract rows that are common into a separate spreadsheet -- Message posted from http://www.ExcelForum.com what do you mean by extract. from which file. do what after extraction. why 2 files instead of 1 file-2 sheets. >-----Original Message----- >I have two spreadsheets. > >One is like this: > >...

looking for link comparing Q07 to M07
Hello, Does anyone have a link comparing Quicken 07 to Money 07? Thanks Frank ...

Sensitive Information & Registry
I need to store some sensitive information in the registry, like a username and a password. What would be a good place to start? What do I need to know? Thanks! donot forget to encrypt those ..... "Ovidiu Blejdea" <blejdea.ovidiu@gmail.com> wrote in message news:OVyDCPxWHHA.3980@TK2MSFTNGP02.phx.gbl... >I need to store some sensitive information in the registry, like a username >and a password. What would be a good place to start? What do I need to >know? Thanks! If you want to encrypt data, the Crypto++ project can help you http://www.cryptopp.com/ "...

To compare info from two worksheets
I need to compare once column of data in first worksheet to two columns in second worksheet. What is the best way of doing this?? thank -- Message posted from http://www.ExcelForum.com What kind of comparisons are you making? What are you looking for - duplicates? differences? less than/greater than? in either one of the two columns? only in both columns? What are you comparing - text? dates? numbers? More info = more help. 8>) Ed "lisaoc >" <<lisaoc.16tlyr@excelforum-nospam.com> wrote in message news:lisaoc.16tlyr@excelforum-nospam.com... > I need to com...

comparing percentages (I'm stuck)
Okay. I'm stuck. I'm trying to compare our company's sales from this November to last November. I want to be able to say that this year's sales were ___% greater/lesser than the sales last year at this time. For example, this November's sales were $87,544.25 and last November's were $82, 247.79. How do I figure out what percentage higher the November sales this November are compared to November of last year? --- Message posted from http://www.ExcelForum.com/ if you simply divide last year by this year you will get about 6% increase. If last yr was 182k you woul...

How do I combine information from two separate spreadsheets
I have a list of thousands of SKU numbers (unique number identifing an item) with each sku having several adjacent horizontal cells with information about that SKU. On a seperate work sheet, I have another list of the same SKU numbers but with different information in the adjacent horizontal cells. How do I combine into one spreadsheet so that all of the information associated with a SKU number will be on one spreadsheet ? If the columns are IDENTICAL, just copy them. Or, You could use VLOOKUP formulas and then copy/paste values. Or a macro -- Don Guillett Microsoft MVP...

Information store #4
We have 5 exchange 2000 server with each server having a 60 gig store with 5,200 users. I would like to get each information store on the 5 servers down to at least 20 or 30 gig. Beacause it is very hard to do maitanace with the information stores being that big. I would appreciate any assistance. Thank You Since your stores are that big, you must be running Enterprise edition. Why not simply create a new Mailbox store and split up the mailboxes between the 2 stores on the same server? Once you move the mailboxes to the new mailbox store, if you wanted to reclaim the space on the fir...