Order By When You Have Joined Tables

Jun 27, 2005

If I use order by on a query that has two linked tables I keep getting an error:
This is the query
SELECT d.PDetailsID, d.po_id, d.po_uniqid, d.job_id,
d.p_qnty, d.p_desc, d.p_unitp, d.p_units,
d.p_extend, d.p_qos, d.p_grec, d.p_done,
d.cocreq, d.est_id, d.line, d.class,
d.selected, d.itemid, d.category, d.GroupID,
d.p_qosextended, d.late, d.lastsavedby, d.matid,
d.customer, SQLACCESS.tblctpur_d.jobid, d.DTime, d.Notes,
d.DescDetails, h.pur_ddue
FROM SQLACCESS.tblctpur_h as h INNER JOIN
SQLACCESS.tblctpur_d as d ON h.pur_id = d.po_id
ORDER BY d.line

The error is :
The colum prefix d does not match with a table or alias used in the query

View Replies


ADVERTISEMENT

Forms :: Possible To Have Combo Box On Form With Fields Joined With Alphabetical Order On Surname

Aug 1, 2015

I have a Table with 6 fields, 2 are named Initial and Surname. Is it at all possible to have a combo box on a form with these to fields "joined" with the alphabetical order on the Surname. I understand that using a query will not work as the result is based on an expression

View 11 Replies View Related

Tables :: Joined Two Tables - Unable To Filter / Lookup On Second Table?

Feb 18, 2013

I have an Access Table with about 28,000 Automobile dealerships across the country shown. I've joined a new/small phone contact table to this to keep up with our phone contacts with the dealership and followup efforts. When I search/filter on the dealership table all is fine. However when I search/filter on the phone contact table with a few test entries, I get nothing at all. I supposed that after joining the tables, I'd be able to do a search on the field named follow up date and find/filter today's date or other dates and locate which dealerships to contact when the correct date arrives. But nothing.

View 14 Replies View Related

Query Joined Tables

Jan 24, 2005

What I'm trying to accomplish is probably really simple that I just can't get it to work. I have set up the relationship properly, I think. Here is what it looks like:

tblProjects(ProjectID[PK],ProjectName, ProjectType, ProjectDate)
tblMembers(MemberID[PK], MemberName)
tblRoles(RoleID[PK], RoleName)
tblProjMemb(ProjMembID[PK], ProjectID[FK], MemberID[FK], RoleID[FK])

Each project will have three different member roles. Here is what I'm trying to get from my db:

myQuery(tblProjects.*, tblMembers.MemberID As Role1ID, tblMembers.MemberName As Role1, tblMembers.MemberID As Role2ID, tblMembers.MemberName As Role2, tblMembers.MemberID As Role3ID, tblMembers.MemberName As Role3)

I need Role1ID = Role1 If when tblProjMemb.RoleID = 1. I need to create a column for each of the three member roles and have the name of that person in that column.

I'm not sure how to accomplish this. Any help is much appreciated. Thanks!

View 1 Replies View Related

Can't Add New Records When Tables Are Joined

Apr 18, 2005

I have two tables in my database
Agents
Zip Codes (linked table)

If I create a query and then a form off the query that includes fields from both tables - Access will not let me add new records.

Ultimatley what I am looking to do is have a zip code field in the Agents table that I fill in and then it will automatically look at the zip codes table and then fill in the zip code, county, & state in a different area of the form.

I tried using fields from both tables and tried a query using fields from both tables and it will not let me add new records.
I have a relationship between the fields in both tables.

What am I doing wrong. I know this can be done.

Any help would be great!!!

View 2 Replies View Related

Issue With PDF Report From Joined Tables

Nov 11, 2007

Hi,

I have problem with pdf-ing a report that has record source coming from a query namely from the two tables joined based on the same of several fields (foreign keys).

I have PDF coding that would turn Access report to PDF report. It has no problem when the report's record source is from one table only.

Just wonder if anyone who might have a similar issue would like to share with me some help. Is it normal that PDF doesn't work if the report comes from joined tables in Access?

Thank you in advance

View 3 Replies View Related

Deleting Rows From Joined Tables

Jun 5, 2006

I am using the following query to identify rows in one table that have no match in another:

SELECT MAS.MASID
FROM MAS LEFT JOIN IVT ON MAS.Field1=IVT.Field1
WHERE ((IVT.IVTID) Is Null);


MAS is one file with MASID as the key.
IVT is the other file with IVTID as the key

This provides me a list of the rows in MAS with no match in IVT.

I now want to delete these rows in MAS so we can process the other fields in the table.

what is the syntax for the delete statement? I can't get the join to fit in correctly.

Help!

View 2 Replies View Related

Get Information From A Few Tables Joined Not Working....

Nov 28, 2007

Hi,

I am getting information on products stored in a query. I want that query to check a couple tables to see if an item is linked through all of them. If it is not listed in the last table (catalog) I want it to be shown.

Code:Catalog tableVolume Prefixprodno Price15 - CE 0218 9.9915 - CE 0722 3.7215 - CF 0218 12.3615 - CF 0091 14.00Source TableSource MediaCM70904 15 - CE

Code:Batches QueryCustomer Number Prefixprodno Source716933 0218 CM70904716933 0408 CM70904

This is the Select Statement I'm using, which gets the 0218 but not the 0408 I want

Code:SELECT (fieldnames......)FROM [Batches] INNER JOIN ([Source] INNER JOIN [Catalog] ON [Source].[Media] = [Catalog].Volume) ON ([Batches].Source = [Source].[Source]) AND ([Batches].Prefixprodno IN ([Catalog].Prefixprodno))WHERE [Batches].[Customer Number]=716933;

If I do NOT IN I get both 0218 and 0408. I just want 0408. I'm sorry if this seems confusing but any help would be amazing at this point =/

View 3 Replies View Related

Can't Add Data Into Fields In Joined Query/tables

Jan 17, 2006

I can not, or not allowed to enter data in fields in a query built from three tables that are jointed by a common field with the same name. The parent table is linked to another Access data base as is one child table the other is local data. Also, I am not able to view the child table data when viewing the parent table.

View 1 Replies View Related

Reports :: Joined Tables - Returning Multiple Records

Apr 10, 2014

I a report based on query based on joined tables. Im using FK and PK accordingly in the tables and have a junction table.

I've attached a sample (removed all unique identifiers for privacy in case you're wondering).

Table structure as follows:

Employees tbl - Junction (License Link tbl) License No tbl, License Class tbl, Endorsements tbl

So the query runs on all of these tables.

When I build a report on the query, Simon shows up Six times. I suspect because he has six endorsements.

How do I produce a report on this table/query structure so that I get:

Simon.
Class, 1,2,3,4,5
Endorsement F,R,T,W,DG

View 1 Replies View Related

Create Access Form With Having Joined Tables Fields

May 14, 2013

I have at least 3 relates tables in my access database.the first(sessions) table stores session detailes like id,date,time

The second one (tblemployees) contains our employees details like name, idp ,the third (attreq) relates the first table to second it stores ids of sessions and ids of personals that which determine which personals have attended in special sessions.

Now,my problem is that I want when a personnel log in and opens "confirmed session"form ,the access check and open records that this person have attended. How can I do it?

View 3 Replies View Related

Removing Duplicate Entries From Joined Tables In CrossTab Query?

Jun 19, 2007

Hello all,

I have made a crosstab query that is sporatically making duplicate counts. I'd like to know how to fix the problem, but more importantly - I'd like to know why and how my query is giving me these results so I can avoid making this same mistake again.

The relationship is set up so that all records from one table are returned, with only matching records from the other - so no problems that way as far as I can see. This is my crosstab query that is giving some counts of the same record as being matched sometimes two, three, or even four times:

TRANSFORM
Count([Testing DB].[DB_ID]) AS [CountOfDB_ID]

SELECT
[Testing DB].State, [Location].[North], Count([Testing DB].[DB_ID])
AS [Total Of DB_ID]

FROM [Testing DB] LEFT JOIN [Location] ON [Testing DB].[Address] = [Location].Address

WHERE (([Testing DB].[Window Length]) Is Not Null))

GROUP BY [Testing DB].State, [Location].[Size]

ORDER BY [Testing DB].State, [Location].[Size]

PIVOT Format([Date],"mmm-yyyy");

When I do a simple query for a list of the records being counted by this query, I can see the duplicate entries and can eliminate them simply by adding "Distinct" to the select statement - but I haven't had any luck adding any kind of distinction with this crosstab that will do the same.

How can I eliminate duplicate counts of records in my crosstab? If anyone has a suggestion, it would be much appreciated.

View 1 Replies View Related

Result In Query Not Showing Because Field Not Filled In (Joined Tables)

Apr 23, 2014

I have a query, that I have a criteria to show appointments in the past (< Date()) but one result doesn't show up although the appointment end date is a past date, it only shows up when I fill in a field that is in another table that is joined and part of the query. But there's no criteria there for it to not be null.

View 3 Replies View Related

Replication Changes Record Order In Tables?

Sep 14, 2006

I decided to learn about replication today and I made a master database.

To my surprise, I noticed the records in the tables in the Master version are not in the same order as the original version. The records are now in random order but before they were in the order of entry.

Now i have to create new queries for all the forms that have a table as a record source just to get the record ordering right.

I want to know how reliable is Access Replication and should I expect a lot more suprises like this. Is replication more trouble than it's worth?

View 2 Replies View Related

Tables :: How To Input More Than One Product Per Order

Nov 19, 2013

i am very new to access. Logic and method needed in order to create a data base that records weekly orders.

What types of tables do i need?
How can i input more than one product per order?
How do o group the information in a organized way?

Ideally i would like to Input the clients name the week of the order and then all the products etc that are ordered.

Then be able to see in an orders table each client that made an order and a subdatasheet (the small plus in the corner) that once clicked i see all the products that are in that order?

View 10 Replies View Related

Tables :: Autonumber Sorting Out In Order

Sep 2, 2013

I am creating a database for cases. I want to set autonumber into sequence, Let say if there are data numbering 1,2,3,4,5. If I delete no.2, The data will rearrange in oreder from 1,2,3,4. While If I add a new value it would be the no.5.

Instead of data value autonumber 1,2,3,4,5 as I deleted no.2 and add new records. The data has become 1,3,4,5,6.

View 1 Replies View Related

Auto-numbers Are Not In Order For Tables

Nov 21, 2012

My autonumbers are not in order for tables.As in for example for a customer

customerid customer first name customer last name

17 john parker
18 james johnson
19 alex scott

This is right at the top of the table the first sets of data,i want it to be

customerid
1
2
3
4
5
,etc

Why does it have a random number like "17" when I DO NOT have the first 17 customers !

View 6 Replies View Related

2 Top N Queries Joined

Apr 16, 2008

Col1 Col2

A 10 10
B 15 15
C 20 12
D 25 9
E 30 20
F 35 18
G 40 2
H 50 23
I 55 1
J 60 18

I have to get the Top 5 for each Col, grouped by group, but all in one table. What I did was split Col1 to one query with a return of 5. And also did that with Col2. But now I have to join them, and only a few records pop up, those which have the same group name, all others were excluded...what can I do?

View 3 Replies View Related

Tables :: Autonumber Order In Existing Table

Jul 9, 2015

I was adding an autonumber field to an existing table and I assumed the numbering would follow the order of the primary key but that doesn't seem to be the case.

View 9 Replies View Related

Delete From A Joined Query

Nov 25, 2005

Hi...

I have an Access query, which gives me the "orphans", between two tables.

They look like this

tblSurveys tblJobs
* *
Grade -> JobGrade
Paytype -> JobPayType
Survey -> JobSurvey
other... -> other (including jobcode)


Linked as indicated, (all from tblSurvey to those matching tblJobs)
with criteria, WHERE jobcode is null
Ie. an unmatched query between tblSurvey and tblJobs.

Now I want to delete those from the survey where there is no jobs.

But when I change my select query to a delete query, I get the error:
Cannot delete from specified tables

My SQL (access generated) looks like this:

DELETE tblSurveys.*, tblJobs.JobCode
FROM tblSurvey LEFT JOIN tblJobs ON
(tblSurveys.Grade = tblJobs.JobGrade) AND
(tblSurveys.PayType = tblJobs.JobPayType) AND
(tblSurveys.Survey = tblJobs.JobSurvey)
WHERE (((tblJobs.JobCode) Is Null));


That first line looks wrong...

Anyway, if someone can help me to delete these I will be forever grateful.

The way I understand it, a delete query should look more simple... ie
delete * from tblName where (and type the intricate stuff here)

Thanks in advance
Reenen

(BTW: I posted this on Ozgrid as well, but they are bigger on Excel there, and response times are better here. Apologies in advance)

View 1 Replies View Related

Counting Joined Tuples

Sep 6, 2006

Hi,
I have two tables A and B that I joined using a LEFT JOIN. I know that some rows in table B will not be joined. How do I count how many will not be joined and how do I return those rows?
Thanks
ClaudiaM

View 2 Replies View Related

Tables :: Come Up With Automatic Line Numbers For Each Specific Order?

Oct 2, 2014

I am trying to come up with automatic line numbers for each specific order. So for example, I have job number 123456 that has ordered 3 items, what I would like is that item 1 has a field with a 1 in it automatically, and item 2 has a 2 in the field and so on. But the trick is that when order 123457 gets entered and has 5 items entered, I would like it to start over at 1 and go to 5. Does this make sense? Is it possible to do this in a table? Or can this happen on the form? That I assign a value to a field. I am using Access 2010, have been for a few years now, but I have lots to learn.

View 14 Replies View Related

Tables :: Concatenate Field Names But For Each Record Order Can Be Different

Jul 10, 2014

I have
Field1
Field2
Field3

I need to concatenate these three fields into one but for each record the order can be different. What I want to do is have another field in the table to store the concatenation order as the field names not the field values.

I also need to display the concatenated field as the actual field values and not field names in a form or a report

So if field4 was the concatenated field store it would store the information like this

[Field1] & " " & [Field3] & " " & [Field2]

In a form or a report

Field4 should show as Values of those fields

Monkey Dog Cat

View 1 Replies View Related

Tables :: Generate New Order ID For Same Customer In Orders Table?

Oct 3, 2012

how do i get form to generate a new order ID for the same customer in my orders table

View 2 Replies View Related

Queries :: Joining Two Tables In Order To Create A Form

Apr 2, 2013

I am trying to use a join query to join two tables in order to create a form. I have done this on two other occasions in my database with no issues. Now it is only pulling the ID and I want it to pull the description. I have looked at the SQL view for the other queries that are similar as well as the design view and all of them are set up the same way with the exception that this new join query is pulling CertID rather than the CertDescription.

Is there any reason why it would be doing that? Is there an error that I could possibly be doing? It seems according to my notes that I have created all the queries the same but this last one does not pull the same info as the other queries.

View 2 Replies View Related

Delete Query With A Joined Table

Aug 6, 2004

I have created a query that show all the records form Material Estimation table that the job number matches in the Production Table, that the AssDate is between the frm_Export_Data dates.

This the SQL view of the query.

DELETE [Material Estimation].*, Production.[AssDate]
FROM [Material Estimation] INNER JOIN Production ON [Material Estimation].[Job Number] = Production.[Job #]
WHERE (((Production.[AssDate]) Between [Forms]![frm_Export_Data]![FromDate] And [Forms]![frm_Export_Data]![ToDate]));

I want to delete all the records in the Material Estimation table that show up in this query.

Is this possible?

Thank you...

View 4 Replies View Related







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