Query multiple slightly repeated tables

[Mr. SLP]

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

I could see narrowing it down to student / teacher / campus / equipment. However, I am having to track accessories with the equipment and need to be able to give numbers of chargers ect that I have so I really need the accessory table. What all do you picture being in the "booking" table? I literally have a couple of thousand items to track and they are often placed with students for indefinitely, or at least until an evaluation is completed and states that the student doesn't need said item any more. Also, the more I think about it again, I question my need for the junction table. I went into it thinking "each student can have multiple teachers and each teacher can have multiple students so I need the junction". However, since every device is assigned to a student, with a teacher signing off as being responsible for it, the device table functions as that junction table correct?

Also, I feel at least 1 table is needed as a lookup table so I can limit responses.

 

[isladogs]

In your case the booking table would probably be student id, equipment id, start date and end date. It's a junction table

A charger is just another item of equipment. It needs a separate record in the equipment table, not it's own accessory table

I don't understand your final sentence about limiting responses

 

[AccessBlaster]

Also, I feel at least 1 table is needed as a lookup table so I can limit responses

The lookup doesn't necessarily have to be in the relationship, it could be feeding a combo or assisting in auto filling other fields.

 

[Mr. SLP]

Ok charger was a poor example. I’m talking things like an auxiliary cable, usb cord, case, or a carry bag. That specifically will be used with other equipment and wouldn’t be placed on their own and can then be associated with a device as a package and can be easily identified as needing to be picked up in the event that particular device needs to be returned (like if a student leaves the district).

Limiting responses is so that other members of the team (who can barely use a computer) don’t enter a new item in as iPadAir and not iPad Air and thus muddying up the data. Currently the excel spreadsheet that this team has been using for years (I’m new to the department) has the same type of device listed in a minimum of 5 different ways all the way down to things such as “I pad” or “iPad air” (extra spaces making it appear like a different device altogether when I run a pivot table. This is 1 of the biggest reasons I’m going the access route. I’m wanting to setup forms to be able to have consistency with our data entry so that I can get a really good picture of what I have, where it’s at, and be able to better justify the need to purchase new/additional equipment.


Sent from my iPhone using Tapatalk

 

[Pat Hartman]

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.

I feel like I am invisible. That is EXACTLY what I posted as a sample.

If Accessories are ALWAYS related to Devices, then Accessory should be a child of Device.

 

[isladogs]

Ok charger was a poor example. I’m talking things like an auxiliary cable, usb cord, case, or a carry bag. That specifically will be used with other equipment and wouldn’t be placed on their own and can then be associated with a device as a package and can be easily identified as needing to be picked up in the event that particular device needs to be returned (like if a student leaves the district).

Limiting responses is so that other members of the team (who can barely use a computer) don’t enter a new item in as iPadAir and not iPad Air and thus muddying up the data. Currently the excel spreadsheet that this team has been using for years (I’m new to the department) has the same type of device listed in a minimum of 5 different ways all the way down to things such as “I pad” or “iPad air” (extra spaces making it appear like a different device altogether when I run a pivot table. This is 1 of the biggest reasons I’m going the access route. I’m wanting to setup forms to be able to have consistency with our data entry so that I can get a really good picture of what I have, where it’s at, and be able to better justify the need to purchase new/additional equipment.


Sent from my iPhone using Tapatalk

Its all still equipment that you are loaning and that will later be returned.
One equipment table makes the whole process much simpler

To limit responses, use comboboxes with limit to list =yes.
That eliminates all name variation issues at a stroke

 

[Mr. SLP]

I feel like I am invisible. That is EXACTLY what I posted as a sample.

If Accessories are ALWAYS related to Devices, then Accessory should be a child of Device.

By child you mean only related to the device table? Already fixed that. If you mean a child in the sense of another field on the device table that wouldn’t work as it wouldn’t let me pull a report of how many I have being used/unused since it would be a list of things in that field.


Sent from my iPhone using Tapatalk

 

[Mr. SLP]

Its all still equipment that you are loaning and that will later be returned.

One equipment table makes the whole process much simpler



To limit responses, use comboboxes with limit to list =yes.

That eliminates all name variation issues at a stroke

I agree that would make it easier from a data point of view but how would I then be able to know what accessories go to a particular device? My end goal, and what brought up this original posts/question is to be able to print out a receipt, more or less, of all the items that a kid has and then have it list all the accessories with each device where it’s grouped by device so at the top of the page all the kid’s info under that device A, immediately under that all the accessories for device A, then device B and so on. However, I feel the first step to this would be to build a query to pull it all together so I have something for the report to reference.


Sent from my iPhone using Tapatalk

 

[Mark_]

Pat,

Your not invisible, just not being replied to. If you WERE invisible you'd be doing things much more entertaining that posting on here!

 

[Mark_]

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.

Yes, tables... I'm such a long board I keep thinking "FILE" and "TABLE" as they used to be interchangable when talking about how the data was stored... Long long ago in databases far, far away...

Take a look at the sample I'm attaching. Look on the form how the three combo boxes work. NONE uses a PK so they are lookup by value, not lookup by reference. This seems like what you need for your "TAG" file. One thing you may wish to add, depending on your needs, is a Yes/No in the lookup table to say if this value is in use. If NO, don't let new tags be added with it but keep it around as a search/reporting value.

In designer look at the properties, especially how I'm referencing other than the first column for the queries. Likewise I am not showing values other that what the user should see. Please let me know if this gives you a good idea on how you can use a lookup by value.

If you need it by reference, simply toss in the PK and use that.

 

[Mr. SLP]

This makes SOOO much sense seeing this. Thank you!