Joining

[Perfac]

Some time ago Ken Sheridan taught me to combine my business’s 7 entities(Creditors, Employees, Agents, Customers, Cash Suppliers, Banks, other). I call that table t01CombinedEntity. It works well for all purposes. To end up with effective financials it all have to go through Union query, still no issue.
1. I created a table t01Image with foreign key CmbEnt_ID, and field Logo01.
2. The foreign key joins nicely with CmbEntID in t01CombinedEntity and query q01CombinedEntity and records meet with images, no problem.
3. Query q02InvPurchase is joined with q01CombinedEntity and works well after I joined queries q01CombinedEntity and q01Image. The Image field Logo01 is part of q01CombinedEntity but not in q02InvPurchase yet.


4. My problem is when I click on Logo01 to make it one of the fields in q02InvPurchase(to be able to place the image on Invoice); q02InvPurchase does not work correctly any more. I changed the join properties and the query shows all the records but is not updatable.
Im missing something. Should I start drinking alcohol for the first time in my life, or am I just old?

[June7]

What PK/FK fields are q02InvPurchase and t01Image linking on?

Hard to follow your query chains from descriptions. Provide SQL statements or the db for analysis.

[Perfac]

Attachment 32667I thought I attached my DB and a word doc with the SQL of the three queries. I dont see them in the "Box" though. Let me know if you got it.

[June7]

The attachment link doesn't work.

[ArviLaanemets]

https://support.office.com/en-us/art...9-c2b258d5bed7

I'm afraid UNION query falls into 'SQL Specific Query' category!

And I'm not sure about queries based on query instead on table too! (I simply haven't ever thought about trying this - and I prefer to keep it this way )

[Perfac]

As Described previously in this thread, we don't understand why we can't join the image file to q01Creditors, Please read the initial thread.
Here is the attachment for the db:
https://drive.google.com/file/d/1kYh...jk0MuzWcq/view

[June7]

How large is that zip file? It was taking forever for me to download and I aborted it. Can't you make a copy and trim it down - delete data and/or remove objects that aren't needed for the issue and run compact & repair?

[Perfac]

Our choice of process is as follows.

1. Sales people including “Agents” talk to any PROSPECT.
2. PROSPECT gets registered as a PROSPECT who does no business yet. We still want to join Images and keep all usable data of the PROSPECT to do more marketing, not registering bankparticulars or relevants.
3. When the PROSPECT do the first transaction we convert the prospect to a cash CUSTOMER. The PROSPECT will now only be found under CUSTOMER and not under PROSPECTS.
4. The info stay being stored in the PROSPECT table, but the CUSTOMER table will have a few extra fields like bank particulars.
5. There is also a checkbox which if checked makes the cash CUSTOMER a credit CUSTOMER. Fields for relevant’s information will now appear on the form.
6. As you may see in the prospect table the Image table is joined and we place it succesfully to the prospect form. There may be 10 Prospects for every one Prospect we convert to a customer.
7. When converted we now need to display the images on the CUSTOMER form. This is the issue.
8. We don’t know why the same Image table joins succesful in the PROSPECT query, but not in the CUSTOMER query. Or if the PROSPECT query then keeps it all and works, why when we join the customer table, it becomes not updatable.
9. The process is the same with registering cash SUPPLIER first, then converting them to cash CREDITOR or “credit” CREDITOR. We have the same challenge. We get to show the image on the form but the query is Not updatable.

Query Q01Prospect
SELECT t01Prospect.PrpID, t01Prospect.ProspNum01, t01CombinedEntity.EntityName01 AS ProspectName01, t01Prospect.Adr_ID07, t01Prospect.Agt_ID01, t01Prospect.Emp_ID02, t01Prospect.BusTpe_ID02, t01Prospect.MainBus_ID02, t01Prospect.JobTit_ID04, t01Prospect.BusRegNum04, t01Prospect.VatRegNum06, t01Prospect.PhoneNum06, t01Prospect.FaxNum06, t01Prospect.CntNmeSrn04, t01Prospect.CntCelNum04, t01Prospect.EmlAdr07, t01Prospect.Website06, t01Prospect.Chkpst06, t01Prospect.StartDate04, t01Prospect.DiscontinueReason03, t01Prospect.BusDetail04, t01Prospect.OwnerTenant, t01Prospect.EntPros, q01Address.PstCdePO_ID, q01Address.PstCdeST_ID, q01Address.POBoxNum, q01Address.StreetNumber, q01Address.StreetName, q01Address.BuildingName, q01Address.RoomNumber, q01Address.PSuburb, q01Address.PCity, q01Address.PProvince, q01Address.SPstCde, q01Address.SSuburb, q01Address.SCity, q01Address.SProvince, q01Address.PSuburb, q01Address.PPstCde, t01Image.Logo01, t01Image.Photos01, t01Image.Documents01
FROM t01Image RIGHT JOIN (t01CombinedEntity INNER JOIN (t01Prospect INNER JOIN q01Address ON t01Prospect.Adr_ID07 = q01Address.AdrID) ON t01CombinedEntity.CmbEntID = t01Prospect.PrpID) ON t01Image.CmbEnt_ID25 = t01Prospect.PrpID
ORDER BY t01Prospect.ProspNum01;


Query Q01Customer
SELECT t01Customer.CusID, t01CombinedEntity.EntityName01, t01Customer.Prp_ID01, t01Customer.EntTpe_ID03, t01Customer.LdgAcc_ID19, t01Customer.Rlt_ID01, t01Customer.ChkPst14, t01Customer.CkkPst15, t01Customer.StartDate02, t01Customer.EndDate01, t01Customer.Status01, t01Customer.CreditLimit01, t01Customer.AccNumWthSpl01, t01Prospect.PrpID, t01Prospect.ProspNum01, t01Prospect.Adr_ID07, t01Prospect.Agt_ID01, t01Prospect.Emp_ID02, t01Prospect.BusTpe_ID02, t01Prospect.MainBus_ID02, t01Prospect.JobTit_ID04, t01Prospect.BusRegNum04, t01Prospect.VatRegNum06, t01Prospect.PhoneNum06, t01Prospect.FaxNum06, t01Prospect.CntNmeSrn04, t01Prospect.CntCelNum04, t01Prospect.EmlAdr07, t01Prospect.Website06, t01Prospect.Chkpst06, t01Prospect.StartDate04, t01Prospect.DiscontinueReason03, t01Prospect.BusDetail04, t01Prospect.OwnerTenant, t01Prospect.EntPros, q01Address.AdrID, q01Address.PstCdePO_ID, q01Address.PstCdeST_ID, q01Address.POBoxNum, q01Address.StreetNumber, q01Address.StreetName, q01Address.BuildingName, q01Address.RoomNumber, q01Address.PPstCde, q01Address.PSuburb, q01Address.PCity, q01Address.PProvince, q01Address.SPstCde, q01Address.SSuburb, q01Address.SCity, q01Address.SProvince, t01BankParticulars.BnkPrtID, t01BankParticulars.BnkBrc_ID, t01BankParticulars.Act_ID01, t01BankParticulars.AccountName, t01BankParticulars.AccountNum, q04BankBranch01.BnkBrcID, q04BankBranch01.BnkBrcNum, q04BankBranch01.Bnk_ID01, q04BankBranch01.BranchName02, q04BankBranch01.BranchCode01, q04BankBranch01.BankName02
FROM t01Image RIGHT JOIN (((((t01Customer LEFT JOIN t01Prospect ON t01Customer.Prp_ID01 = t01Prospect.PrpID) LEFT JOIN t01CombinedEntity ON t01Prospect.PrpID = t01CombinedEntity.CmbEntID) LEFT JOIN q01Address ON t01Prospect.Adr_ID07 = q01Address.AdrID) LEFT JOIN t01BankParticulars ON t01Customer.BnkPrt_ID07 = t01BankParticulars.BnkPrtID) LEFT JOIN q04BankBranch01 ON t01BankParticulars.BnkBrc_ID = q04BankBranch01.BnkBrcID) ON t01Image.CmbEnt_ID25 = t01Prospect.PrpID;