none
Input data via a join query RRS feed

  • Question

  • Can I input data into one table via a query, if the query is a join query?

    I have 2 tables - one that contains info about airline flights, the other containing flight prices. I want to create a query that contains columns for the flight info (airline, departure airport, arrival airport) and one column for the prices. I'd like to update the prices column via the query.

    SELECT Flights.FlightID, Flights.AirlineID, Flights.DepartAirport, Flights.ArriveAirport, Prices.Price
    FROM Flights INNER JOIN Prices ON Flights.FlightID = Prices.FlightID;

    But when I try to input price data, I get a bing, not allowing me to do so.

    I've tried flipping the query to make the Prices table the main table, but it still doesn't allow me to update.

    I've also created a tabular form (as well as a datasheet form) using this query, and I still can't update the prices.

    I know joins don't always allow data entry, but even on something as simple as this?

    (I can't use an UPDATE query, because each price is individualized. There is no formula.)

    Thanks for any assistance or other ideas on how to accomplish this.


    • Edited by Shoomie1 Thursday, July 27, 2017 6:14 PM
    Thursday, July 27, 2017 6:05 PM

Answers

  • Hi,

    If the price table is related to more than one flight record, then I don't think you will be able to update a joint query, assuming you're trying to update a specific price for each flight.

    You might be able to do it by using only one table and simply use a function to identify the related flight for each price. For example:

    SELECT Price, FlightID, DLookup("DepartAirport","Flights","FlightID=" & [FlightID]) As Depart
    FROM Prices

    Hope it helps...

    • Marked as answer by Shoomie1 Friday, July 28, 2017 7:43 PM
    Thursday, July 27, 2017 6:25 PM
  • Hi,

    A one-to-one relationship is usually between both the primary keys of the tables, and a one-to-many relationship is usually between the primary key of the parent table and a foreign key in the child table.

    When you link tables in a query, you're not really creating relationships. You're just creating a JOIN.

    The problem in your setup is you were joining on two fields with an index set to allow duplicates, which Access is probably interpreting as a "many-to-many" relationship or join.

    Hope it helps...

    • Marked as answer by Shoomie1 Friday, July 28, 2017 8:41 PM
    Friday, July 28, 2017 8:26 PM

All replies

  • Hi,

    If the price table is related to more than one flight record, then I don't think you will be able to update a joint query, assuming you're trying to update a specific price for each flight.

    You might be able to do it by using only one table and simply use a function to identify the related flight for each price. For example:

    SELECT Price, FlightID, DLookup("DepartAirport","Flights","FlightID=" & [FlightID]) As Depart
    FROM Prices

    Hope it helps...

    • Marked as answer by Shoomie1 Friday, July 28, 2017 7:43 PM
    Thursday, July 27, 2017 6:25 PM
  • But when I try to input price data, I get a bing, not allowing me to do so.

    Are you trying to EDIT the existing PRICE record or add a new record?

    Editing should not be a problem.   But to create a new price record then you need LEFT JOIN flight to price.   Of course you will need a field to distinguish between old and new price records for a given flight.


    Build a little, test a little

    Thursday, July 27, 2017 9:25 PM
  • I'm trying to edit the data.
    Thursday, July 27, 2017 9:44 PM
  • I'm trying to edit the data.

    Hi,

    Did you try my suggestion? Just curious...

    Thursday, July 27, 2017 9:46 PM
  • I'm trying to edit the data.

    Does the record exist in the Prices table?   

    Try a left join.


    Build a little, test a little

    Thursday, July 27, 2017 10:33 PM
  • Provided you have the relationship correctly set up and enforced, and the FlightID columns in each table correctly indexed, you should be able to edit the Price column in the query's result table.  However, the best way for data in related tables like this to be edited is via a flights form, within which is a prices subform.

    The usual interface for a one-to-many relationship type like this would be for the parent form to be in single form view and the subform in continuous forms view, but if you wish to see all flights simultaneously in the form you can use two correlated subforms within a single unbound form.  The prices subform's RecordSource would be a query which references the FlightID control (which can be hidden) of the flights subform as a parameter.  The prices subform is requeried in the flights subform's Current event procedure.  As the user navigates form row to row in the flights subform, the prices subform will show the process for the currently selected flight only.

    You'll find an example of correlated subforms in CorrelatedSubs.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates the use of both correlated and nested subforms, using Northwind data as an example.

    Ken Sheridan, Stafford, England

    Friday, July 28, 2017 11:23 AM
  • Yes I did, and it worked. I'm just waiting to see if anyone gives me a more direct way of doing it before I say it's the answer. Thanks.
    Friday, July 28, 2017 2:57 PM
  • Tried it. It still doesn't work.
    Friday, July 28, 2017 3:07 PM
  • Yes I did, and it worked. I'm just waiting to see if anyone gives me a more direct way of doing it before I say it's the answer. Thanks.

    Okay, thanks for the update. I was just wondering in case it didn't work, so maybe I can offer another choice.

    Cheers!

    • Edited by .theDBguy Friday, July 28, 2017 3:08 PM
    Friday, July 28, 2017 3:08 PM
  • I even tried the following:

    The fields in the Flights table have Lookups. So I thought maybe the 3rd tables are preventing this. So I made a copy of the Flights table, and removed the Lookups and created another query using that table. Yet it still wouldn't let me update any field from the Prices (or even the Flights table). But as soon as I removed the Prices join, I was able to edit the Flight table. So it's definitely the join that stopping editing. I just don't know why.

    Friday, July 28, 2017 3:21 PM
  • So it's definitely the join that stopping editing. I just don't know why.

    If you can share a copy of your db with dummy data, we might be able to tell you why.

    Just a thought...

    Friday, July 28, 2017 4:09 PM
  • If you can share a copy of your db with dummy data, we might be able to tell you why.

    I'm willing to do that, but how do I attach a file to my message. (I'm new at this.) I don't see an attachment icon.
    Friday, July 28, 2017 4:56 PM
  • Hi,

    One option is upload it into a file sharing site and post a link here. You're welcome to email it to me, if you're so inclined.

    Just a thought...

    Friday, July 28, 2017 5:43 PM
  • One option is upload it into a file sharing site and post a link here. You're welcome to email it to me, if you're so inclined.


    I'm sending it to your gmail account. Thanks.
    Friday, July 28, 2017 6:00 PM
  • Got it. Thanks.

    Below is a screenshot of your query. I think the problem is with your table relationship.

    What is supposed to be the relationship between these tables? One-to-one?

    Friday, July 28, 2017 6:55 PM
  • What is supposed to be the relationship between these tables? One-to-one?

    Yes. For every flight, there is one price.

    If for a one-to-many relationship a join will work fine, why won't it work for a one-to-one relationship?


    • Edited by Shoomie1 Friday, July 28, 2017 7:56 PM
    Friday, July 28, 2017 7:42 PM
  • Hi,

    A one-to-one relationship is usually between both the primary keys of the tables, and a one-to-many relationship is usually between the primary key of the parent table and a foreign key in the child table.

    When you link tables in a query, you're not really creating relationships. You're just creating a JOIN.

    The problem in your setup is you were joining on two fields with an index set to allow duplicates, which Access is probably interpreting as a "many-to-many" relationship or join.

    Hope it helps...

    • Marked as answer by Shoomie1 Friday, July 28, 2017 8:41 PM
    Friday, July 28, 2017 8:26 PM