Counting Matching Related Records

Sep 30, 2007

Hello there,

I have tables like so (simplified):

-=Holiday=-
HolidayID
Name
Date

-=Booking=-
BookingID
HolidayID
ClientID

What I would like to do is create a query which returns a list of all the holidays with a field showing how many bookings have been created for each holiday. This almost works:

SELECT Count(1) AS CountOfBookingID, Bookings.HolidayID
FROM Bookings
GROUP BY Bookings.HolidayID;


...but it does not display holidays where there are no bookings.

Is it possible to create one which will show all holidays even if there are no bookings?

Thanks!:cool:

View Replies


ADVERTISEMENT

Show All Records That Have More Than One Matching In A Related Table

Dec 16, 2005

Hi,
I've been trying to get this for ages now - both in the design view and in sql:

I have 2 tables - one called DrawingsRegister and a related one called DrawingRevisions. Each drawing has one or more drawing revisions. I want a query that will show each drawing (just once) that has more than one revision:


SELECT tblDrawingsRegister.DrawingNum, tblDrawingsRegister.DrawingName, tblDrawingRevisions.DrawingNum

FROM tblDrawingsRegister INNER JOIN tblDrawingRevisions ON tblDrawingsRegister.DrawingNum = tblDrawingRevisions.DrawingNum

WHERE ((Count([tblDrawingRevisions]![DrawingNum])>"0"));

Thanks for your time,
RCurtin.

View 1 Replies View Related

Counting Of Cases And Counting No Records

Jan 26, 2007

I have a report due the first of each week in which I need the cases open and cases closed for the previous week, the week two weeks prior and the 2007 and 2006 year to date on two different types of cases. I have a case management table with a field for Type of Case, date assigned and date closed that I uses in my queries. Presently I have two query, one that generates only Type 1 cases from the Case Management Table and another for Type 2. I then use the Type 1 Query in another query that limits the results for Type 1 cases to those opened last week, one for those open two weeks ago, one for 2006 YTD and one for 2007 YTD. In these 4 queries I have one field [Type of Cases] and I have the query count. I then do this for Type 2 cases and then go through the whole process to do Closed Cases. All my queries have criteria to automatically filter the dates to the time periods mentioned above. I then have one report query that I put all the number in for my report. This query has 16 fields with the numbers for each period, last week open and closed, 2 weeks open and closed, etc. I then generated a report that takes these numbers from my report query and puts it in a report format automatically. As you can imagine this takes some time to go through each query to generate these numbers, so I was wondering how I may do this differently. Also, I have experienced a problem when a field produces no records I get a blank sceen with nothing under the Count of column and get the same thing for my report. How can I fix this.

View 1 Replies View Related

Queries :: Mass Duplicate Main Records And Related Subform Records

May 29, 2014

In my simple database (attached), I need to mass duplicate Tasks and their Notes.

I have three tables: tbTasks (PK: Task_ID), tbNotes (PK: Note_ID), jtbTaskNotes (FKs: Task_ID and Note_ID). jtbTaskNotes is my many-to-many junction table that ties Tasks to Notes.

The main form (fmTasks), bound to tbTasks, has a subform (sbfm_TaskNotes) that displays notes associated with each Task. On themain form,you select which Tasks you want duplicated via a checkbox. The append query (quCopyTasks) will duplicate all tasks that have the checkbox checked. All good there. However, I can't figure out how to also duplicate each task's Notes.

I found Allen Browne's solution [URL] ....., but that only handles duplication of one record at a time, whereas I need to duplicate many records at a time (sometimes 10+ records). How do I go about duplicating multiple Tasks and their associated Notes?

Before you ask "why are you duplicating records?": There are times when tasks need to be re-accomplished and therefore need to have a new record. It's easier to duplicate records than it is to hand-jam everything again.

View 5 Replies View Related

Matching Records

Dec 22, 2004

A very elementary question - but I'd be grateful for an answer.

I have two tables (or perhaps two queries) each with a key field. If all is well, there should be complete correspondence between the two sets of records. That is, if there's a record with key 12345 in one table, there should also be a record with key 12345 in the other table.

I'm looking for the simplest way of checking whether or not this is the case, and, if it's not, detecting which records in one table are unmatched by any record in the other.

Will

View 1 Replies View Related

Return All Matching Records

Aug 6, 2005

I have a database called LettersDatabase this databse holds all the letters that have been made including the path to the doc. I use SSN to ID the letters to customers on the Contacts Database.

Contacts database also uses the SSN to id the contacts



I have a form that creates new letters for customers in this form I have listbox that queries the LettersDatabase for all matching records based on the forms contact SSN to see how many letters have been made for that customer.


The problem is that my listbox only shows the first record matching that SSN but there are more records in that LettersDatabase with the same SSN that I need to have diplayed on the listbox as well.

I may be writing the query incorrectly.
Here what I have for the query on the listbox

Like[Forms]![LetterMaker]![txtSSN]

I try adding (&"*") to the end of the query but that does not help.


If anyone out there has the solution to this problem it would be greatly appreciated

Thank you

View 3 Replies View Related

Delete Matching Records

Feb 27, 2006

Hi can anyone please help me out. How can I delete records from one table, where matching in a second table?

View 1 Replies View Related

Count Matching Records From A Subform. Please Help...

Mar 1, 2006

I need to have the sum of the "matching records" of a subform, exported to a variable of the main form, in order to use it in an if condition.

e.g. "IF a client has brought X? times the vehicle A for a service of type B, THEN do ..."
How do I get the X value in a variable within the main form which presents all activity for all clients (by means of a subform).

I hope this is clear.
Plaese help me, guys. You 've done it before, you're so great!

View 2 Replies View Related

Need Help With A Query For Finding Matching Records

Aug 5, 2005

I have two tables Table 1 and Table 2. Table 1 has 6 fields 175 records and Table 2 has 4 fields and 330 records. The first three fields in both the tables are the same. I need to find all records in the first table that have a matching record in the second table based on those first three fields. How can I accomplish this? Any help would be greatly appreciated.

Thanks

View 3 Replies View Related

Deleting Matching Records From Another Table

Dec 6, 2005

Hi

Apologies if there is a previous post that answers this - I've looked, but can't find anything that works.

I have two tables with identical structures. tblA contains a subset of the records on tblB, with identical values on all fields except ID. I need to remove from tblB all records appearing on tblA. I thought the following would work:

DELETE tblB.* from tblB
INNER JOIN tblA ON tblB.Field1 = tblA.Field1
AND tblB.Field2 = tblA.Field2
AND tblB.Field3 = tblA.Field3...

but I get "Could not delete from specified tables".

What am I doing wrong? Or is there an easier way?

Dave

View 2 Replies View Related

Need Count Query To Output 0 (zero) When There Are No Matching Records

May 21, 2007

I have a form where a user reviews information input by another user, once they have done this they sign it off by selecting their name from a drop down list. On this form there is a scrolling message which tells the user how many un-signed entries there are. This works fine until there are 0 (zero) entries to be signed off.

The scrolling message is linked to a count query which basically counts any records that does not have a name entered in the required field. I have done this with ' Not Like "*" '

Like I say it works when there is one or more entries to count, but as soon as there are none the query does not output a zero it is just blank. This is the problem.

I need some sort of statement to say if there are no matching records please display a zero.

Any help would be much appreciated!

View 1 Replies View Related

Forms :: Non Matching Records To Give Message Box?

Dec 11, 2013

I have two tables, categories and items. I have a form that is linked to the categories table and a subform that is linked to the items table. The user selects a category on the main form then an item to add to the category on the subform. (all of this is functioning correctly - but you need to understand for my question.)

Both the form and the subform have a text box that displays a record from their respective table, for the example we will call this color.

What I need to do is create code that checks the color text box from the category (main form) against the text box for the item (subform) and if they are not a match I need to display a message box - from where I can then add the necessary actions.

EDIT: note this is not a query on an entire table or 2 it is just comparing the two text boxes on the form and subform

View 5 Replies View Related

Queries :: Query That Returns No-matching Records

Jul 15, 2013

Suppose I have two tables with fields as follows:

Assigned Courses (Courses that employees should do)Employee number
Assigned Course Name

Fulfilled Course (Courses that employees have done)Employee number
Fulfilled Course

Now I need to do a query that tells me which employees haven't done. Is there anyway to do that that is not an exclude query?

View 4 Replies View Related

Forms :: Return All Records Matching A Set Criteria

Dec 3, 2014

I have a form that is bound to a query that returns all records matching a set criteria (namely, the field ApprovalDate is null); the idea is that the user clicks on a command button which gives information, and if the user chooses to continue (vbYes), then the field ApprovalDate has its value set as the current date, and the record is saved.

Next, I have put in DoCmd.Requery to requery the form and effectively go to the next record wanting approval - this appears to work fine, however I need to add something to the code that handles a null return from the requery - preferably a message box and then have the form close.

View 5 Replies View Related

Forms :: Return Number Of Matching Records

May 12, 2015

I have a datasheet form listing prospect information.

Each prospect is assigned a position, there can be more than one prospect with the same position.

I'm trying to get a text box to lookup the prospects position and search the remaining rows for matching positions and return the total number found.

This data does not need to be stored anywhere, just a reference value for users to look at.

View 4 Replies View Related

Queries :: Create A Query For Non-matching Records

Jul 24, 2014

I am trying to create a query in Access 2010 for records that don't match based on the following criteria. I have two tables with identical ID's and I need to do a comparison on the amount field between both tables and only return the records from one table displaying all fields plus the amount fields that do not equal. I tried this in the wizard unsuccessfully.

View 13 Replies View Related

Find - Not Finding All Matching Records, But Filter By Selection Does.

Jul 9, 2007

Hi All,

I have a strange problem that has me beaten.
I open an Access 2003 table, sort on field "Job Number", click the Find button and enter a job number.
I expect to find 30 records, but Find only hits 8 of them.
If I over-type the Job Number (with the same numerics), Find is then able to return this record.
If I place my cursor in ANY of the 30 records and click Filter by Selection, all 30 records are included.
I thought there may be a non-printable character or a space hidden at the end of the field, but I expect both Filter and Find would return the same records.
Even more strangely, when my client zips the MDB file and emails it to me, this behaviour does not occur on my PC.
Has anyone else comes across this behaviour? If so, can you please point me in the direction that will help me find a solution.
cheers,
Mark Chimes

View 3 Replies View Related

Create An Entry In A Table Based On Records Matching

May 23, 2006

Where a many to many relationships is resolved with a linking table...Is it possible to create an entry in the linking table where the two linked tables have the same value e.g. create a entry in the linking table where client table has same value as a job profile table i.e. both are in insurance. Therefore want to create an entry in the middle table with foreign keys from either link automatically

ta

View 1 Replies View Related

Queries :: Show All Fields With No Matching Records - One Table

Aug 14, 2013

How to get the following results using 1 table:

Field1 ID is an auto record ID, field2 ID is actually field1 ID assigned that record, in other words record 1 has a roommate (record 5) assigned to it, record 2 has a roommate (record 4) assigned to it

Table A

Field1 ID Field2 ID
1 5
2 4
3 6
4 2
5 1
6 8

Results records I'm looking to display for would be:
1 5
2 4
3 6
6 8

I only want to display all fields for records, but I don't want to show their matching record, so I want to display record1, but not record 5 because record 1 has record 5 as a roommate, want to display record 2 but not record 4.

View 5 Replies View Related

Related Records

Aug 28, 2007

I've been using an access d/b for ages, it's one of the template (Orders) databases included with access but modded to suit my own use. copy attached with customer details removed.

Last time I used it it worked fine but yesterday, when I tried entering order information, I get an error saying cant add or change a record because a related record is required in table orders.

Can anyone shed some light on the potential problem? Bear in mind I'm not an advanced user and have very limited knowledge on relationships in access.

Thanks in advance.

Marc

View 4 Replies View Related

First/Last Of Related Records

Oct 13, 2005

The "last" total in an Access query is unpredictiable. I have a Journal, with the main form/dataset being linked to the entries of that day. I would expect the 'last' total word to give me the last entry of that day, ie. what I ate for supper. But in the two qrys shown, it does the opposite. What is the most relable way to get the last entry of that day (ie. the one with the highest journalID)? :confused:

Sample Database attached.

View 7 Replies View Related

Min Value And Related Records

Oct 29, 2005

Hi,

i need a query to return the first orderdetailsID and related productsID, so that all the other products that are the same as oldest order can be manufactured together.
In access help menu it shows it would be something like using the criteria under productname1 field:
(SELECT [productname1] FROM [the next one to cut query] WHERE min([orderdetailsID]))
But this returns an error cannot have aggregate function in where clause
min([oderdetailsID])).

I've also tried with 2 queries, 1 with the min function and the using inner join, but can't get it to work.

Thankyou

View 4 Replies View Related

Related Records

Jan 18, 2006

Before I spend an age trying to get this to work I thought I'd consult some experts.

I have a main data table for recording all possible insurance claims against a company. What I want to do is create say 2 incidents but link them in some way. i.e. One of our drivers had a car accident (1 claim against our motor policy), but then the driver of the other car sues us(1 claim against our public liability).

I can create 2 seperate records and maybe have a field to highlight parent/child reference numbers, but is there anyway I could allow the user to click a button and see the related records simultaneously?

Cheers

View 2 Replies View Related

Forms :: Possible To Use A Command Button To Update Matching Records In A Form

Jul 7, 2014

Is it possible to use a command button (update) to update matching records in a form (Test)? I have a file (april.xlsx) in which the first column Cytogenetics ID (14-xxxx) is a unique identifier that matches a record in the database. The next column Result (NL-F, NL-M, F-VUS, M-VUS, A-M, A-F) and the date column next to it are what need to be updated in that record. In the database there is a field called Result that needs to be updated with
the text from column 2:

NL-F and NL-M = Normal
VUS-F and VUS-M = Variant of Unknown Sig.
A-M and A-F = Abnormal

There is also a Final TAT Date field in the database that needs to be updated with column 3 in the spreadsheet (Final TAT Date).So basically when there is a match with column 1 in april.txt to a record in the database, the Result field in the database is updated with column 2 of april.txt and the Final TAT Date field in the database is updated with column 3 of april.txt.

Cytogenetics ID Result Final TAT Date
14-0390 Normal 4/11/2014
14-0396 Variant of Unknown Sig 4/18/2014

View 1 Replies View Related

Help Find Related Records.

Apr 27, 2005

Hi
I'm have created a video database with a genre field. Is there a way that when i click a button a list is displayed of all the records which are the same genre?
Thanks
Aden

View 1 Replies View Related

Modules & VBA :: Training Matrix - Matching Listbox Selections To Table Records

May 6, 2015

I have a training matrix that lists employee names and certifications on various operations. The objective is to choose an operation and run a query to display everyone who is certified on that op. There are additional variables.

Code:
Name EMP ID OP1 OP2 OP3 OP4 OP5
-----------------------------------------------------------------------------
John Doe 526261 C C C
Bob Doe 555622 C C C
Sheila Doe 066600 C C C

Okay that looks about right for the data itself. The listbox has all the ops, you choose an op and hit a button and it goes and finds everyone who has a 'C' in that op column and pulls their record.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved