Combing related tables with no join...?! (1 Viewer)

KaylaHansa

New member
Local time
Today, 07:06
Joined
Mar 22, 2020
Messages
18
Hello everyone! I really need to do this but I'm not sure if it's even possible. (I'm new to Access and databases in general). So I have a main table, called AllCustomers. I have several other tables (ex: AlarmCustomers, CameraCustomers, ect). The main AllCustomers table primary key has a one-to-many relationship with these other tables. Each sub table has it's own fields (ex: AlarmCustomers has an "AlarmID" field). I know you are probably thinking why not have all the fields on one table, but please do not tell me alternative ways to structure the database. I cannot make changes to this and have to work with what is there. What I'm trying to figure out is if it is possible to create a query or form that displays the records in a list without combing based on relationship. Here's what I mean:

Normally when creating a query that combines the tables the results will look like this:
CustomerName(1) AlarmID CameraID
CustomerName(2) AlarmID CameraID

I need it to look like this:
CustomerName(1) AlarmID
CustomerName(1) CameraID
CustomerName(2) AlarmID
CustomerName(2) CameraID

Each customer may have several alarm and camera IDs. So the customer should be listed more than once, but each individual AlarmID or CameraID should only be listed only once. Is this possible to do, despite the seemingly opposing one-to-many relationship?

If you know how and have any input, it would be incredibly helpful!!!
 

vba_php

Forum Troll
Local time
Today, 09:06
Joined
Oct 6, 2019
Messages
2,884
just reading your post and not producing a replica database to test with, I would say that no, you can't do this without writing code. some of the other guys here may see it another way though. if you want to write literals in your queries though, then it would be possible, but if you have more than 3 records in a table it would not be practical in the least.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 10:06
Joined
Apr 27, 2015
Messages
6,280
Have you considered a cross-tab query? I have only used them once, but it sounds like what you are trying to achive.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:06
Joined
Feb 19, 2013
Messages
16,553
if you can't change the table structure you can build a union query. Not particularly efficient and not updateable but should get you there.

Something like

SQL:
SELECT CustomerID, AlarmID, "Alarm" FROM AlarmCustomers
UNION SELECT CustomerID, CameraID, "Camera" FROM CamerasCustomers
etc

then join this query to your allcustomers table on CustomerID

For the future, it helps us enormously if you provide some example data and the outcome required from that example data, rather than just describing it. The above assumes for example that your ID's are numeric.

Each customer may have several alarm and camera IDs.
with regards design, it may be OK - depends what your alarmcustomers etc tables look like and the ultimate purpose of the db. It may be they should be combined into one table with an additional field to designate 'alarm', 'camera' etc
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 10:06
Joined
Apr 27, 2015
Messages
6,280
Good morning CJ, for my own understanding, why would a Cross-Tab not work? Am I misunderstanding how they function?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:06
Joined
Feb 19, 2013
Messages
16,553
Morning John - how's isolation going? Just battening down the hatches here. Was planning to come out to Italy in May or June for a week or so and also see other friends currently in Venice and Parma, but on hold for now.

Re Crosstab, the OP wants to display the data vertically. from his description he has a one to many relationship between customers and the alarms/cameras tables. So a normal select query would give him the equivalent of a crosstab view since each 'item type' is in a separate table. But that select query would only really work if the relationship was one to one. (i.e. if customer has two alarms and one camera, the same camera id would appear against both alarm id's).
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:06
Joined
Jan 23, 2006
Messages
15,362
Kayla,

Based on your, "I'm new to Access and databases in general ", I suggest you tell us in simple, plain English What you are trying/need to accomplish. I see something like Customers and Equipment, but would like to hear the set up and requirement in your words. There may be a number of options.
Good luck with your project and welcome to the forum.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 10:06
Joined
Apr 27, 2015
Messages
6,280
Morning John - how's isolation going? Just battening down the hatches here. Was planning to come out to Italy in May or June for a week or so and also see other friends currently in Venice and Parma, but on hold for now.
Thanks CJ, for the explanation. It makes sense when it is explained. As I said, CTQs are something I never took the time to understand.

Isolation is going as well as to be expected. Yesterday was NOT a good day for Italy with the record number of deaths. We ha e been told that the North of Italy is still going through the worst and that the South where I am has yet to feel the full force.

The folks here are ta,ing it very seriously and are observing the precautions to the letter. It is pretty bad, but their spirits are up.

Thanks for asking...
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:06
Joined
Feb 28, 2001
Messages
26,999
There is nothing magical about a JOIN, because you can name two tables and write a restrictive WHERE clause. The JOIN syntax makes it easier to avoid the big danger of non-JOIN queries based on multiple tables - the dreaded Cartesian (or Permutation) JOIN where you get essentially a matrix that is the product of two vectors. Big, ugly, and intractable. But instead of writing FROM xxxx JOIN yyyy on xxxx.zz = yyyy.zz, you can make a WHERE clause that includes ( xxxx.zz = yyyy.zz ) and get a similar effect.

However, as noted by several others, as long as you don't have more than (I think) 16 tables, you could write a UNION query based on multiple independent JOIN queries, each between your main table and ONE of the subsidiary tables. I said 16 because UNION queries have a contributor limit. So what is being suggested is that you DO use a JOIN on the individual tables but then merge the result with a UNION query. Does that help put it in better perspective?

Since your main table is your customer list, your JOIN would be the type that says "Show every entry in the product-specific table and only matching entries from the main table." (Fill in appropriate names.) Then make a UNION of those queries, remembering that the type and number of fields have to match for every contributor to the union. So you can't include product-specific stuff in the union unless you can remap it (a.k.a. "fake it") for EVERY contributor. From there, you can use the concept of "layered" queries if you want to filter out something or run aggregate queries for totals, averages, counts, etc. Make the UNION query a named query and you can run other queries (and forms and reports) against it = as long as updates aren't required through the UNION query.

Unfortunately, the performance of this might not be so good because of the order in which things have to be done internally with Access. The actual performance would depend in the size of the tables, of course.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:06
Joined
Feb 19, 2002
Messages
42,970
When you reach a limit on unions, you can union the union. So union 1 unions 6 tables. Union 2 unions 6 tables and union 3 unions 1 and union2
 

KaylaHansa

New member
Local time
Today, 07:06
Joined
Mar 22, 2020
Messages
18
Thank you everyone for your replies. I will look into all the options, hopefully I'll be able to figure it out. Right now the database just has data entry capabilities but I'm trying to add an interactive dashboard. Here's a sample of the structure of the database:
Capture.PNG

The Main Contact and Customer Type fields are all from the main AllCustomers table. Below those, each tab (Alarm, Camera, Network) is it's own table that is related to AllCustomers. I added two yes/no fields to each of the sub tables: DonePending? and DoneBilling? which are not displayed in the form when creating a new customer. So, by default, every new customers = false for DonePending and DoneBilling. I want to create a form that individually displays each new ID that has been entered (be it an Alarm system ID, camera system ID, or network system ID) in a list, but still referencing which customer it is. Once they are activated the user can check the DonePending? box (the label would be displayed as "Activated" to the user) which would move that system ID out of the list and push it to a new list with all the records that equal false for DoneBilling? After it is billed that will be checked as well and the customer will be considered completed. I have figured out how to structure all this except making the lists. I can only make separate lists for each system type, but cannot combine all of them into one. The closest thing I've come to is a LEFT JOIN query with all the tables but it is still a confusing list, not listing each system individually on it's own line.
 

KaylaHansa

New member
Local time
Today, 07:06
Joined
Mar 22, 2020
Messages
18
I wish I could show more examples but I am not allowed to disclose the confidential customer IDs.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:06
Joined
Feb 28, 2001
Messages
26,999
Pat, truly spoken. Sadly, a UNION of a bunch of UNIONs just adds layers to the mass of queries and slows down processing.

KaylaHansa, you said you didn't want to hear comments bout structure. I have resisted for the most part, but please understand that once this gets to a certain level of being unwieldy, you might have no choice but to fix that structure anyway. This method that we are all offering with UNION queries comprised of individual JOIN queries is a stopgap measure, a "band-aid" or "bicycle patch." At some point, this could reach blowout proportions. I'm not saying this won't work - but this approach will have practical limits due to the complexity it adds to your work. When the cost of that complexity exceeds the cost of fixing the structure, you will have an economic reason to fix that structure for good.

Having said that, we can do our best here to keep from giving you too many stumbling blocks. But that design you described to us is a huge stumbling block all on its own.
 

KaylaHansa

New member
Local time
Today, 07:06
Joined
Mar 22, 2020
Messages
18
I'm not sure how to even combine into one table because larger commercial customers may have many sites and therefore several camera systems, each one with it's own modules, site addresses, ect. As for the whole database it's functionality is very simple, just basic data entry/ reporting. I just want to add the dashboard to make activation and billing status easy to track. Other than that, I don't imagine it will need any other additions.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:06
Joined
Feb 28, 2001
Messages
26,999
I'm not sure how to even combine into one table

You seem to still be struggling. I'll try to be more precise and lay out some ideas. Looking at your first entry, I may have to stand back a bit from my previous criticism. This structure is probably workable as long as you kept it reasonably pure.

So I have a main table, called AllCustomers. I have several other tables (ex: AlarmCustomers, CameraCustomers, ect).

This is not necessarily totally wrong. If all customer data that is the type of thing common to all customers is in the AllCustomers table, that is your structural anchor. That is, the information in the AllCustomers table contains NOTHING about the equipment, you are still good. Then, if the ONLY thing in those other tables that have device classes as part of their naems is highly specific to alarms or cameras, but the only info about the customer is a link to the customer ID, then if you have a one-many relationship between the customer's ID and the various equipment tables, you are still in the clear.

The wrinkle that may be in your problem is that you need to clarify how you handle customers with multiple sites. Personally, I would build a site table. Every customer starts with at least one site, but if a customer has more, you make more entries to end up with one customer, many sites. Then your equipment would be linked, not to the customer table but to the site table, which could have its own key. And the site tables would lead you back to the customer table. That wrinkle is for you to consider and explore.

To make a single list of all of that stuff that you have now, omitting site issues, you can make a UNION query like this (which by the way is exactly what CJ told you earlier):

Code:
SELECT CustID, CustName, CustAddr, (etc), "ALARM" As EntryType, AlarmID As ItemID, AlarmType As ItemType, (etc)
    FROM  AllCustomers INNER JOIN AlarmCustomers ON AllCustomers.CustID = AlarmCustomers.CustID
UNION
SELECT CustID, CustName, CustAddr, (etc), "CAMERA" As EntryType, CameraID As ItemID, CameraType As ItemType, (etc)
    FROM AllCustomers INNER JOIN CameraCustomers ON AllCustomers.CudtID = CameraCustomers.CustID
 (etc.)

Then save that as a named query so that you can write other queries that call it to get the master list and do the filters.

You wanted it to look like this:

Code:
CustomerName(1)     AlarmID
CustomerName(1)     CameraID
CustomerName(2)     AlarmID
CustomerName(2)     CameraID

To get that, if my UNION query is called UNQRY then

Code:
SELECT CustName, EntryType, ItemID FROM UNQRY ORDER BY CustID, EntryType, ItemID ;

This will give you records:

Code:
Customer Name (1)   ALARM   AlarmID1
Customer Name (1)   ALARM   AlarmID2
Customer Name (1)   CAMERA CameraID1
Customer Name (2)   ALARM   AlarmID1
Customer Name (2)  CAMERA CameraID1
Customer Name (2)  CAMERA CameraID2
etc.
 

KaylaHansa

New member
Local time
Today, 07:06
Joined
Mar 22, 2020
Messages
18
I will try it out as soon as I have the time. Thank you for breaking it down and for all the help, it is much appreciated!!
 

Users who are viewing this thread

Top Bottom