Recover modified tables

[OCM]

Hi,
I was working of troubleshooting a query in my Dev. environment. I thought about posting a sample db (a strip down version) in the forum. To do so, I created a new DB and imported few objects that are relevant to my questions and modify two of the tables (deleted records rename etc.) During the process, I forgot to update the linkage which caused the Dev. environment BE to be updated.
I’ve a backup (older version) of my Dev. Environment in a removable drive (both BE & FE). Another option I was thinking was to create a new Dev. environment form the production DB.

Can you please advise?

TIA
Regards,


OCM

 

[MajP]

Not sure I understand the question. Normally my Dev environment has updates to forms, reports, and code. Normally not a lot of changes to the table structure. So your Dev front end is still the most up to date. The backend could then come from your dev backup or the production.

 

[OCM]

Yes, I imported the tables from Admin BE.
However, when I attempted to run existing queries, reports etc. I get nothing (blank). Am I missing any steps?

TIA
Regards,


OCM

 

[MajP]

So the problem can arise if you import in a wrong order. When you import you want to bring the tables in first before other objects. If you bring in other objects first your queries can get messed up. This may have to do with the "name auto update" where it tries to rename fields in a query. I always create new blank DB. I bring in the tables then compact and repair. Then I bring in queries. Then I bring in forms, reports, and code. Then I compile the code in vba editor and check for any missing references. Then I fix any relationships in relationship window.
Look at your queries and see if you have "Expression" in the column headers now.

 

[OCM]

Thanks,
I understand your explanation. But, as you stated in previous reply my Dev front end is the most up to date. As a result, I only imported couple of tables from Production. I didn’t import any other objects, my up to date queries, forms and reports are only in my Dev environment.

TIA
Regards,


OCM

 

[MajP]

I think you still could get into a problem but not certain. If you delete tables that your queries are based on and then import those tables from another location. I think the name alias problem could arise. Can you post the sql for one or more of your queries that do not work? Without seeing it, that is the only thing that comes to mind. Also is this all queries or just certain queries? Any chance you have criteria in your queries on data that does not exist in production. Example would be you had dummy data in the development table with values that do not exist in production.

 

[OCM]

MajP,
Thanks. The issue happens in Dev environment where queries/reports working before I modified the two tables (tblPICTSDATA & tblAgency) which I import from the production successfully.
Here is SQL for one of my query:

SELECT tblPICTSDATA.PICTSID, tblPICTSDATA.RECIPFTNM, tblPICTSDATA.RECIPLTNM, tblPICTSDATA.PROVNM, tblPICTSDATA.REFDT, tblSTATUS.STATDESC, tblPICTSDATA.ASSIGNDT, tblPICTSDATA.CLOSEDT, tblPICTSDATA.CLSREASON, tblPIUNITS.INVUNTDESC, tblAGENCY.AGENCYNM, tblPICTSDATA.PROSDT, tblPICTSDATA.PROSACCPTDT, tblPICTSDATA.PROSREJDT, tblINVESTIGATORS.INVTYPE, tblLOGIN.USERNM, tblFINANCIAL.FINRRAMT, tblFINANCIAL.LETTERDT, tblFINANCIAL.FINALDT, tblFINANCIAL.SETLMTAMT
FROM tblSTATUS INNER JOIN (tblPIUNITS INNER JOIN (((tblAGENCY INNER JOIN tblPICTSDATA ON tblAGENCY.AGENCYID = tblPICTSDATA.PROSAGENCY) INNER JOIN tblFINANCIAL ON tblPICTSDATA.PICTSID = tblFINANCIAL.PICTSID) INNER JOIN (tblLOGIN INNER JOIN tblINVESTIGATORS ON tblLOGIN.USERID = tblINVESTIGATORS.USERID) ON tblPICTSDATA.PICTSID = tblINVESTIGATORS.PICTSID) ON tblPIUNITS.INVUNTID = tblPICTSDATA.IUID) ON tblSTATUS.STATUSID = tblPICTSDATA.PICTSSTAT;

TIA
Regards,

OCM

 

[MajP]

That is a whole lot of innerjoins. So if one of those tables does not have a match you are not returning any records. I would think you would want to have an outer join on atleast tblPictdata. So for example you have a statustable which has a statusID and statusDesc and all you are trying to do is bring in the description. Assume your development table was this

ID Desc
O Open
C Closed
P Pending

And you changed the data to this in development or production and you have cascade updates selected
ID Desc
Op Open
Cl Closed
Pe Pending

Now you will return 0 records because of the innerjoin will not match anything.. My best guess is your import is fine, but you have a real data issue somewhere. I would simply rebuild that query and see where it fails to return records.

 

[OCM]

MajP thanks.

Thanks, I managed to get the query running now. However, I’m not getting the correct results. As you explained it seems the joins that are causing the problem.

1. To isolate the issue, I did work through the query table by table. Without applying criteria, I ran my query after inserting a table. The first thing I noticed was that when I inserted either of the following two tables (tblAgency & tblFinancial) I didn’t get the complete value for PICTSSTAT field (i.e. 3 = pending assignment) not showing in the query result.
However, before adding either of these two tables and run the query, 3 = pending assignment was in the list.

2. I attempted to edit and change the join to 2nd and the 3rd option but got error. How do I go about changing the join type on these two tables to left join?

I’ve attached a screenshot to give you some idea.

TIA
Regards,


OCM

 

[MajP]

I assume you want all data from tblPicsdata and then record from the other tables when there is a match. So if using the wizard that is likely the second option that says "return all records from tblPicsdata and records from X where they are equal". Then from tblInvestigators to Login you want another outer join. So return all records from tblInvestigators and those from login that match.

Graphically think of arrows flowing out of your main table and and flowing through tables that link to other tables.

 

[OCM]

MajP, thank you.
Since the two tables (tblFinancial & tblAgency) produced error, I manually edited/deleted the default join (I didn’t use the wizard) and changed to the second option “return all records from tblPicsdata and records from X where they are equal" (screenshot attached) I then ran couple of queries and the result seems to be correct.
However, you brought up a good point in regard to the join between Investigators and Login. As you can see in the screenshot, it’s the default join and if I understand you correctly, I need to change this correct? If so:

1. What will be the graphical representation, login the (one) side and investigators the (many) side and
choose the 2nd option in the join properties (…Include ALL records from tblLogin and only those records form tblInvestigators….”?

2. What is the difference between the three graphical arrows (last page in screenshot)?

TIA
Regards,


OCM

 

[MajP]

However, you brought up a good point in regard to the join between Investigators and Login. As you can see in the screenshot, it’s the default join and if I understand you correctly, I need to change this correct? If so:

Not necessarily, if you ensured referential integrity. If every Investigator is ensured to link to a login then you will return all investigators. But imagine you had all inner joins and somehow the login table got wiped out. Then no investigators would be returned and then no data from your main table would be returned. So your arrow should flow outwards return all from Pictsdata and matches from investigators. Select all from investigators and matches from login.

What you are showing appears to be the representation of the join in the Relationship window and not in the query window. Is that correct? They are kind of different.

in the query window I should only have
A line with solid dots on the end (inner join)
Table1 o-------------o Table2
Only show records where there is a match in both tables

A line with a right arrow (outer join)
Table1 ---------------> Table2
Include all records from table1 and matching data from table 2

A line with a left arrow
Table1 <--------------- Table2
Include all records from table2 and matching data from table 1

So assume table 2 is Status and it has values
StatusID StatusDesc
P Pending
O on time
L Late
E Early

And table1 stores a StatusID foreign key, and they are related by statusID.
Assume table 1 has thousands of records and each is supposed to have a statusID foriegn key relating to the status.
If you do not force the user to put in a status ID then that field could be left blank. Or if you allow them to type it in then they may pick B, Z, or some other value. If you do an inner join then any record where the status field in table1 does not match a value in table2 will not be returned. If I want to to show all records from table1 event if there is not a match to status then I need an outer join.

The relationship window is a little different. With the query you can change your join type at any time and join whatever you want. The relationship window is about forcing data rules and establishing the default join type. If you pick a join type (inner choice 1 or outer choice 2,3) that will become the default when you make a query, but you can change it in the query.

If in the relationship window I establish a join between table1 and table2 where table1 has a StatusID foriegn key and table2 has a StatusID primary key, it knows I am creating a one to many relationship. There are many records in table1 (foriegn Keys) related to 1 record in table2(primary Key).
So you will see
oo------------------1 (infinity to 1) lots of records that are pending, lots are on time, etc
However you only see this if you select enforce referential integrity. Referential integrity means you cannot add a record to table1 unless you enter a statusID foriegn key.
There are two other choices cascade delete and cascade update. Cascade delete means if you delete a record from your primary key side then it will delete those related records with matching foriegn keys. You would not want that in this case. If you deleted Pending then it would delete all the records that were pending. But if I was maintaining a table of owners and a table of their cars. If I deleted an owner from the policy then I would want to delete their cars of the database.

 

[OCM]

Thank you for the detail description of the joins.

What you are showing appears to be the representation of the join in the Relationship window and not in the query window. Is that correct? They are kind of different.

I use access 2016 and I took the screenshot while I’m in query window. When I I posted was while in query design window. When I right click and select show table and as I select tables access creates the relationship. So, I thought I was in query window.

I see what you mean by ensuring referential integrity. But, when I select database tools > relationships, I get a default relationship.

… your arrow should flow outwards return all from PICTSDATA and matches from investigators. Select all from investigators and matches from login.

So, from attached screenshot it means PICTSDATA should be the Many side and tblINVESTIGATORS the one side? How about tblAgency related to tblPICTSDATA and tblInvestigators to tblLogin, do I need to revise the join?

You gave good example StatusID & StatusDesc that I can relate in my situation more than once: case:

1. tblPICSDATA
PICTSSTAT (values = 1,2,3…)
tblSTATUS
STATDESC (values = 1pen, 2:closed, 3ending….)

2. tblPICSDATA
CLSREASON (values = 1,2,3…)
tblCLSREASON (values = 1: recovery, 2:no recovery…)

3. tblPICSDATA
IUID (values = 1,2,3…)
tblPIUNITS (values = 1:admin 2:clinical 3rovider

4. tblPICSDATA
PROSAGENCY (values = 1,2,3…)
tblAGENCY (values = MFC, HD, MD…)


TIA
Regards,



OCM

 

[MajP]

Maybe in 2016 it shows the relationship (1 to many) as well as the join type. I have prior version. Regardless, the join type defined in the relationship window establishes the default join type. All that does is create a default, but in the query window you can always change it or break it. So you really do not have to change the default join unless it makes sense. If you enfore referential integrity an inner join is fine because every record in table1 will match a record in table2. When you do not enforce referential integrity then you have to think about the join type.

so imagine I have a table of software trouble tickets and a table of software fixes for a product. To keep it simple lets assume 1 or more software fixes can address a software trouble ticket. This is a one to many relationship. So in my table SoftwareFixes I would have

Fix ID (PK)
other Fields
TroubleTicketID_foreignkey

I am not going to enforce referential integrity because not every trouble ticket would be assigned a fix. Also I may have software fixes being worked that are not directly related to a trouble ticket.
I could do three queries that return the exact same fields, but different joins, and answer different questions.
I could show all open trouble tickets and those with assigned fixes (left join)
or I could show all fixes in work and those that map to an open problem (right join)
or I could show the trouble tickets with a fix (inner join)
In your relationship window you may have set the default to show one of these, so you will have to change it in the query window. Unfortunately once you establish a relationship (which is more about relational integrity) it also establishes a default join type for future queries.

 

[OCM]

Thanks, very useful resources.
I review the join properties and performed few test runs. The query now returns records I expected to see (where I was getting blanks in the past)
I’ll proceed with testing before I deploy it to users. I’ll keep you posted.

Thanks again for all your help!

Regards,


OCM