Correct Join Type And Query Statement

Feb 7, 2006

I've been struggling with this for awhile now. I've been trying to research a solution but I'm not even sure what to search for.

I have the following table structure:

tblContacts
Contact_ID (PK)
First_Name
Last_Name

tblBidPack
Bid_Pack_ID (PK)
Bid_Pack_Description

tblDistributionList
List_ID (PK)
Bid_Pack_ID (FK) (1-M)
Contact_ID (FK) (1-M)

What I would like to do is create a query to display all of the contacts from tblContacts that are not associated with a certain Bid_Pack_ID. My end goal is to create a form where I enter Distribution List Information. I want a listbox on the left with all of the contacts from tblContacts and a listbox on the right with contacts that have been selected for a particular Bid_Pack. When the user double clicks an item on the left it should show up on the right and be removed from the left. I'm looking for the same functionality as when you use the design wizard and you choose fields to include/exclude.

For example, let's say I want to add contacts for distribution list "A". My thought is the box on the left would show all the contacts from tblContacts that aren't selected for distribution list "A" and the box on the right would show all of the contacts that are part of distribution list "A". As you double click an entry it would perform the appropriate record creation/deletion and each box would be required.

So back to my original question, what is the correct method (relationship, join, querydef) to select all of the contacts from tblContacts that haven't been assocatied with a particular bid pack?

Thanks for any help you can provide. Even a nudge in the right direction would be appreciated.

View Replies


ADVERTISEMENT

Query Using Joins To Disply All Records From Both Sides Of Join Type

Apr 14, 2008

I have a list of PC SN#s in tbl1;
I have a list of other PC SN#s in tbl2
I want to display ALL records of tbl1, AND ALL records of tbl2, matching up the SN#s (where there is a match) - but I want to display all records whether they match or not.

How can I accomplish this?

View 1 Replies View Related

Update On A Query With A Join Statement

May 23, 2005

Hi,

I am trying to do an update on a table with a query which has a join as follows but getting an error:

UPDATE RoleObjects
SET AccessType = 1
FROM RoleObjects
INNER JOIN Objects On Objects.ObjectID = RoleObjects.ObjectID
WHERE (RoleObjects.RoleID <> 1) AND (MID(Objects.ObjectName, 4, 2) = 'SR')


I tried to debug the code and found that the following part of the code with the Select clause works fine.

SELECT *
FROM RoleObjects
INNER JOIN Objects On Objects.ObjectID = RoleObjects.ObjectID
WHERE (RoleObjects.RoleID <> 1) AND (MID(Objects.ObjectName, 4, 2) = 'SR')

Do u have any suggestions.. ??

Thanks a lot for your time.

View 1 Replies View Related

General :: Cross Join To Create Ledger Type Query From 4 Tables

Apr 2, 2013

I have 4 queries in which data needs to be connected from the date and shown as a single date showing each sections entry in a row and a cumulative total is maintained as the balance .

See the attached image ...

View 7 Replies View Related

Queries :: Update Statement For A OUTER JOIN Select Statement

Feb 12, 2014

I have the following Select Statement:

SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],

[code]....

Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?

View 2 Replies View Related

Correct Join - Not Bring Required Data Back?

Jan 21, 2008

Hi ,

recently posted a thread about a union query. I have got over that hurdle now but face a new problem which is:

When I have created a right join to bring back data from a table, if there is no data it will leave the fields blank. Is there any way I could bring back the row data of the same table.??

Hope some one could suggest something.

Thanks again.

View 9 Replies View Related

Please Correct This Statement

Mar 31, 2005

Dear All

It may be a very dump question but I can't get my head around it.

I have a MainForm and then a subform Form1 on it.

There is a command button cmd1 on subform Form1. Now I want to access cmd1 through another command button cmd2 on a different form Form2. How would I design the statement?

I have tried the followings on OnClick event of cmd2 on Form2

Forms!MainForm.Form1.cmd1.visible = true

Forms!MainForm.cmd1.visible=true

Forms!Form1.cmd1.visible=true

Forms!MainForm.Subform.Form1.cmd1.visible=true

Nothing works. Any suggestions?

Thank you

:confused: :confused:

View 3 Replies View Related

Convert General Number To Correct Year Date Data Type

Jan 10, 2014

In a query I would like to extract the last ten years. This is what the data looks like:12/13 (Data type = text)(Short for 2012/2013 which actually represents 7/1/2012 to 6/30/2013.)

I am able to extract the "12" and turn it into the general number 2012 using: ("20" & Left([TAXYR],2))*1..But how can I convert that to the data type-date so I can include those records in the past ten years from todays date? I was trying to use DateAdd but I think the problem is the data type and where converting to date gives me "1905" or "9/##/1905." I get why it does that, but is there a workaround? Maybe adding 39,785 days?? (2014-1905)*365

View 4 Replies View Related

Reports :: Changing Numbers To Correct Date Format - IIF Statement

Dec 17, 2013

How to get this expression to work? It works by changing the numbers to correct date format, however, if the field is null, I want a blank to appear instead of "type"

=IIf([DATE_APPLIED_X] Is Null,"""",CDate(Mid([DATE_APPLIED_X],5,2) & "/" & Right([DATE_APPLIED_X],2) & "/" & Left([DATE_APPLIED_X],4)))

Report.JPG

View 10 Replies View Related

Join Properties Cause Type Mismatch

May 29, 2007

Okay, I apologize, I'm not extremely Access knowledgeable so bear with me.

I have an Access database I created which I thought was working okay, then I realized it was missing one vendor because we haven't started writing checks to that vendor yet and probably won't for a while.

I am using a make-table-query to create a table that combines data from two linked tables. The two linked tables have a matching field (VendorID) one linked table is "History" and the other table is "Master" (these tables are SQL data from MS Great Plains if you need to know that).

Now my problem ... not every VendorID in Master table is present in History table.

I first created the join statement to say use all records in History and only those records in Master that match. This works without any error statements, however, I discovered there is a significant field of detail that is being omitted because a VendorID is in Master, but since no checks or invoices have been created for that VendorID it does not exist in the History table yet.

I decided to change the join properties to say "include all records in Master and only those records in History that agree". When I try to run the query I receive the error message "data type mismatch". I feel certain it is because several of the fields have no information to draw from on the vendors that don't exist in the History table.

What I need the query to do is go ahead and use the information from the Master table and when there is no matching VendorID in the History table use a zero for those fields.

I used to program and know a little C+. I understand "if" statements and feel like a sql if statement could work around this (for example, if History.VendorID does not exist zero, else History.[fieldname], but I don't know how to go about doing it. Any help for a novice would be much appreciated. Thanks in advance

View 14 Replies View Related

Type Mismatch In JOIN Expression

Aug 15, 2005

I don't quite understand this error message, and therefore cannot begin to fix it. One of my forms will not open except in design view because of a "Type Mismatch in JOIN expression" error. Can someone please tell me where to look to fix it? Thanks.

View 5 Replies View Related

Relationship JOIN TYPE Dialog Window

Jan 28, 2006

I have created two table in my database listed below

tblEmployee
EmployeeID{Primary Key, Auto Number}
FirstName
LastName
PositionTitle

tblWorkHistory
EmployeeID {Foreign Primary Key}
Organization {Primary Key}
Position
StartDate
EndDate

I am creating a one to many relationship with the tblEmployee EmployeeID and tblWorkHistory EmployeeID
because one Employee can have many past jobs

where I run in a cloud is that in access under the edit relationship dialog window it has an button called JOIN TYPE when I click on that button it open the Join Properties dialog window and presents me with three options

1. Only include rows where the joined fields from both tables are equal

2. Include all recoreds from the 'tblEmployee' and only those records from 'tblWorkHistory' where the joined fields are equal

3. Include all records from 'tblWorkHistory' and only those records from 'tblEmployee' where the joined fields are equal

Can someone explain what access is trying to ask me as it pertains to my example. I am new to access to a basic answer would help me with future relationship issues and a better understand of the concept


Thanks

View 2 Replies View Related

Change Data Type/ Join Mismatch

Apr 30, 2006

Looks like mismatched join types has caused others plenty of headaches in the past!!
I am trying to create a query that connects 2 separate areas of our factory.
The first table (Table 1) has a field called prod_code which is a text field and this code (5 digit number) describes a manufactured product.
The next table (Table 2) also has a field called prod_code which is also a text field, is a different 5 digit number, and describes the same product but after packaging.
There is a 3rd table which I want to use to link both of these tables, but in table 3:
table 1. prod_code = table 3.item_code, and is a long integer. and table 2.prod_code = table3.item_code_prnt, also a long integer.
I need to be able to use table 3 to correlate data from tables 1 and 2.
I suspect I need to use Clng or similar but am unsure how to apply it.
Have been trying to construct an expression but continually get error (bracketing error, join mismatch etc, etc)
Any clues?

View 3 Replies View Related

ADO Using An Sql Statement To Join Tables

Sep 5, 2005

Hi there

I am hoping someone can give me a few pointers on how to get started on what I am trying to achieve....

I have 2 tables that form a 1 - to - many relationship.

I have designed a form that I wish to show fields from both tables.

Using ADO I would like to be able to open the form and be able to see my desired fields in the same form.


Can anyone provide any ideas on where I can get started.


Thanks for your help!!

View 3 Replies View Related

Please Help Me With This Simple Join Statement.

Oct 26, 2005

Hi.

Select Materials.ID, Materials.AvailableUnits, Substances.Name, Suppliers.CompanyName FROM Materials Right Join Substances on Materials.Substance = Substances.ID

This works in joining the substances.name (from substances table) with the value stored in materials.substance (which is ID of substances.ID)

How can I add another Join to join Suppliers.CompanyName with suppliers.ID. The current Materials.Supplier is a key of Suppliers.ID which holds the value of Suppliers.CompanyName. I've tried this a million times and as soon as I insert a second join, no matter what format I get "syntax error in join statement".

PLEASE PLEASE help!

View 3 Replies View Related

SQL Outer Join Statement?

Dec 14, 2007

Hi,

I have a problem about SQL outer join

I have two tables

-----------
table one

id value1

1 a
2 b
3 c

-------------
table two

id value2
2 d
4 e
5 f

---------------------
I like get the results

id value1 value2
1 a 0
2 b d
3 c 0
4 0 e
5 0 f

* if no value put 0 .I am using SQL Server 2005 now

I try left join ,right join and full join, but I can not get the results.Please help me.

Thanks for help

Mark

View 1 Replies View Related

Join Two Fields In Select Statement Results

Apr 12, 2006

I'm sure this is easy to do, but I'm not sure how to word my search criteria when trying to look for a solution.


Let's say that I have 2 fields in MyTable: FirstName, LastName.

I've been trying to use the following query, but obviously it does not work:
Select (FirstName + ' ' + LastName) as MyName from MyTable.

I would like to have the following data returned to me in the following format:
John Doe
Jane Doe
Jim Doe
Jill Doe

Thanks,
CRhodus

View 1 Replies View Related

Subform Correct Answer Adds To Number Correct In Main Form

Mar 2, 2012

The code I have is.

Code:
Private Sub Command26_Click()
If Forms![test site]![prp test].Form.[A Right Answer] = -1 Then
Forms![test site]![number correct] = Forms![test site]![number correct] + 1
End If
DoCmd.FindNext
End Sub

Then when clicked it checks a yes/no box to see if "A right Answer" is the correct yes. Then it should pop to the main form and take the number correct cell and add one to it. I am trying to get the record to go to the next record inside the sub-form but docmd.findnext seems to be wrong too.

View 4 Replies View Related

Query Not Returning Correct Results

Jul 3, 2006

Hi everyone,

I'm having an issue with a query I created. When run the query requests an Artist Name. I enter this and it returns one result. However in actual table ther are two results for that artist. The only difference is that for the record that doesnt appear the field labelled "Gallery" has a zero value.

I have checked the table and the gallery field is not set to a required field so I dont understand why it wont show it as a result of the query.

Any ideas?

View 3 Replies View Related

Query Not Finding All The Correct Unmatched Records

Mar 31, 2006

I have several tables that I am trying to get information from:

Clock Number Table with fields: Clock #, Name, Title, Dept, Term, HireDate, & TransDate.

Completed Training with fields: Clock #, Data Completed, Doc # & Rev Level

Linked Table, Controlled Documents with fields: Doc #, Title, Effective Date & Rev Level

Linked Table, Distribution Table with fields: Doc #, Distribution, Rev Level & Effective Date.

Some of the tables have more fields that those listed, but they do not pertain to this query.

I am trying to create queries that will provide me with the names of employees who have not been trained on Controlled Documents that have been distributed to their department.

So far, I have been able to determine if no one has been trained, but if even one person has been trained, they do not appear on my list. I would like to know the Clock # and name of those that have not been trained.

I have created 3 queries to get this far. Query 1 is the Clock # Table and the Completed Training Table joining the Clock #. Query 2 is the Controlled Document Table and the Distribution Table joining the Doc #, Rev Level. Query 3 takes these queries and joins Doc # and Rev Level. and pulls records where the Rev. Level and Doc # is Null.

View 2 Replies View Related

Accessing Correct Combo Box Column From Query

Jan 4, 2008

Hi everyone,

I have a combo box that is populated from a table called tblProducts.
There are only three fields ID, Model and Description in this table.

ID is the primary key field and is an Autonumber

In the form when a user selects a product from the combo box the "Model" fields is displayed which is how I want it.

I am doing a query that should show the "Model field however when I run the query it shows the "ID" field which is useless information for the end user.

How do I show the correct field in the query?

View 7 Replies View Related

Correct Week Number Format In A Query

Jan 6, 2005

Wondering if anyone can advise...

The 1st/2nd Jan of this year were treated by most people as still being week 53 of 2004, and week commencing 3rd January being week 1 of 2005. In VB I have a formula to do this:

WeekNo = Format(Now(), "ww", vbMonday, vbFirstFourDays)

However my problem now is that I need to replicate this format in a Query within Access. The formula

WeekNo: Format(Date(),"ww")

treats 1st/2nd Jan as week 1, the 3rd as week 2 etc. Any idea how I can get it to treat 3rd Jan as week 1 instead?

There's a challenge for someone!! Help appreciated.

View 1 Replies View Related

Queries :: Query Correct But Report Wrong?

Aug 3, 2015

Query 1: has all the data (description, account, amount).
Query 2: sums Amount by Account No.
Query 3: combines Query 1 and 2.

Query 3 also has a join by the Account No. There is a report based on query 3.

Issue: I can run query 3 to equal 0 and generate the report correctly but when I generate the query to not equal 0 the report brings in all the data.

View 4 Replies View Related

Date Time Query No Pulling Correct Dates!!

May 11, 2006

Hi

I have a table with

"starttime", "endtime", "agent", "phonenumb" and a datediff calculation that calculates the diff between "starttime" and "endtime"

the "starttime" and "endtime" columns are in "10/05/2006 12:57:55" format, i.e. the column holds both the date and time

The data is stored in an sql DB and is accessed by an odbc link using Access 2003

I want to be able to run a query within access that lets me chose "agent" and a specific date or a range of dates.

Any help would be greatly appreciated

Thanks

Ben

View 1 Replies View Related

Queries :: 2 Inputs For Same Field - Query Result Not Correct

Sep 4, 2014

I have a query that allows 2 inputs for the same field ( batch no )

if you enter "pco0093" only you get 4 results
if you enter pco0094 only you get 4 results

both have the same part numbers is the results

When I run both together pco0093 and pco 0094 and use the "group" function on the field batch no I get 8 results ( as expected)

if I use the " where" function I expected to get the 4 results but with the " quantities doubling

What I am getting is more than 4 results but 5 with a part that was not on any of the single results

File attached ....

View 9 Replies View Related

Queries :: Select Query - Count How Many Records Not In Correct Format

May 7, 2013

So I have a table with around 20,000 records and there is a field which holds a phone number for each individual within the organization, I want to run a select query that counts how many are not in the correct format

The format is 10 digits and it must start with a 0

Using Access 2010

View 2 Replies View Related







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