Query multiple slightly repeated tables (1 Viewer)

[Mr. SLP]

I have multiple tables that connect back to 2 main tables, they themselves, are connected as many to many.
My goal is to get them all into a query so I can then run a report to be able to show that a teacher has the following students (or a student has the following teachers as well as the following equipment) and therefore is responsible for the following low/mid tech devices, high tech devices, and their accessories. This is a level of query I have not worked with and can’t seem to figure it out.



Sent from my iPad using Tapatalk

 

[plog]

I have multiple tables that connect back to 2 main tables

1. That doesn't make sense.

2. You asked no question.

3. You've provided us no details about your tables/fields.

In your next post please tell us how we can help. Then provide us with the relevant data needed to give you that help.

 

[Pat Hartman]

As plog suggested, please post your schema. Make sure we can see how the tables relate.

As for the report, you will probably need to create 1 or more subreports.

 

[Mr. SLP]

As plog suggested, please post your schema. Make sure we can see how the tables relate.

by schema I'm assuming you mean a snipping of the relationship screen? if so here it is. Its a bit of a mess I feel with all the different tables being so interconnected. Part of the mess is that I am trying to set it up to force the users to have uniformed naming of devices since right now everything is being done on excel spreadsheets and each type of device seems to be entered/spelled differently each time.

 

[plog]

You've got to fix your relationships. You have a spider web where relationships should be more like a tree. There should only be 1 way to travel from 1 table to another. It seems every table in your system is directly related to each other, that is incorrect. You've got way to many relationships, you need to throw away everything in your relationship tool and start from scratch. Add just the main table then bring each other table in 1 by 1 and relate them to just one other table. Do that until every table is back in there but related properly.

Then after that we can work on whatever it is you initially posted about.

 

[Mark_]

One other thing, I would include all of the information required for "iPads" in devices. They are devices so you can include the relevant fields. This cuts down some on the redundant tables.

It also looks like you can have multiple entries for the same "Device Name" and each "Device Name" belongs to a "Device Category". This seems very odd as "Name" is normally associated with a unique instance. Is this really a "Device Model", such as "HP LaserJet"?

 

[isladogs]

Concur with the last 2 replies by plog & mark

Amongst the redundant tables, I would suggest you could immediately get rid of any with just a PK field and one other field:
Device_category table - definitely serves no real purpose
Status table - linked to 3 other tables currently but is it really needed?

I have similar code used for booking school rooms or equipment for specific staff / lessons. See attached screenshot.

The code used to populate the timetable grid is based on just one table:

SELECT tblEquipmentBookings.ItemID, tblEquipmentBookings.ItemName, tblEquipmentBookings.BookingDate, tblEquipmentBookings.LessonID, tblEquipmentBookings.Period, tblEquipmentBookings.TeacherID,tblEquipmentBookings.AllDay, tblEquipmentBookings.ClassID, tblEquipmentBookings.RoomID, tblEquipmentBookings.SubjShort, tblEquipmentBookings.Activity, tblEquipmentBookings.Repeat FROM tblEquipmentBookings WHERE (((tblEquipmentBookings.ItemID)=1) AND ((tblEquipmentBookings.Repeat)=False) AND ((tblEquipmentBookings.Period)>0) AND ((tblEquipmentBookings.BookingDate) Between #01/08/2018# And #01/12/2018#));

Creating the placement of items on the grid involves using an array and recordset - the details of which I won't go into here

 

[Mr. SLP]

plog
Ok I am trying to start a square 1 with the relationships and the first issue I'm running into is picking the main table. I want to say that the main table would actually be the teacher/student junction table since everything is then matched up to a teacher/student combo. However, I cant figure out how to add a 1 (teacher student combo) to many device relationship. When I add a

Mark_
I decided to split them thinking that it would make reports easier as there is alot of additional information needed for ipads than most any other device that I have to track. The more I look at it now I see that was a dumb choice. sorry, still learning as I go. To answer your question about the "device name" thing, you are correct this is more like model.

 

[Mr. SLP]

Concur with the last 2 replies by plog & mark



Device_category table - definitely serves no real purpose

Status table - linked to 3 other tables currently but is it really needed?

I realize I have several tables that don’t seem needed but I did it in an effort to minimize the chance for my colleges, who are almost computer illiterate from entering some things in wrong. For example, device category will be used to identify devices used as writing supports/communication/vision support or other functions that they serve. Have the device category preassigned to a list of device names/models I don’t have to worry about them typing something super specific and useless for pulling a report of all kids placed with equipment for communication.

Status is pretty much the same thing. I will be using it to be able to pull a report of all items already placed with kids, ready to be delivered to kids, broken, in for repair, or unassigned. Currently coworkers say the same things a million different ways on the crappy excel sheet that is being used to track what kid has what... I have decided to condense it down to 4 main tables (student; teacher; devices; accessories).




Sent from my iPhone using Tapatalk

 

[plog]

main table is a pretty ambigous term. In the end you are going to end up with every table in the relationship, its just easier to start with what you think will be the center of that picture.

You initially posted about wanting to track which teachers had which students and equipment. So that would lead me to belief that either the teacher table or an equipment table would be a good place to start. Pick one and then branch off from there.

 

[Mr. SLP]

Ok got ya. In this case the main would be devices I guess... sorry for all the dumb questions.


Sent from my iPhone using Tapatalk

 

[isladogs]

Once again, plog beat me to it ...

The example I gave in my previous reply is based on:
- tblEquipment - items available for bookings
- tblEquipmentBookings - equipment item, booking date, booking time/timetable period

For the purpose of display, these are then matched to:
- tblTimetable - TeacherID, ClassID, RoomID, TimetablePeriod

To make a booking, staff just click on an empty slot on the bookings form ....

On a separate issue, did you read the various replies to the customer signature thread that you recently resurrected:

https://www.access-programmers.co.uk/forums/showthread.php?t=243577

 

[Mr. SLP]

To make a booking, staff just click on an empty slot on the bookings form ....



On a separate issue, did you read the various replies to the customer signature thread that you recently resurrected:



https://www.access-programmers.co.uk/forums/showthread.php?t=243577

Yeah I saw it and based on the responses realized that this was something I wasn’t even going to attempt. I’ve got a hat enough time getting the relationships and what not working that I don’t need to even think about going there. Also, coworker, even though we are the “assistive technology” department, are pretty anti tech for some things and if I was to set something up for us to go completely paperless they would utterly flip.



Sent from my iPhone using Tapatalk

 

[isladogs]

Yeah I saw it and based on the responses realized that this was something I wasn’t even going to attempt. I’ve got a hat enough time getting the relationships and what not working that I don’t need to even think about going there. Also, coworker, even though we are the “assistive technology” department, are pretty anti tech for some things and if I was to set something up for us to go completely paperless they would utterly flip.



Sent from my iPhone using Tapatalk

Never mind - I quite enjoyed finding out how to do it anyway and plan to use the idea in one of my own databases ....!

 

[Pat Hartman]

I would suggest you could immediately get rid of any with just a PK field and one other field:

I disagree. These are lookup tables that control values. The only other way to implement this is with combos that have value lists and those are too hard to manage so defining tables is a better solution. Of course, my table maintenance solution (attached) is even better because you get to use it in multiple apps.

The device_name table should be the one called tblDevices. The one you are using as the junction table should have a name that reflects its usage such as tblStudentDevices.

Back to the question. You cannot have separate 1-m relationships in a single query. You end up creating a Cartesian product which will make no sense. Using a slightly different example that will make the problem more obvious:
Student --> classes
Student --> vehicles

Just because classes and vehicles both have relationships with students, doesn't mean that the three tables can be used in the same query. This is what you end up with:

Mary, English, auto
Mary, English, scooter
Mary, Math, auto
Mary, Math, scooter
John, English, scooter
John, English, bicycle
John, English, truck
John, Science, scooter
John, Science, bicycle
John, Science, truck

To create a report with both classes and vehicles, you need a main report for students and TWO subreports. One for classes and the second for vehicles.

NOTE: Do not use embedded spaces or special characters (#) in object names. Also be careful to avoid property and function names.

 

[Mark_]

@OP,

As to having too many files, in general the basic idea is to have few files, few columns, and many rows. This isn't perfect as a description but it does give you a general guide line when you ask "Should I add this as a table?".

For myself, I normally only ever have one table in a database for looking up values. This can have a "Sort" (think status, device type, what have you) and a "SubSort" (based off of a sort value) as well as not only the value looked up but a description of that value so others can understand what it means in depth. This tends to avoid having many of the "lookup" tables you currently have ( Device Category, Device Name, Status, iPad Model, Campus) while making it far easier on you to keep track of what is coming from where and what table you need to update.

Over holiday I may have to spend some time writing up how to do lookup tables and post a sample database. This seems to happen far to often on here...

 

[Pat Hartman]

post a sample database.

You might want to look at what I posted.

 

[isladogs]

Pat

You said you disagreed with my comment but I can see no possible use for either of these tables
- device category
- ipad_models

I'm far from convinced about several other tables shown in the relationships window.

Can't it be simplified to
- student / teacher / campus / equipment / booking plus the 1 junction table Tchstudent

 

[Pat Hartman]

I wouldn't make them separate tables. That's why I posted the sample database. I would keep them as table data though. The only way you can restrict users to a specific list of categories is to make a table that defines all valid categories. For short lists, some people use value lists in combos. The point is that the table provides a "master" list of valid values. If you use the technique I suggested, you can use a single table with a minimum of two columns - tableName and ItemValue. You would need to make the two fields the primary key to eliminate duplication. That means you would also need to keep both fields in your many-side table in order to join the two. OR, you can create a unique index on the two fields but use an autonumber as the PK. Using an autonumber as the PK, makes the joins simpler and also allows for simplified combo usage since for combos to work correctly, they REQUIRE a SINGLE, UNIQUE identifier. You won't get an error if you don't have one but your combo will be flaky.

I do agree that there are other design issues that need to be addressed.

 

[Mr. SLP]

@OP,

As to having too many files, in general the basic idea is to have few files, few columns, and many rows. This isn't perfect as a description but it does give you a general guide line when you ask "Should I add this as a table?".

For myself, I normally only ever have one table in a database for looking up values. This can have a "Sort" (think status, device type, what have you) and a "SubSort" (based off of a sort value) as well as not only the value looked up but a description of that value so others can understand what it means in depth. This tends to avoid having many of the "lookup" tables you currently have ( Device Category, Device Name, Status, iPad Model, Campus) while making it far easier on you to keep track of what is coming from where and what table you need to update.

Over holiday I may have to spend some time writing up how to do lookup tables and post a sample database. This seems to happen far to often on here...

I'm not sure I totally understand what you mean by "few files, few columns, and many rows" I'm assuming files is tables?... A masterlookup table sounds exactly like what I need. In my Googleing all I'm quickly seeing about lookup tables is basically exactly what I'm already doing If you have time I would love to try and do that.