edit data using a query 1

[spongie1]

I am using Access 2002. I have been trying to find the proper source information to read on this topic. I am not finding it.

I want to use a query to edit my table Production Speeds. I want to the query to list all machine/product combinations in a production group and provide the ability to add production speed entries to match those groups.

I will try to accurately describe what I have done so far.

table Production Groups
tblGID
tblGName

table Machine Names
tblMID
tblMName
refG

                    Machine Group Table   
                   / (Left Join)     
                  /                     
Production Group -                  
Table

I have a table that lists production groups, and I have a table that lists machines and has a reference to the production groups table.

I have used a Left Join to join the two tables. I am able to use the query to determine which groups do not have machines listed and add records to the machine names table.

I want to take this one step further, but I am not finding good information.

I have two other tables:

Product Names
tblPID
tblPNumber
tblPNames
refG

Production Speeds
tblSPDID
tblSPDStandard
refP
refM

I want to show all the production groups next to all the machines and products. I want to show them even if a machine or a product does not exist for that particular group. However, I also want to link this grouping to the production speeds table so that I can associate a speed with each combination of product and machine in each production group category.

Given my current understand (minimal), what I am trying to do seems like a logical extension of what I have already done.

                    Machine Group Table   
                   / (Left Join)       \ (Left Join)
                  /                     Production Group -                       - Production Speed
Table             \                     /  Table
                   \ (Left Join)       / (Left Join)
                    Product Names Table

I am getting the impression that the only way I can do what I want to do is by using a MAKE-Table query to contruct a table, then possibly join that table to the production speeds table. This might not work because it would still require two left joins between two tables. Can I edit a elements as I have above when my query has two left joins instead of one?

Make a query from the 3 tables listed below and call it NewTable
                    Machine Group Table   
                   / (Left Join)    
                  /                    
Production Group -                      
Table             \                    
                   \ (Left Join)      
                    Product Names Table


Use NewTable to link to the Production Speed Table
            ___________________   
           / (Left Join)       \ 
          /                     NewTable -                       - Production Speed
          \                     /  Table
           \ (Left Join)       / 
            -------------------

Is what I am trying possible with other SQL programs (limitation of Access), or is what I am trying fundamentally incorrect?

How would I achieve the same objective through other means if what I am trying is not possible?

 

[rac2]

Production Speeds (call it ProductionSpeeds )
tblSPDID
tblSPDStandard
refP
refM

Machine refM produces product refP at tblSPDStandard speed.
A machine may make many products; the same product may be made by many machines. There are no speeds for machines without products, nor for products without machines.


Product Names (call it Products)
tblPID
tblPNumber
tblPNames
refG

Group refG makes product tblPID.
A group may make many products or it may make no products.
A product is made by a single group or it is not made by any group (refG IS NULL), (which is kind of wacky but maybe the meaning is that we have a product but we do not know which group will make it.)


Machine Names (call it Machines)
tblMID
tblMName
refG

Group refG uses machine tblMID.
A group may have many machines or no machines.
A machine is used by a single group or it is not used (refG IS NULL)


Production Groups (call it Groups)
tblGID
tblGName

These are the groups, tblGID.


"production groups next to all the machines and products."
"a speed with each combination of product and machine in each production group category"

SELECT  g.*, m.*, p.*, ps.*
FROM ProductionSpeeds ps
JOIN Machines m ON
    m.tblMID = ps.refM
JOIN Products p ON
    p.tblPID = ps.refP
JOIN Groups g ON
    g.tblGID = p.refG
    AND g.tblGID = m.refG

Looks to me like you are totally on top of it. This query will not have any group that does not use a machine to make a product with a recorded speed. Probably all you need to do to get every group is make that a RIGHT JOIN of the Groups table.

 

[spongie1]

Ok, I know how to display the information from multiple linked tables in the query.

In some queries I can actually modify the table entries. What are the rules that govern this capability? Where can I read about how I can setup complex queries to modify the table entries?

 

[rac2]

There really is only one SQL rule, an UPDATE statement can update a single table.

Values from other tables can be obtained through correlated subqueries which return single values. SQL Server offers an extension to SQL that facilitates this, a FROM clause which can JOIN tables and provide values for the update. Probably Access has some features for this. Since these are extensions to standard SQL you will need to read specific to Access. Wish I could help you, but I don't know much about Access, other than to have formed the opinion that the online help that comes with it is very poor. I would start a new thread with your last post seeking documentation on Access update queries.

 

[spongie1]

Thanks rac2. I gave you a star. I appreciate your assistance. Now, I have a starting point.