Query multiple slightly repeated tables

[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!