2 queries: 1 I can edit data/1 I can't

[MaryMoseley]

I have a 1 to 1 relationship in this contract admin database, because each month, I import data from a contract management spreadsheet. The imported file has all possible contracts to close. The additional info I research before I can close contracts is put in the Info table. The contract # is the primary key for both tables.

Same two tables are used in both queries, same join properties (even believe I may have copied). Property sheet properties are the same. Basic difference is pulling Ready to Close and Pending to Close

I researched all the details about how/when queries can be updated. Nothing stands out as applying, plus one of these works! This will probably be something I call myself a dummy about, but comparing the two queries, I haven't figured it out!

Here is the SQL for Pending to Close, which doesn't let me edit:
SELECT Info.[Close Status], Info.Pending.Value, Info.[Pending Completed].Value, Info.[1797 sent?], Info.[Status Date], abfP2P.[PIIN-SPIIN], Info.Comments, Info.[Sect:Status Date], abfP2P.Contractor, abfP2P.Section, Info.[LISSR bal?], Info.[Z Ship #], Info.[Z date], Info.[Final Pay #], Info.[Mod MOCAS=EDA?], Info.[Prod Complete?], Info.ULO, Info.[Closed Using], abfP2P.[Paying Office], abfP2P.FDD, abfP2P.[Physical Completion Date], abfP2P.[Overage Dt], Info.[ACO Rmk added]
FROM abfP2P LEFT JOIN Info ON abfP2P.[PIIN-SPIIN] = Info.[PIIN-SPIIN]
WHERE (((Info.[Close Status])="pending"))
ORDER BY Info.Pending.Value, Info.[Status Date];

SQL for Ready to Close, which lets me edit fields:
SELECT Info.[Close Status], Info.[Status Date], Info.[Sect:Status Date], Info.Comments, abfP2P.Contractor, abfP2P.[PIIN-SPIIN], abfP2P.Section, Info.[LISSR bal?], Info.[Z Ship #], Info.[Z date], Info.[Final Pay #], Info.[Mod MOCAS=EDA?], Info.[Prod Complete?], Info.[Closed Using], abfP2P.[Paying Office], abfP2P.FDD, abfP2P.[Physical Completion Date], abfP2P.[Overage Dt], Info.[Sect:Status Date]
FROM abfP2P LEFT JOIN Info ON abfP2P.[PIIN-SPIIN] = Info.[PIIN-SPIIN]
WHERE (((Info.[Close Status])="ready") AND ((abfP2P.Section)="1" Or (abfP2P.Section)="2"));


What else do you need to know?

 

[CJ_London]

Where to start

sorry to say your field names are a mess, using ?:#, you really ought to revise these names.

here is a link to explain the implications for not changing them - it may even be one of the reasons for your problem.

http://support.microsoft.com/kb/826763


Left joins may or may not allow you to edit - the table to the left of the join will always appear whilst the table to the right will appear when there is a match, otherwise it returns null values - null values is not a record so cannot be edited. So I'm surprised your second query allows you to edit - but perhaps you are only editing the left table

In your first query you have

Info.Pending.Value, Info.[Pending Completed].Value

the .value implies a field called Pending.Value so I'm surprised you can even open this query without being prompted for an input for Pending.Value etc

 

[MaryMoseley]

My original teacher said not to have spaces, etc. in the field names but last year a teacher said it was okay. I figured Access had changed so I developed casual names. I changed this so I don't have special characters or spaces anymore. Good feedback. Thx.

My right table is the info imported (abfP2P). I do not make changes to that data, since it will be replaced each month. Info is what is important. My left table is the Info table. With one to one relationship/primary key is the same, which join property: 1, 2 or 3? Seems like any would be fine.

For this comment: the .value implies a field called Pending.Value so I'm surprised you can even open this query without being prompted for an input for Pending.Value etc.

I don't get a prompt but I think this field is my problem (plus one more field with same properties.) I compared the queries field by field and these fields are the big difference. These are combo boxes which allow multiple values (anything pending). I guess I could set up y/n for each possible pending item but there are 10 possible pending situations and I don't want to add 10 fields. Is there another way to set this up?

Thanks so much for your help!!

 

[CJ_London]

spaces are a matter of personal choice, but typically spaces can slow down development - you have more typing to do (spaces and square brackets) and if you get it wrong can take a long time to find the problem, particularly in complex queries

My right table is the info imported (abfP2P).

Not in your queries

...FROM abfP2P LEFT JOIN Info ...

These are combo boxes which allow multiple values (anything pending).

Didn't appreciate you are using multi value fields. Regret I don't use them because I find them too restricting for the type of work I do - which often requires allowing for upsizing to SQL server and multivalue fields only work in Access 2007 and later.

I think using the .value means the query returns all values in one field which isn't editable. For example if you have record with two values selected your query returns

[ID], [Value1,Value2], [Field3]...
To make it editable I think you need to drop the .value and the query will then return two rows

[ID], [Value1], [Field3]...
[ID], [Value2], [Field3]...

each of which is editable (subject to getting your join right). As I said, I don't use them but I think in the second example if you edit [Field3] the change will be reflected in the other row(s).

The alternative way is to use a lookup table or a child table but you would normally edit this through a form rather than a query - you shouldn't really be using queries (or tables) to edit data directly, but edit through a form

 

[MaryMoseley]

I have only been developing this database since April. I guess I usually develop forms when everything is "complete"/working well. I am not very knowlegable at this time--I don't have much time and I develop any knowledge as I come across things that I want to do or things that don't work.

my Q about one to one-does it matter which join to use?

You have been a GREAT help! Thank you.

 

[CJ_London]

Which type of join only matters depending on what you are trying to do

1. Inner join will only return records where the fields match
2. Left join will return all the records from the left table and only those records which match on the right table
3. Right join is the other way round

left table/right table does it matter? yes

Typically the left table is the one side of a one to many relationship and the right table the many side. In a one to one relationship normally one table can be considered the 'primary' primary table and the other 'not primary'. Think of a dancehall where two ladies are doing the waltz together - one of them has to take the male lead, but it doesn't matter which one

If you have an invoice header table and an invoice detail table, you have one header record and many detail records.

So if you want to show all invoices, regardless of whether there are any detail rows you left join from the header table to the detail table - detail rows will show and where there aren't any rows, the header will show with a blank row for the detail. If you inner join, then invoices without any rows will not show. If you right join you'll get all the detail rows but no headers where they don't exist.

Normally for forms you would use an inner join, but it does depend on what you want the form to do. However, again depending on what the form is intended to do I would not use any joins on a form. Using invoices as an example the main form would have a recordsource of invoice header and this form would also contain a subform with just the invoice detail table as a recordsource. The 'join' is then set by using the linkchild and linkmaster properties.