Newbie Confused With (probably) A Simple Query Issue
Oct 23, 2004
Hi, I've looked all over the forum for an answer to my problem and can't find it. This will probably make regular contributors groan, but I'm totally confused and if someone could help that would be ace.
I've got a popular budget airline website (never mind it's URL, I don't like Spam either) and want/need to move it to Access and thence to run user queries via SQL.
So far so good.
So I set up an access DB:-
I've got three tables:-
Airline Info has three fields:- Airline Code (3 letters, primary key), Airline Name and Airline URL.
Airline Routes has four fields:- RouteID (9 letters, primary key), Airline Code (3 Letters), From Airport Code (3 Letters), To Airport Code (3 Letters).
Airport Info has three fields:- Airport Code (3 letters, primary key), Airport URL and Airport Name.
Hopefully anyone reading is still with me.
I've set up the relationships so that Airline Info.Airline Code is linked to Airline Routes.Airline Code.
Now I want Airline Routes.From Airport Code and Airline Routes.To Airport Code to both link to Airport Info.Airport Code (the ultimate idea being to find the Airport Name), and Access will let me do that but therein the problem lies.
Essentially I want a report that would say (eg) Jet Blue fly from John F Kennedy New York to Los Angeles.
All I can manage is to get "Jet Blue from John F Kennedy New York to LAX". I can convert the From airport code to its name, but not simultaneously convert the To airport code to its name. It's gotta be something to do with the query, but I don't know what and have spent two days on this now Anyone a) understand all of that b) know what's going on?
Ta in advance,
Alex.
View Replies
ADVERTISEMENT
Jul 19, 2007
Hey all,
Just found the forum -- love it. I'm hoping it'll help me out with a lot of my problems. I've extensive experienced coding for PDAs, and with other databases or VBA-based programs, but I've just run into a frustratingly simple problem with an Access database I'm building for medical school (I'm a second-year med student).
Here's the situation: I've two linked tables -- e.g., TblNeuroQuestions and TblNeuroAnswers -- in which the primary key in both is the Question # field. I've created a form to easily enter data enter the questions table (question #, question, and 5 answer choices.) What I want to do is have the question text automatically be copied into the corresponding Question field in the TblNeuroAnswers table, so that I don't have to do it manually (the structure of the TblNeuroAnswers is merely Question #, Question, Answer (A,B,C,D,E), and Explanation). In other words, I want the entry of the question data in the main form (the NeuroQuestions form, which enters data in the TblNeuroQuestions table) to automatically be entered in the corresponding Question field of the TblNeuroAnwsers table.
It seems like it should be an easy thing to do; run a simple update query, for example. But I'd like the form do dual entry, so to speak. Any suggestions?
View 2 Replies
View Related
Aug 6, 2006
I am completely new to Access but probably all I will need is a gentle push in the right direction. I'd like to think I'm fairly quick at learning new things. Thank you in advance, here's where I am, (first step anyway):
Table 01 Parts List
ID - name - cost
Table 02 Item List
ID - nick name - description
Table 03 Items to parts relationship (this is where I fall apart)
ID - Item ID (relationship to table 02) - Item nickname - PartID (relationship to Table 01) - Part name - Part quantity.
What I am trying to do:
I resell items in table 02 which are built of various parts from table 01. I need to generate a table that says:
Item 1 is built from 4 of part 1, 2 of part 2, 9 of part 3, and the total cost of parts for Item 1 is x. I want to update the parts list cost on a regular basis and have the item price change reflected.
If I can understand how to make this work, I can ultimately add in labor and overhead etc etc. Baby steps for now.
Thanks again
-Richard
PS
Here's a copy of my sad little db
http://www.biggsandsmith.com/temp/Trial-db2.mdb
View 4 Replies
View Related
Oct 31, 2006
Hey guys i would really appricate some help
I have made severa databses before but im trying to make this one perfect and im curious, when defining tables should you include atributes about an entity that are static?
Its kind of hard to explain what i mean but for example in a shoe shop a staff member gets commision on what they sell (ie 5%) so when producing the table should a coloum be reserved for commision or should this be left out as it will be calculated later on in a querie and if so should i state this in the design section of my database?
any opinions would be greatly received as i have been reading books on sql and ERD's for days and havent been able to obtain any answers
cheers guys
mike
View 1 Replies
View Related
Feb 16, 2006
I have a database with a table containing drawings recieved. Each drawing can have many actions, first action is sender detail, date, and letter ref. Second action is sender detail, date, letter ref and status (ie acceptable / not acceptable)
I can run a query which looks for all drawings with a status of not acceptable but i then lose info on sender letter ref or I can run a query which gives the senders letter details but for all drawings, not jut those not acceptable. What I want is a query which looks at all docs sent by C1 AND not acceptable.
View 1 Replies
View Related
Oct 10, 2006
:confused: Hi i was wondering if any one can advise me, I have two databases and wish to update a an edited table from the second database to the origianl table in the first database. I tried an append query were i could browse for the first database. But this type of query was not suitable as it would add a new set of records to the table in the first database, i only wished to add the edited fields from the second datbase table to the first database table.
I have tried a update query, with the following path in the update to field "C:Documents and SettingsshareenMy Documentsdabatases.mdb bl_transactionMaster". But this is not working, am I totally going in the wrong direction or should i just coding this update instead of using a query. Any Ideas!!!!!! please help
View 3 Replies
View Related
Jul 26, 2005
I have been reading this is how I am able to articulate my problem with some degree of knowledge.
I have 3 tables as Follows:
Table 1. [ID]pk, [Contestant Name],[Event],[Category],[Class]
Table 2. [ID]pk, [Judge Name],[Event],[Category]
Table 3. [ID]pk, [Contestant Name],[Event],[Category],[Class],[Score1],[Score2],[Remarks]
Table 3 is a record of score from a specific Judge to a specific contestant. There will be a record for each contestant from each judge that is judging that specific [Event] & [Category]. therefore if there are two judges and two contestants there will 4 records entered into the table.
Problems 1. (I can't get my tables related properly)
I should not have to enter the duplicate information each time I enter a record into the Table3. The common information should be available within the other tables using queries.
Why i think I have problem 1:
There is a many to many relationship here on the parent form what i want is the [Judge Name], [Event], [Category].
On the related Sub form i want to see a query that shows me parts of tables 1,2,3 for each Contestant that is entered in the matching [Event],[Category] combined with the query of 1,2,3 and be able to enter this judges scores against the contestant Name and then when I change to a new judge I may see the same contestants but be able to enter a new score record against that contestant with a different judge.
In English
Assume you are a judge for a contest you have a form in front of you
Parent form Select your Event, Name, Category from a pull down box in the sub form you are presented with a list of Contestants that are on stage. You enter Score1 & Score2 for each Contestant and the record is created.
Judge #2 is doing the same thing
He selects his Event,Name, Category from the pull down menu on the Parent form and walla the subform populates with the same contestants as Judge#1 and he enters his Score1 & Score2 into the subform
and
What we have is 2 Judges, 5 Contestants and 10 Records created into Table3
Sorry for the length but the last part helps me visualize what I am trying to explain.
End result is I can't get it to work is this a job for a union query ?
View 2 Replies
View Related
Jun 6, 2006
:( anyone pls help...
here's my setup i have 3 tables w/ same field(example Field name: PC)
i need to add all the values to another table because i need to get the quantity..
example Table1: 5 Table2:6 Table3: 7
Table4: 5+6+7 (18 should be in Table4 w/ field name Quantity)
can email me at icetumbler03@yahoo.com tnxx...
really appreciate and need asap.
View 1 Replies
View Related
Aug 8, 2006
I have two tables
Table1 fields: Date, MoneyIn, MoneyOut, (plus other fields...)
Table2 fields: Date (some <> from Table1, other match), MoneyIn, MoneyOut, (plus other different fields...)
Question: Can I run a query that creates a list with
field1: all dates from Table1 AND Table2
field2: MoneyIn
field3: MoneyOut
Thanks
View 1 Replies
View Related
Jan 16, 2008
Hi,
I have two tables.
1. Customer (Id)
2. Transaction (Customer1Id, Customer2Id)
I want to delete all records in Transaction where Transaction.Customer1Id or Transaction.Customer2Id are not matched against the Customer.Id value.
I realise this query is very simple but I cannot get it to work in Access. Any help would be greatly appreciated!!
Thanks,
Ben.
View 1 Replies
View Related
Oct 25, 2004
I work for a nonprofit. Everyone has 10 jobs. I am the only person who uses/has used access. I have a donor database listing names addresses, and different fields for donations made/not made for each year. I do simple queries to pull names from the list that our board members would like to personalize a letter to, and the rest goes to a mailing service for addressing and stuffing of envelopes. This year, we would like to send a different letter to those who have donated in any of the last 3 years. I can pull contact info and the 3 fields (donted 2003, 2002, and 2001) together in a query, but not sure what to make the criteria. If I put >1 (since some of my fields automatically have a zero, and are therefore not null), then I only get those names that have donated all three years. Some may have donated two years ago, and not again, some may have donated only last year, or 2 out of 3 years, etc. I know there must be a way to do this, but can't get my head there having only had basic interaction with access. I know by process of elimination how many there should be, so I will know if it runs correctly. Hope this makes some sense. Bear in mind, I am a novice, and certainly not a software person.
View 3 Replies
View Related
Apr 5, 2006
Hi all,
Another quick Q - am a newbie at Access!
I am performing the following query:
.Open "SELECT DISTINCT([Date]), SUM([Batch Qty]) As [Batch] FROM [" & TableName & "] " & _
"WHERE [Date] BETWEEN #" & sDate & "# AND #" & eDate & "# " & _
"GROUP BY [Date], [Batch Qty]", cn, , , adCmdText
Data Produced:
29/03/200610
29/03/200620
29/03/2006100
30/03/200660
30/03/200680
30/03/20065
30/03/20065
How do I Sum the Totals for the Dates? So it produces:
29/03/2006130
30/03/2006150
TIA
View 1 Replies
View Related
May 17, 2006
OK, I have two tables that have pretty much the same data in them, but, the first table has SOME data that the second table doesn't and I need to get that data into the table that does not have it.
Here's a description of what I want to do:
Table #1 has about 10,000 lines of data with the employee SSN as the ID for the records. In this table are two extra columns of data (HRContact)and(HR ContactCode) that are not always populated in Table #2.
Table #2 has about 300,000 lines of data with the SSN as the ID field. Some of the records that match the SSN's from Table #1 have the data HRContact and HRContactCode, but not all of the records have those fields populated.
So, what I need to happen is for the query to go through Table #1, find the SSN of a record. As it finds each SSN, it goes to Table #2, finds that same record with the same SSN, then looks in the HRContact field to see if there is data there, or if it is Null. If there is data in that field, then it goes on to the next SSN in Table #1 and repeats the preceeding process. If the data in HRContact is Null in Table #2, then it goes back to Table #1 and grabs the HRContact and HRContactCode data for that record and writes it into the HRContact and HRContactCode field for the record in Table #2. the query would repeat this process until it reaches the end of file in Table #1.
I hope this is clear and if you have any questions, please ask me...
Thanks for your help,
Dave
View 1 Replies
View Related
Jul 28, 2006
I have an asset table, an equipment table, and a customer table. The asset and customer table has a field DateOut and equipment table has a field OnHand. I would like the OnHand table to decrement by one each time the DateOut is set to current, Date(). Tried Iif, tried expression builder, now completely lost. Any help would be great.
View 1 Replies
View Related
Sep 26, 2006
First off let me say that I'm VERY new to Access. I've been put in charge of administering a large (to me) database and I've just started working with the insides of the database for about 3 weeks now.
The most urgent thing though is I need to make an "Honour Roll" report for the employees, that is, they get pass/fail inspections and I'm trying to create a query that returns all employees that have 10 or more passes IN A ROW, with at least 2 of those passes being a personal evaluation vs. an after the fact quality verification. There's already a query in the database that returns the employee ID, total passes, total fails. I've managed to add the field that includes type of inspection, but I can't seem to find a way to count the number of PE's in the field.
The second issue is getting the query to ignore all inspections prior to their last fail. I think I can work out moving the date of the inspection into the query, but then using it is another story. If it requires a script of some sort, then I'm hopelessly lost. The last programming I did was BASIC back in school... many years ago.
If I were to break it down into a statement, it would be something like this:
If (employee passes) >= 10 since last (employee fail) AND (inspection type="PE") >=2, then send (employee ID) and info to a report.
Any help with this is appreciated, I'm starting to get desperate!
View 2 Replies
View Related
Oct 5, 2006
In the code of a button, ive got....
Which should be pulling the value in cb1 in the last cat query, but im getting an error saying it cant find the field "|" refered too...
Basically there is a table called Last Cat, and I want to draw the value of cb1 out of it where the catpathid = 5 so i made the query to do that... but now im abit lost....
Me.combo1.Value = [Last Cat Query1]![cb1]
any help would be awesome.
View 1 Replies
View Related
Mar 15, 2006
Hi,
I have a database with a query that returns a list of printers. This is created by finding all the printers in the database that take a perticular cartridge by code. Sample below.
PrinterName
-----------------
Some Printer 1
Some Printer 2
Some Printer 3
ETC
What I want to do is take the results from the query and convert it into a single line of text and place it into a column of a table related to that cartridge code.
So if say the cartridge code '12345' returns a list of printers such as;
printer 1
printer 2
printer 3
I want to take this data and turn it into a line like this;
printer 1, printer 2, printer 3
I then want to take this new line and place it in a column called Desc in another table relative to the original cartridge code.
table should look like this after query.
BEFORE:
CartCode | CartDesc | Price | Desc
12345 | Something here | 10.00 |
AFTER:
CartCode | CartDesc | Price | Desc
12345 | Something here | 10.00 | printer 1, printer 2, printer 3
I hope what I have said makes sence. I am pretty new to access and feel I have done quite well so far. Please can someone help me solve this problem?
Thank you
View 4 Replies
View Related
Mar 14, 2006
Hi, i'm a newbie so please be gentle :)
ok, i have 2 tables, one is for part number and product ID and the other has product ID and Vehicle details.
i know how to set up a parameter query but just can't quite get the result i want, which is to set up a query based on the result of the first query.
in short i want to enter a part number and it comes up with the vehicle details.
Many thanks in advance
Ty
View 5 Replies
View Related
May 28, 2006
Hello, Really hope someone can help me.
I have 2 lots of info in 1 table - nameley customer address & posting address
I need to print a form that always has the posting address on the same side. but in some cases if the postal address is the same as the customer address then it is not filled in. hope this makes sense:
Please Im Really stuck
View 2 Replies
View Related
Aug 24, 2005
Hi all. I am fairly new to access and am trying to do something that shouldn't be that hard. I have a simple form with one text box and a command button on it. I have the command button set to run an update query.
My problem is that when I enter data and press the button, I don't know how to get the entered data into the query?
I know this is simple to do, but since I am new to access, I can't figure it out. Does anyone have a simple example or step by step details that they can share with me?
Thanks
Chris
View 2 Replies
View Related
Sep 22, 2006
Hi there - i'm looking for a bit of help on something that is probably obvious to experienced Access users - i've checked the forum for about 25 min or so and couldn't find a related answer to this question.
I have a select query (QRY2) that reads another select qery (QRY1), and I need to (or rather, i'd like to) add a field to QRY1 - how do I get it so i can view this new field from QRY1 in QRY2?
thanks so much for anyone who could help me!
View 4 Replies
View Related
Jan 25, 2007
Hi,
just finding my way in Access 97 with no real support. I have made a query that creates duplicate records when I only need to see one for a certain criteria (lets say criteria X). This is due to the data feed involved in making the query where criteria X is found in numerous records.
What I want to display is only one record per entry containing an exact match for criteria X.
Finding it hard to articulate this but I would need something that defines:
If criteria X is the same, just display one record.
Hope this is somehow clear as I am struggling a bit. Would be gratefull of some help.
Thanks
View 3 Replies
View Related
Aug 2, 2006
I have written the following query for use with my Automatic Weather Station
10 Min Wind Direction: Val(IIf((([Date]="11/07/2006") And ([Time text]>=1600) Or ([Date]>"11/07/2006")),[CR10X AWS]![Field6],""))
Basically I had some data from 1300hrs on 7th July until 1600hrs 11 July that was wrong and I needed to 'null' the data reading - the above works fine showing a value of '0' for the incorrect data between these times / dates and all the correct values are displayed since then however, since 01 August the field nows shows 0 again - any ideas why ?
Regards
Richard
View 1 Replies
View Related
Jun 12, 2007
I have several queries in access that are linked to excel and when i update the excel worksheet the query information appears in a tabular format, which is fine.
I also ahve a master table in excel which is supposed to update using the info imported from access. This table is based on fixed cell refernces but whenever i update excel with the access info, the majority of the excel fixed cell refernces change to random numbers?
Any ideas?
View 1 Replies
View Related
Jul 5, 2005
My question is essentially about the saving of information into the underlying database. I have been searching the forums and havent had any luck in finding what I wanted. There were a number of similar threads but I couldnt find any replies which could really point me in the right direction.
1. I am trying to prevent the underlying data from being changed until I click the save command button. It cant seem to work since even before I click the save button, the record is already written.
I tried using Before update cancel = true but that prevents me from moving away from the control. Also, I tried using edit locked but that still records the changes once i made it on the form.
What is the most common way of people saving records upon confirmation? And how do you prevent automatic recording? Any advice would be very much apperciated.
TK :(
View 2 Replies
View Related
Sep 13, 2005
Greetings all,
I have done a bit of reading and research on the 'tag' property in forms and tried to set some code. Now I am all muddled as to how to do it and what comes first?
What I am trying to do is if the condition in one field 'time required' is yes then I would like a whole lot of bound controls (fields) not to appear so that the user cannot enter any information by mistake.
The code is below;
Dim ctl As Control
For Each ctl In Me.Controls
If Me.TimeRequired.Value = "-1" Then
If Me.Tag = "Fermyesno" Then
ctl.Visible = False
End If
End If
Set ctl = Nothing
Can someone set me straight.
rbinder
View 1 Replies
View Related
