Access Mixed tables

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

Hello, I'm making a small inventory database for a local school system and I was wondering if it is possible to have one table for each school and then have a "master" table that pulls the data from every other table. If there was a way to do this it would be great.

Thanks

 

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

Welcome to CF.

Not my area of expertise, but I'm sure someone will be around to help you in a bit.

In the mean time could you give us more information, i.e. what program you are wanting to use, how many schools, etc. The more we know about what you are trying to do the better we can help!

 

I've been around for a little while, just haven't posted, but thanks for the welcome. I'm using Access for my program. As far as the number of schools, currently there are 9, plus a central office and a transportation center.

I'm sure this could probably be accomplished somehow using queries, but I don't know whether I should make a master table and have the queries pull data from it or the other way around. And at the moment, I don't know how I'd do it anyways...

I'm still doing my own bit of messing around, I'm pretty good at figuring things out on my own given time and resources, but it's good to have help sometimes.

 

Hi and welcome to CF!

The way I would design this is as follows:

A table called 'schools' which has the schools details (such as address etc) plus a unique identifier.

A table called 'inventory' which has as one of it's fields the school identifier. Set that field as a "Foreign key" with the schools table.

This means that you can query for items, and know which school the item is in, or query on a school for it's items, or even query on a group of schools.

And it allows an item to be moved from one school to another easily - just change the school identifier in the record.

Harry.

 

lizaoreo,

Questions:

1. Is the local school system organized in a heirarchial pure Microsoft Active Directory Domain/Tree/Forest?
2. Will each school maintain their own separate Access database(s) for their inventory?

 

1. Umm, I believe we do have a pure Microsoft AD, but I'm not 100% on that as I'm just a technician and knowledge such as that doesn't just come to me, though I do dig about stuff like that some since I'm a student. From the best of my knowledge and understanding though, that is the case though.
   
   
2. I believe I may have mislead you about the upkeep of this "inventory" sheet I am making. At the moment at least it's more for me and a co-worker to keep up with inventory and what we've finished and such as we are currently going school to school room to room doing inventory, updates, and the like. However, there has been talk of possibly trying to unify the chaos that is our inventory system (currently there are many different people who keep up with different inventories of the same stuff). That being the case, there is a chance in the future my database could possibly become more of a mainstream thing, at which point there is a possibility that each school will have it's own database that it upkeeps and then there'd be a master database that pulled the data from all of the others. But at this time, that's not the case so I'm going to say no for now.

Thanks for the help so far. haven't' had a chance to work on it recently, but I'll hopefully have time soon.

 

Sounds good to me.

I'd avoid the use of ID key fields though. If you used school_name (a natural key) instead of school_id (a surrogate key) as your unique identifier, for example, you'd avoid many otherwise unnecessary joins with the inventory table. Maybe I should elaborate...

Any query against the inventory table will need a school name to make sense. Having quantity 10 of chair style 145 in school 471 is meaningless nonsense. You'll want to know you have 10 computer desks in Jacksonville High. That means you'll have to join to schools every time you query inventory to retrieve that natural key, if you use surrogates that is. On the other hand, if you export schools' natural key into inventory (as a foreign key), most of your queries against inventory won't need to join with schools at all! The school's name is right there where you need it. ;)

Note: I'm oversimplifying a bit. You might have several Jacksonville Highs in different districts for example. So name alone might not be a "good" primary key. But hopefully you get the idea.

 

Crito makes a point that several others have put to me in the past.

IMHO this is a mistaken view for the following reasons:

1) The overhead for a join isn't that bad for any decent DB.
2) In most main DBMS systems you can construct a view to hide the join. I am not good enough at Access to know if it has views or a similar mechanism - but I think it's query system is good enough to get the same effect.
3) Use of the name field for a foreign key causes problems if the school changes name. Say the Rt Hon Blair decides that one of your schools is now an Academy. With my schema you just change the name in the school record. Using the names for the join means you have to change all the names in the item records.

Harry.

 

1) Joins are the #1 source of performance degredation in large scale RDBMSes.
2) The view will still have to use joins, it just masks this complexity from the user.
3) That's what cascading updates and deletes are for.

 

Another reason is your keys will be real-world verifiable.

Say someone else also has a school database and you need to merge your data with theirs. With ID fields you'll have convoluted queries like: WHERE joes_school_id = 4 or marys_school_id = 23 or new_school_id = 835. In fact, I've seen some commercial systems spend 90% of their time doing nothing but translating between one person's surrogate keys and another's.

All that would have been completely unneccessary had they used natural keys instead.

In short, Jacksonville High is Jacksonville High to everyone. School_ID 314 is only meaningful to you.

 

It didn't sound from the OP's original question that this would be 'large scale'!

Exactly my point.

Have we scared Lizaoreo away?

Harry.

 

Heh, nope, I'm still here. Though a bit perplexed that my simple little question became a such an interesting debate topic

Thanks for the help though guys. I think I've got something of an idea as to what I want to do. Just gotta sit down and do it now I suppose.

 

LOL It's fair to say that with anything IT related, underneath what appears to be a simple concept there is a labyrinth of complexity, that if you ever manage to solve and understand will lead you directly to the secret, which more often than not, manifests itself as a giant can of worms.

 

I'd have to agree with Harry on this one. The database probably won't be large enough for the join to make a difference, and a change in the primary key (school name) could cause a serious problem. A plain old one-up primary key that isn't dependent on anything external is worth considering.

 

Okay, I'm going to pick at your brains some more. Is there a way to have a query put a value in by default for a specific field (IE. School name).

I've got a master table for the inventory and a query pulling for one of the elementary schools. I want to be able to be on the elementary schools query Datasheet View and put in data without showing the school field and still have it put in the school name on the master inventory table.

Also, just an FYI though I doubt it matters, I'm using Access 2007 for this whole thing.

 

lizaoreo,

Do you know the difference between table, form, and report?

 

yes, I've taken a class on Access and a slightly more advanced class on databases that just dealt with more Access features.


I might add that it's been a little while and while I'm somewhat familiar with the other features, I couldn't just use one without digging around and figuring it out again. Most of these dealt with queries and the different query types (append, delete, etc). There was also some report/form related material covered. I actually believe in high school I learned some about reports and forms, but only on the most basic level then.