Access :: Row Numbers In Join Query

Join On Fractional Numbers/String?

Jul 6, 2006

Greetings all:

I have a database tracking customers and inventory, and I've tried to add in a feature where I can meet customers needs with future inventory. Key to this process is three tables:

tblCustomer - containing my customer information
tblInventory - with inventory data including a part number
tblNeeds - a table I've created for this purpose, containing CustomerID and Part Number.

Here's the premise: when speaking with a customer, I may learn they want a product I don't currently have in inventory, or may have never had in inventory, but that I may get in the future. I do this "on demand" in the form of a report, and it works really well ... except:

I have found that several similar products from the same supplier have similar product part numbers. What I would like to do is be able to match my query on a fractional part of the part number. In all cases, the part number would be the same over the first six characters (in a text field). Any ideas how I might do this? In my current query, I use a join, but I can't do that for fractional strings....

I've attached the query I am currently using (and running a bound report from this query):

SELECT DISTINCT tblCustomer.CustID, tblCustomer.CoName, tblInventory.ProductDescription, tblInventory.ProductPartNumber, tblInventory.UnitsAvailable, tblInventory.ProductID
FROM tblInventory INNER JOIN (tblCustomer INNER JOIN tblNeeds ON tblCustomer.CustID = tblNeeds.CustomerID) ON tblInventory.ProductPartNumber = tblNeeds.PartNo
WHERE (((tblInventory.UnitsAvailable)>0));

And what I want to do is match the tblInventory.ProductPartNumber on the tblNeeds.PartNo by matching only the first six characters....

Thanks in advance,

Don

View 3 Replies View Related

Queries :: Returning Set Of Numbers From Custom Formatted String - Incorrect Use Of Join?

Mar 20, 2015

I suspect my design is flawed

Code:
SELECT tblData2.Prefix, tblData2.LineNum, tblData2.Year, tblComments.comment, tblComments.Address
FROM tblData2 LEFT JOIN tblComments ON tblData2.LineNum = (NumbersOnly([tblComments].[Address])
WHERE (((tblData2.MyYear)<1990))
ORDER BY tblData2.LineNum;

The NumbersOnly function returns a set of numbers from a custom formatted string. The above fails on the join (I think) but maybe there's another way of doing it?

View 4 Replies View Related

WHERE Clause On LEFT JOIN : Why Do I Get Join Expression Not Supported Message?

Nov 10, 2006

I've been toiling with the issue of WHERE clauses on the "Right" side of Left Joins. I'm aware that you need to use JOIN ON......AND.... rather than JOIN ON....WHERE.... if the WHERE relates to the Right Hand table.

I've even got an example in my DB where the above works, but now am struggling to use the same theory for other tables. Therefore, I went and created two Mickey Mouse tables to test the logic but am getting an error.

I have
Table 1 with one field called Field 1 - values A, B, C
Table 2 as follows

Field 1.....Field 2.......Field 3
A.............100
C.............200..........XXX

I hoped to have a query that finds all records on Table 1 and records on Table 2 where Field 1 matches on the two tables and Field3 = XXX

My SQL is
SELECT Table1.Field1, Table2.Field1, Table2.Field2, Table2.Field3
FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1
AND Table2.Field3="XXX";

but I get Join Expression not supported

What am I doing wrong?

Thanks
Andrew

View 7 Replies View Related

Create A JOIN Of Different Tables Called Join A Variable And List

Nov 16, 2013

And then called this join as a symbol or variable, and then have it use to select the items from these joined tables, can this be done in Access? Here is an example of a code that I created, but it has an error message saying the FROM syntax is incorrect.

Code:
SELECT firstJOIN.trainID, firstJOIN.trainName, firstJOIN.stationID, firstJOIN.stationName, firstJOIN.distance_miles, firstJOIN.time_mins
FROM (trains INNER JOIN ((station INNER JOIN lineStation ON station.stationID = lineStation.stationID)
INNER JOIN bookingLeg ON bookingLeg.startID = station.stationID or bookingLeg.endID = station.stationID )
ON trains.trainID = bookingLeg.tid) as firstJOIN

Can Access do something similar to this, in the FROM statement I joined 4 tables, because each unique fields are in each table and I have to joined them to get those fields. I called this join firstJOIN and in the SELECT statement, I list those columns in the table by calling it firstJOIN.trainID. Can Access do something like this, but syntax it differently?

View 6 Replies View Related

Queries :: Join Results Of Unmatched Query With Matched Query To Include Null

Mar 24, 2013

I am trying to do the good 'ol sales report (query) to include customers with no sales.

I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)

If I run a query to show customers (in the customer table) with account numbers, that works

An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).

How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?

I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?

View 3 Replies View Related

Join Query

Jun 6, 2005

Hi All,

I have 2 tables. [1] AvailableCars [2] CarPics

[1] AvailableCars consists of fields as Zone (eg. ZoneA, ZoneB, ZoneC) , CarCategory (eg. CarAA1, CarAB1, CarAA2 ... and so on), DailyRates, and Valid DateBounds (FromDate, ToDate).

[2] CarPics consists of CarCategory and Carpicture (Path of the car image).

Now I want to Bring Only those cars from the AvailableCars table and CarPics which satisfy the criteria of falling between Valid Date Bounds...I am using join as the following :

SELECT
AVAILABLECARS.ZONE, AVAILABLECARS.CARCATEGORY, AVAILABLECARS.DAILYRATES, AVAILABLECARS.FROMDATE, AVAILABLECARS.TODATE, CARPICS.CARPICTURE
FROM CARPICS
JOIN AVAILABLECARS.CARCATEGORY = CARPICS.CARCATEGORY
WHERE
AVAILABLECARS.ZONECODE = "ZONEA2XY" AND
AVAILABLECARS.FROMDATE BETWEEN "DATEVAR1" AND "DATEVAR2"
ORDERBY AVAILABLECARS.ZONE, AVAILABLECARS.CARCATEGORY

Using above query, I am not able to get the desired results. :o

Can Any body help me in solving this problem ? :confused:

Thanks in Advance,

Regards,

Jigs

View 3 Replies View Related

Query With Join And Where

Jul 14, 2005

I need to write a query which retrieves all the records from first table and those matching from second table and also satisfies a criteria.

I had

Table 1
F1
F2
F3

Table 2
F4
F5
F6
F7

I had to get all the records from Table1 and also those matching with Table2
So i did left join and getting all the records.
And I also need to check whether F3 in Table1 is in between F6 and F7 of Table2.

So I wrote query like this

SELECT Table1.F1, Table1.F2, Table2.F5
FROM Table1 LEFT JOIN Table2 ON (Table1.F1 = Table2.F4) WHERE Table1.F3 BETWEEN Table2.F6 AND Table2.F7;

I am getting only those records which are matching where condition.
I need to get all the records including which match the criteria and also those which don't match.

Please any suggestions?

Thanks

View 3 Replies View Related

Query With Inner Join

Nov 30, 2005

I am trying to create a query which shows only the newest note for every prospect. I have the code below, but I get a 'Syntax error in JOIN Operation' when I try to run the query and TBLNotes is highlighted.

SELECT TBLProspect.CompanyName,TBLNotes.Note, TBLNotes.NoteDate
FROM TBLNotes AS a
INNER JOIN TBLProspect ON TBLProspect.LeadID=TBLNotes.LeadID
WHERE ((((Select Count(*) from tblNotes where LeadID=a.LeadID and NoteDate>=a.NoteDate)) In (1)))
ORDER BY a.LeadID, a.NoteDate DESC;

Where am I going wrong?

View 1 Replies View Related

Inner Join Or Sub Query?

May 7, 2006

I hope someone out there can help me, I'm not an SQL experianced person, I only know enough to make problems. Anyway I believe my problem below is probably simple to someone that knows SQL very good. I have a link to a sample Access database if anyone is interested in helping me solve this.

The Sample Database (http://shadesoffire.net/files/db1.mdb)

Have a problem and can't quite seem to get it solved. I have simplified the whole thing to make it easier to understand.

I want a report that shows me the Product Category and Sum from work orders that were > $100.

I'm not quite sure how to do this, I've tried several methods of Joining and Sub queries but nothing really works. All I get in my results is product categories > 100. See I would expect the product category to be on the report even it it were $55 if it came from a work order containing total costs > 100.

I have manually supplied the results that I want, any help would be greatly appreciated. My real problem deals with larger results and numbers but these are the basics.

Linked is an Access database with one of my queries that show all work orders > 100. I would expect all those product categories in my summary report.

Redesign of the database is not an option.

These work orders are all > 100, the total on all reports should be $511

WorkOrderNo Total Cost
00001 $120.00
00003 $268.00
00005 $123.00

These product categories are associated with the above work orders and this is the result I am looking for.

Prod Cat Costs
12 $176
19 $89
22 $233
91 $13

The above results would come from this:

WorkOrderNo LineNo PartNo Cost Cat
00001 1 ABC $55.00 22
00001 2 XYZ $65.00 12
00003 1 ABC $89.00 22
00003 2 XYZ $77.00 12
00003 3 123 $89.00 19
00003 4 456 $13.00 91
00005 1 ABC $89.00 22
00005 2 XYZ $34.00 12

View 2 Replies View Related

Help With SQL Join Query

Jan 3, 2007

Hi all, this is my first post to the board. I�ve been searching through the forums but haven�t been able to put all the pieces together on my problem. First some background on my application:
I have 3 tables ISSUES, UPDATES, and PRTS. Each record in ISSUES has several records in UPDATES, and may have a single record in PRTS. The issues and updates tables have 3 �clones� (everything except the table name prefix is the same) that are used by other departments. All of the clones use the same PRTS table.
I am trying to put a query into SQL (I first built it using the query builder & an example from Microsoft for a query to find the last record) that will return each issue, along with the last update associated with the issue. My intent is to switch the table names using code depending on the user�s selection. I know how to switch �ElectricISSUES� to �PowerISSUES� below, but I do not know how to switch the source for the �qryLastUpdate�. Any help would be greatly appreciated.

issueSQL = "SELECT ElectricISSUES.Index, ElectricISSUES.Status, ElectricISSUES.Description, ElectricISSUES.Driver, ElectricISSUES.Champion, ElectricISSUES.PRTS, ElectricISSUES.SubDate, ElectricISSUES.DateClosed, ElectricISSUES.Diamond, Date()-[Subdate] AS DaysOld, qryLastUpdate.Update, qryLastUpdate.Udate, qryPRTS.Step, qryPRTS.[Next Step], qryPRTS.Designee, qryPRTS.[Ewo#], qryPRTS.BreakPointDate " & _
"FROM (qryLastUpdate RIGHT JOIN ElectricISSUES ON qryLastUpdate.UIndex = ElectricISSUES.Index) LEFT JOIN qryPRTS ON ElectricISSUES.PRTS = qryPRTS.[Prob#] " & _
"WHERE (((ElectricISSUES.Status) = 'closed')) ORDER BY ElectricISSUES.Index;"
db.OpenRecordset (issueSQL)

View 8 Replies View Related

Query Without Join??

Jan 30, 2007

I really don't know how to describe this, so bear with me!

i have a table of sales data 'tSales' that holds 3 years of transactions.

Fields are

Acc
Cust
Market
Prod
Qty
Value
Cost
Material
Labour

I have another table 'tLabRate' that holds our labour rates

e.g. in 2004 �20ph, 2005 �25ph etc

in the Form:

RateID
LabRate 20.00 25.00
LabFactor 0.6666 0.8333
LrFrmDate 01/01/2004 01/01/2005
LrToDate 31/12/2004 31/12/2005 etc

What i am trying to do is display the correct factor for the point in time, e.g.
LabFactor where sDate > than LrFrmDate AND < LrToDate

How do i do this in a query?? i can't join i don't think becasue there isn't a common field....?

Thanks

Graham

View 4 Replies View Related

Join Query

Mar 31, 2007

Hi all
How are you?
I have a small question....
I have a db with 2 tables (Contacts, Products).
Contacts table has 6 columns (CustomerID, CustomerName, Bread, Tea, CD, Pencil)
Products table has 2 columns (ProductID, ProductName).
I want a query that show this result:
Customer NameProduct Name
JohnTea
JohnCD
MarkBread
MarkCD
MarkPencil
JaneTea
TonyBread
TonyPencil

How can I do that??
Kindly, find attached the data base.
Sorry for bothering you.

View 4 Replies View Related

AnyOne Help With This?? Join Query

Jan 16, 2008

Hi All,

Head is battered with this join query. I have 2 tables which I want to join, one being individual data and one being budget data. I want to Sum the budget Cost And Budget admissions dependant on the joins.

SELECT FyldeCoastActivity.Speciality AS [Specialty Code], dbo_Specialty.SPECTEXT AS [Specialty Desc], FyldeCoastActivity.GP_Practice, FyldeCoastActivity.[Activity Type], Count(FyldeCoastActivity.Activity) AS CountOfSUSActivty, Sum(FyldeCoastActivity.BasicSpellCost0708) AS BasicCost, Sum([PBC month 8 budget].[Budget Admissions]) AS [SumOfBudget Admissions], Sum([PBC month 8 budget].[Budget Cost]) AS [SumOfBudget Cost], [BasicCost]-[SumOfBudget Cost] AS [Cost Difference], [BasicCost]/[SumOfBudget Cost]*100 AS [Cost %]
FROM ((FyldeCoastActivity LEFT JOIN dbo_Specialty ON FyldeCoastActivity.Speciality = dbo_Specialty.SPECCD) LEFT JOIN [Al Providers] ON FyldeCoastActivity.Provider_Code = [Al Providers].Trust_Code) LEFT JOIN [PBC month 8 budget] ON (FyldeCoastActivity.ProviderCode1 = [PBC month 8 budget].[Provider Code]) AND (FyldeCoastActivity.Speciality = [PBC month 8 budget].[Specialty code]) AND (FyldeCoastActivity.GP_Practice = [PBC month 8 budget].[Pract code]) AND (FyldeCoastActivity.[Activity Type] = [PBC month 8 budget].PODSUS)
WHERE (((FyldeCoastActivity.[Financial Month])<=8) AND ((FyldeCoastActivity.ExcludeReason)=0 Or (FyldeCoastActivity.ExcludeReason)=99 Or (FyldeCoastActivity.ExcludeReason) Is Null))
GROUP BY FyldeCoastActivity.Speciality, dbo_Specialty.SPECTEXT, FyldeCoastActivity.GP_Practice, FyldeCoastActivity.[Activity Type]
HAVING (((FyldeCoastActivity.GP_Practice)="p81129") AND ((Sum(FyldeCoastActivity.BasicSpellCost0708)) Not Like 0))
ORDER BY FyldeCoastActivity.Speciality, FyldeCoastActivity.[Activity Type];

The query is coming back with Very high figures which leads me to believe that the joins are not working and just summing all the data in the budget table.

I have uploaded a screeen shot of the query.

Any help would be much appreciated.

Thanks

View 2 Replies View Related

AnyOne Help With This?? Join Query

Mar 10, 2008

Hi All,

Quick question...

If I were to do a right join on four different attributes E.g. a = a b = b etc etc and the criteria would be Attendance is null. I am getting the results back which I want but the columns which I have joins on are blank? Any ideas why?

Any help appreciated....

View 2 Replies View Related

Queries :: Search For Multiple Plot Numbers Preferably In One Parameter Prompt With Comma To Separate Numbers

Aug 12, 2014

I'm having multiple problems with my database like things such as -

i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too

I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?

i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:

Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:])
Site - (criteria = Like "*" & [Enter Site:] & "*")
Product - (criteria = Like "*" & [Enter Product:] & "*"

The Query is the one im most concerned about , i can live without a form.

View 14 Replies View Related

JOIN Query Question

May 21, 2007

Hi,

I am a SQL Server user and there are a few Access differences which seem to be getting the better of me... Can someone answer a simple question? I have the following query:

SELECT tbUserGroups.f_GroupName, tbUserGroups.f_DefaultCredits, tbUserGroups.f_OverrideUsers, tbCreditSchedules.f_CreditName, tbUserGroups.f_Created, tbUserGroups.f_Updated, COUNT(tbUsers.f_ID) AS Users
FROM tbUserGroups
LEFT JOIN tbUsers ON tbUserGroups.f_ID=tbUsers.f_UserGroupID
INNER JOIN tbCreditSchedules ON tbUserGroups.f_CreditScheduleID=tbCreditSchedules. f_ID
GROUP BY tbUserGroups.f_GroupName, tbUserGroups.f_DefaultCredits, tbUserGroups.f_OverrideUsers, tbUserGroups.f_Created, tbUserGroups.f_Updated, tbCreditSchedules.f_CreditName;

This comes up saying that I am missing an operator? From browsing the forum, someone suggested putting parenthesis such as:

SELECT tbUserGroups.f_GroupName, tbUserGroups.f_DefaultCredits, tbUserGroups.f_OverrideUsers, tbCreditSchedules.f_CreditName, tbUserGroups.f_Created, tbUserGroups.f_Updated, COUNT(tbUsers.f_ID) AS Users
FROM ((tbUserGroups LEFT JOIN tbUsers ON tbUserGroups.f_ID=tbUsers.f_UserGroupID) INNER JOIN tbCreditSchedules ON tbUserGroups.f_CreditScheduleID=tbCreditSchedules. f_ID)
GROUP BY tbUserGroups.f_GroupName, tbUserGroups.f_DefaultCredits, tbUserGroups.f_OverrideUsers, tbUserGroups.f_Created, tbUserGroups.f_Updated, tbCreditSchedules.f_CreditName;

However, when i do that... It says i haven't supplied a parameter! As far as i'm aware i'm not trying to supply a parameter, its just a simple join.

I would appreciate any advice.

Thanks,

View 6 Replies View Related

Delete Query Inner Join

Dec 20, 2007

Hi,I am very bad in queries and need your help on this one.I am trying to run a simple delete query having an inner join but it gives an error every time. The error being "Specify the table containing the records you want to delete"here are the details of the simulated table and the query than I am trying to run table1 : name number abc 1 xyz 2 table2 name cityabc AQuery that I am trying to run is:Delete Table1 from Table1 INNER JOIN Table2 on Table1.name = Table2.name;Thanks

View 2 Replies View Related

Need Help On Join Creation Query

Mar 12, 2008

Hi! Im wondering why my code below Does not work!.... any suggestion???

strStatement = "SELECT SessionData.RunNumber, SessionData.SessionID, SessionData.SequenceNo, SessionData.SubjectID, SessionData.Latency, SessionData.BeamBrk1, SessionData.BeamBrk2, SessionData.BeamBrk3, SessionHeader.SessionDate, SessionHeader.ChamberNo" & _
"SessionHeader.GroupID, SessionHeader.SubjectVariables, SessionHeader.TestDefName FROM SessionData" & _
"INNER JOIN SessionHeader ON SessionData.RunNumber = SessionHeader.RunNumber;"

getQueryname = InputBox("Enter a name for New Query?", "Enter a Label or Name for Query", "Enter Query name/Label Here", 500, 700)

Set qryEmployees = curDatabase.CreateQueryDef("getQueryname", strStatement)

View 4 Replies View Related

Join Two Tables In A Query

Feb 13, 2005

I would like to join two tables with one query. Here is the twist though.
The first table would have a set of value like so:

0
50
100
300
500
750
1200
1500

the second table would have values as such

0
1000
1300
1500
1750

by just setting up the relationship to show all values from the sirt table and only those from the second table that match I get a list of values that will not work for me. What I would like to reflect
in the query is all the values from the first table and if there is for instance no value of 500 in the second table I would like to return 1000. For instance there is a zero in each table so that would be output twice. There is however no value of 50 in the second table so the query would read 50 and null or blank when I would like it to read 50 and 1000 (The next value higher).

View 3 Replies View Related

Append Query - Which Join?

Aug 2, 2005

I have a table from which I want to extract selected fields from each record and write a new corresponding record in a separate table (like a summary of each record). This process will need to be repeated on a regular basis. I am using an append query so far but this appends 0 rows unless I set the join type on the query to option to 2 (I think a left outer join) - this means that each time I run the query - it appends records that have already been appended previously. What I want is the query to append only those records that exist in the primary table but don't exist in the secondary one. Can't see a join type that will do this for me - have experimented with the alternatives but it just creates more duplicates.

View 2 Replies View Related

Join Query Question

Jul 25, 2006

I am a brand new user of Access, but need to know how to do an advanced join query. I have two lists joined on one field, but the data across the two fields is not always consistent. For example, one list has a title listed as NY Branch, but another has it listed as New York Branch. How do I have access compare only a certain number of characters, rather than the entire entry?

I'd appreciate any guidance!

View 6 Replies View Related

Delete Query With Join

Jan 11, 2007

Hi, i am having some problems with a delete query,

This is because i am using 2 tables in the query

TBL_12a_Requisition_Sub

and

TBL_12_Purchase_Requisition_Sub

The 2 tables are linked by fields both named [Requistion Number]

Basically where the 2 tables match i would like to delete all records in TBL_12_Purchase_Requisition_Sub. The query works fine when in design view but when running it you get the error message (specify the table containing the records you want to delete). The problem with this is i have already done this in the query.

I have looked over the net and it looks like you need to do something with a select statement in sql, but i can't figure it out. Any help would be much appreciated. Thanks.

View 4 Replies View Related

Query Join Properties

Feb 7, 2008

I have 2 tables. one (Data_Participant) has all of the data on individual participants. the other (Data_Main) has the data of how they participated per year. my Data_Participant table has a primary key (ParticipantID) which does not allow duplicates and the Data_Main table is setup w/ a non-primary key field (ID) which will allow dupes. these two fields are joined in my query with the Data_Participant.ParticipantID set to include all records and and only those records from the Data_Main.ID field where the records are equal.

to me, the join properties being setup like this would allow me to put any type of search criteria i would like under the Data_Main table fields and i would still get all of the Data_Participant's tables as a result, but only those from Data_Main that i had filtered.

ultimately, i'd like to see all of my Data_Participant data and only the Data_Main data that makes it through my filter/search criteria. am i not understanding how the join properties work?

View 5 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

Crosstab/Join Query Problems

Jun 15, 2005

I'm going to try and explain this the best way I can so please bear with me if it doen't make total sense.

I have two tables

tblSales (is used when am employee makes a sale)
employee
date
tier level

tblTierLevels (shows all the different tier levels and the pay amounts)
tier level
pay amount

I want to create a report which groups by employee and shows every tier level even if they didn't sell any of those tier levels. For example...
Jane Doe Units Pay Total Pay
Tier Level 1: 0 $5.00 $0.00
Tier Level 2: 2 $6.00 $12.00
Tier Level 3: 0 $7.00 $0.00

If I create a query with a join to show all from tblTierLevels and only those from tblSales where tier level = tier level if the employee did not sell any of a certain tier it does not display their name. So I can't group by name and show all tier levels.

If I create a crosstab query this will show all employees as well as all the tiers however this lists the tier levels across (as columns) I want to list the tier levels as rows.

Does anyone know how to do this?

View 1 Replies View Related