Please Help With Simple Query (only 2 Tables!)

Sep 5, 2007

I'm using Access 2000. I have 2 tables in my query: tblPlace and tblConnection. Using a form and the Connection table I can link 2 places together from the Place table and present it in a query; for example:
SELECT tblPlace.Place_ID, tblPlace.Name, tblPlace_1.Place_ID, tblPlace_1.Name
FROM tblPlace AS tblPlace_1 INNER JOIN (tblPlace INNER JOIN tblConnection ON tblPlace.Place_ID = tblConnection.Place1) ON tblPlace_1.Place_ID = tblConnection.Place2
WHERE (((tblPlace.Place_ID)=13) AND ((tblPlace.Type)="Street") AND ((tblPlace_1.Type)="Street")) OR (((tblPlace_1.Place_ID)=13) AND ((tblPlace.Type)="Street") AND ((tblPlace_1.Type)="Street"));
http://www.rarekungfumovies.com/sydenham/access1.jpg
Here I want to find out which other streets link to street 13 (Perry Hill). Running the query shows 5 different connections:
http://www.rarekungfumovies.com/sydenham/access2.jpg
I need to somehow combine both sets of IDs and street names (except Perry Hill), so that I end up with a filtered list in alphabetical order:
17 Bell Green
38 Castlands Road
12 Catford Hill
14 Elm Lane
16 Perry Rise
Is there any way this can be done so that I end up with only the above data? :confused: Any help would be much appreciated!

View Replies


ADVERTISEMENT

Probably A Simple Question About Tables

Apr 6, 2006

I am trying to find out what the little arrow next to specific tables is, I believe it's something signifying that it's an updated table....but if I copy one to replace with a new one yet want to keep that old one for a side by side comparison, how do I remove that arrow?

I know this is probably very confusing how I am asking but hopefully there's some really simple way to do this.....or hopefully I am making sense.


Basically this is a This Year Vs Last Year Comparison with data exctracted from a main frame, brought in on a daily basis in the new table and I want the results of all of last year to remain in this other table...so basically FY 2005 vs FY 2006

Hope this makes sense
Thanks

View 4 Replies View Related

Simple DB Trouble - Entities, Tables Etc

Sep 6, 2006

Hi

i currently have a simple spreadsheet in Excel containgin the following fields.

## Date - not unique
simpy shows the date of the transaction

## Debt - who the debt will be paid to - unique per date (e.g. CompanyA)

## Type of debt - what service is it - not unique by date or debtor (e.g. Wages)

## Net Price before VAT (X)

## VAT (value added tax, a UK tax) (Y)

## Price of Service (Z = X+Y)

## Running total of Net Price before VAT (X + X + X...)

## Running total of VAT (Y + Y + Y...)

##Balance - (Z + Z + Z...)

The spreadsheet currently calculates this automatically using simple formulas.

I'd like to create a nice little interface for this data entry using MS access forms. From then I can easily create queries and reports etc.

can somebody please help me out! :D I've tried to list my entities and create an ERD to no avail!

Many Thanks

Mike

View 7 Replies View Related

Simple Question About Showing Tables In Form

Aug 9, 2006

Hi
I have a form which has two command buttons A and B. I set that when somebody click the button A then table A will be open and so on. But I dont want that table opens in new window. I want that I can "draw" a "box" in form where the table will showed. So can anybody help me and tell how to set that table will always show in "box". Can I set this somewhere in Access or do I have write VBA code. (I'am very very poor at coding)

View 4 Replies View Related

Tables :: Simple Cascade Update Not Working

Oct 29, 2013

I've done this tons of times, so I don't understand what's happening... The title says it all: when I update the ID of one record of the main table, the referenced records on related tables won't update even though they have a relationship between them with the option "Cascade update" clicked.

In the attached database, the main table "Expedientes" is related to table "Actuaciones".

View 1 Replies View Related

Tables :: Avoid Double Bookings In Simple Database?

Jun 7, 2013

I work across a number of small venues which have art cases that can be booked for displays. I am trying to build a simple data base to report what space is available and also what art is currently being displayed. The art is usually booked by month, but sometime it can be booked for a week etc.

I have set up 3 tables

Art Inventory

Art Cases by Venue

Art Case Bookings

In the art case booking form, I have set up the start and end date but I cannot figure out how to avoid double bookings of a case? Once I have that worked out I believe I know how to build the required reports for my needs.

View 4 Replies View Related

Simple Like Query

May 11, 2005

Hi there,
I have a really simple question.
I have a field in a table that contains comments (text format). I want to run a query where I can get only the records that contain the word "high" in the comments. There may be some typos so I would like records returned that also contain something like "high". High is not the only word in the comments.

Does this make sense?

Thanks,
Row

View 4 Replies View Related

Simple Query..

Jul 26, 2005

Hi guys, wondering if you could help me, I can't seem to get this right..

I have two tables in question:

Items: StyleTypes:
------ ------------
ItemID ItemType
ItemType StyleType
Style
Lots of Attributes

ItemType is a list of items (Table, Desk, Bookshelf, Chair)
Style is a type of style for the item (Circular table, Radial Desk)

I want to create the Items.Style field based upon a query of the StyleTypes. So if a user types "Desk" in Items.ItemType then the Items.Style field will be limited to only those Styles which match that selected item within the StyleTypes table.

Think thats clear enough, but my attempts have all failed, hope you guys can give me a push in the right direction, thank you!

View 4 Replies View Related

Probably A Simple Query....

Apr 5, 2006

Hi all,

I have very little knowledge of databases and even less about writing queries.

The problem:
I have a database that lists cities (such as Paris, London, New York etc) as rows and employment status (Full-time, Part-time, Unemployed etc) as columns.
I need to go through each city and record which employment status is greatest (for each city) and present it in a new column. I can do this manually but I’m sure a query can be created to automate the process.

Hope that all made sense. Can anyone suggest ideas on where to start?

Many thanks,

Carl

View 1 Replies View Related

Simple Query..need Help If Possible

May 6, 2006

hi
have just designed a rather simple music database for my dad to use at home as he has large collection.
have a table with the heading"artist" and another one with "album" ...my problem is that on the table there maybe 1 or more instances with the same artist but with different albums...when i run a simple query for ..say artists starting with the letter"a" i may get about 10 cases of the same artist appearing in my query when all i want is one...(depending on if i may put in say one artist like abba having 10 albums etc)...i am entering each artist with the album each time so i may enter the artist in say 10 times....depending on how many albums etc

if you understand all that...i would be grateful if someone could help me with a simple query
alan

View 7 Replies View Related

Simple Query

Jul 4, 2006

I have a query that returns the uncompleted jobs from a single table ( its a calls database)
all i need is query that returns the amount of uncompleted jobs onto the switchboard I.E whatshername has X amount of uncompleted jobs , i have done a search and most of the solutions are very complicated for what i want to do .....anyone any ideas ?? pls

View 2 Replies View Related

Simple Query, For You - Not Me

Jul 6, 2006

Thanks in advance to anyone who helps.

SQL and Access are both new to me. I have purchased some books (which are still in the mail), and have gone through this forum (which seems to be a little more advanced for me at this moment).

I have a database that contains Business_Unit (Store Numbers), Post_Date (Last Date of Inventory), and other misc. stuff. Anyways, My goal is to pull the latest date of inventory for each individual store before the last ninety days. What I have so far:

SELECT DISTINCT informix_shrink_head.business_unit, informix_shrink_head.counting_event_id, informix_shrink_head.post_date, informix_shrink_head.delta_cost, informix_shrink_head.delta_count, informix_shrink_head.count_reason_cd, pcw_loc_master.closed
FROM informix_shrink_head, pcw_loc_master
WHERE (((informix_shrink_head.post_date)<=Date()-90) AND ((pcw_loc_master.closed) Is Null))
ORDER BY informix_shrink_head.business_unit;

However, I get the same store multiple times and for multiple post dates.
I do not even know if this can be done in one simple swoop, or if I need to create another table and re-query that? Any assistance or comments would be much appreciated.

Thanks,
A Gator

View 1 Replies View Related

Simple Query

Sep 11, 2006

Hi guys. I have been away a while and boy am I rusty.

I would like to show how many available licenses there are. Here is what I have:
Software (Office 2003)
license purchased (8)
license used (2)

I added an expression along the lines of:
Available: Sum([tblSoftware]![Number of licences purchased]-[tblPC-LicenceRelation]![Number of licences])

This gave 14 and not the desired result of 6!!!
Simple I am sure!! :o
Phil.

View 4 Replies View Related

Please Help - Simple Query

Sep 26, 2006

Hi Guys,

I have a database that collects information on products when engineers return them, what I need to do is when they have submitted the form, email it to 3 people so that the paperwork can be raised.

I understand that I will need to create a query to display just the last record and then email it.

So the idea is when a record is created and the engineer clicks submit the last record then gets emailed to the three people.

Please be gental with me im still quite new to all this.

How do i create a query that just displays the last record?:confused:

Thanks in advance.

Rich

View 1 Replies View Related

Simple Query Help

Nov 21, 2006

I built an update query that adds two strings of text together with a _ inbetween.

eg

field1 = Hello
Field2 = Jamie

after update= Hello_Jamie

This works fine but when i attempt to run the code in SQL on an after update instance i can't get it to compile

The SQL straight from the working Update query is:

UPDATE tblPID SET tblPID.PIDPIDNoRev = (tblPID.PIDno)+"_"+(tblPID.PIDRev);

I have tried quite a few different ways and believe it to be a probelm with the speech marks or underscore

DoCmd.RunSQL "UPDATE [tblPID] SET [tblPID].PIDPIDNoRev = (([tblPID].PIDno)+"_"+([tblPID].PIDRev));"

anyone got any ideas? i'm sure it's pretty simple

Thanks, Jamie

View 2 Replies View Related

Simple Query Help

Dec 29, 2006

HI,

I am sending ZIP file in attach with files in it. I would like some help to change the 'Q_test' query so that I can get the result shown in the excel file.

Regards,
Elio

View 1 Replies View Related

Simple Query Help

Jan 4, 2007

Hi,

I think i am being a bit thick, all I want to do is create a report of projects without products....

I have a table of Projects (customer name, details etc...)
I have a table of Products (product name, config, details etc....)

Every project will eventually have one or more products allocated to it...

How can i query the database for:

'select all projects created without any assigned products... yet'

I'm having a post xmas block!!!

Thanks

View 1 Replies View Related

Simple Query

Mar 9, 2007

Hi there..

I need a simple query...

I want all the records to appear which have some text in that particular field. So if there is no text in that field, it won't come up. What would I need to write under that field in the query?

View 5 Replies View Related

Simple Query

Nov 16, 2007

Hi,

I am trying to run a query to retrieve all rows in all tables using a row called Project Status. I am however not able to retrieve any data what so ever with the wizard and by using the limited sql I know. I have attached a copy of my tables if it helps.

The relationships are as follows: Release to PCR - One to many, Sponsors to PCR - One to many. The column i want to filter on is Project_status located in the PCR table.

Does anyone have any ideas how i can do this plz?

Thanks in advance for any help!

Ket

View 1 Replies View Related

Simple WHERE Query

Dec 9, 2007

I wish to create a very simple query but cannot find the correct syntax mentioned in any documentation.

The WHERE clause needs to have the effect of ignoring certain records in a table ie

WHERE fieldvalue <> 12 AND fieldvalue <> 23 AND fieldvalue <> 37 etc etc for up to an unspecified number of values.

Is the above the correct syntax (very tedious to construct in expression builder or natively) or is there something more simple available such as

WHERE fieldvalue <> (12, 23, 37 ......)

Many thanks

View 3 Replies View Related

Simple Query Never Returns

Feb 25, 2008

The following query never returns, even though there is an index on the name field in the Patient table:

SELECT p.Name FROM sheet4 AS s INNER JOIN Patient AS p ON p.NAME LIKE s.LASTNAME + ', ' + s.FIRSTNAME + ' %'

View 4 Replies View Related

Simple Northwind Query

Nov 3, 2005

Hi

I am re-visiting access for the 1st time in a couple of years to develop a quotations system and I am just messing around at the moment with Northwind trying to work out how it all works.

I have come across a problem that is baffling me....

When displaying the order details table (referenced from products), the product ID is disoplayed in text form, not as a number. Whenever I try and do this with 2 similar tables using the query builder I always get just the other tables number. I have no idea what I'm doing wrong as every field, attribute, join etc seems to be just the same as the sample tables.

Please could someone help a duffer? :)

View 3 Replies View Related

Simple Average Query

Jun 3, 2005

I apologize, I know this has been covered. But I just spent half an hour reading old posts and still can't quite decide how to apply it to what I'm doing.

I have a db that logs surgeries and all their details. One of the new things they want to do is be able to run a list of average cost for a certain surgery, since patients are always asking ahead of time how much it will cost. I have a query (and report that runs from it) that will list all the surgeries and total charges for individual ones for a date range the user specifies. But I can't figure out how to make it calculate an average charge for each surgery. I could if there were always a certain number to divide by, but of course there could be 2 of this type of surgery and 57 of that type.

The query I currently have set up is:

Field: MR#
Table: SurgeryLog

Field: Wname: [LastName] & ", " & [FirstName] & " " & [Initial]
Table:
Sort: Ascending

Field: OperationDate
Table: Surgery Log
Criteria: >=[Enter Start Date]

Field: PatientType
Table: SurgeryLog
Criteria: "SDS"

Field: TotalCharges
Table: SurgeryLog
Criteria: >0

Field: Operations: [Operation1Performed] & Chr(13) & Chr(10) & [Operation2Performed] & Chr(13) & Chr(10) & [Operation3Performed]


Thanks much!

View 1 Replies View Related

Annoying Query, Yet Simple? - Please Help.

Jul 29, 2005

Hi guys,

A friend of mine has recently created a simple database which holds details of childrens story books. It logs the name of the Author, the storyline, its character etc in various tables...

He's currently creating some simple queries for his database. However, there is one query which neither he nor I can figure out, I'll try to explain to the best of my ability.

Basically, the 'story line' field in his database explains the basic plot of the book, some of the plots have the words "son", "dad", "father" and "boy" in them.

Now, he wants to create a simple query, which looks up and displays data which contains the keywords "dad", "father", "son" etc in them. So far he's tried...

Like "*Son*" Or Like "*Father*" etc...

This worked, briefly. The query was bringing up storylines with both contained the words "son" and "father" , which is correct. However, the query was also bringing up story lines with the word "song" in them, or any word which remotely related to one of the keywords, but didn't contain the exact criteria in the query (son, father etc).

In basic terms, how do you create a query which only brings up words that match the EXACT query criteria? In this case, 'Son' or 'Father' or 'Dad' etc? Rather than words which only briefly relate to the criteria, such as 'Song' or 'Fat' etc?

Thanks for your cooperation, feedback and examples would be highly appreciated.

Thanks all,

Jonathan.

View 4 Replies View Related

Simple Query Question ?

Aug 11, 2005

Hi all, been a while since I've been on here.

Not used access in over two years now, and thrown back into the deep to produce some data.

Problem : - Two tables - Both with key field, but second table one to many relationship.

Table 1 - Key Field XYZ
Table 2 - Key Field XYZ Code Value ABC
Table 2 - Key Field XYZ Code Value BCD

What I want to be able to do is create a table that looks like this

Key Field, Code Value 1, Code Value 2, Code Vlaue 3, etc all on one flat record.

Cheers

Swannie

View 1 Replies View Related

Help Needed With This Simple Query

Sep 12, 2005

I'm missing a bit of experience dealing with this query. It's very simple. I'm selecting log activities from COMLOG. However I only want the most recent DateStamp entry of each Cust_FK entry listed.

Because there are more logactivities, there are more/identical Cust_FK values. Would I first have to group these?? I just don't know how I would go about it.

SELECT COMLOG.LogID, COMLOG.DateStamp, COMLOG.Cust_FK, COMLOG.LogActivity
FROM COMLOG;

thanks

View 8 Replies View Related







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