Help with bookstore db

[mike60smart]

Hi, just one question if I may: I'm going through the ZYX laboratories example, and I'm trying to create the attribute grid, but I'm having trouble assigning primary keys as in the example. Am I supposed to be working in database view or design view? Because it seems that the primary key can only be assigned in design view. But then it's impossible for the attribute grid to look like this (database view):

View attachment 98588
design view:

View attachment 98587

I don't understand how to make the SS# the primary key???

Hi
In the example shown of table "Fields"

The first column shows the fields for tblEmployees and it is recommended that you use EmployeeID as the PK

The Second Column shows the fields for tblJobHistory and it is recommended that you have JobHistoryID as the PK - You then need to add an additional field "EmployeeID" which will be a LongInteger - Number and will be the FK linked to tblEmployees - PK

the 3rd Column shows the fields for tblCertifications and it is recommended that you have CertificationID as the PK - You then need to add an additional field "EmployeeID" which will be a LongInteger - Number and will be the FK linked to tblEmployees - PK

 

[FrJonathan]

@Pat Hartman, I'm pretty sure that D.J. said that the db that D.J. is working with just now is "the ZYX laboratories example," - just trying to learn the skills - not the db for D.J.'s bookstore. So of course it sounds like a school assignment. That doesn't contradict D.J.'s statement about the reason D.J. is learning Access - D.J. wants to develop the bookstore db with the skills D.J. is learning from the ZYX exercise and other exercises.

@D.J.Ida Yes, the place where you assign or change the primary key is, in fact, the design view. You're just using the table like a scrap of paper to organize notes on the data elements you need. Therefore, the primary key on your attributes grid is not important, as you won't connect that grid to any other tables.

Each column of related data on your attributes grid represents a table you will create with that data in it. Each of THOSE tables will have primary keys to uniquely identify each record in the table and - as needed - foreign keys to connect the tables to one another.

By the way, it is NOT recommended to use the Social Security Number as the employee's identification number. It has nothing to do with database management; it creates significant risks of identity theft. I tried to post a link to an article on the subject but the forum won't let me. For some reason, inserting a link causes the website to ID it as "spam-like".

 

[CJ_London]

and I'm trying to create the attribute grid, but I'm having trouble assigning primary keys as in the example.

see my example, using excel in post #7

@Pat Hartman - there is no diversion, OP was working through an example posted by another responder

 

[FrJonathan]

Here's an example of the table organizations you're identifying in your attributes grid. Because each employee can have more than one certification, you'll put the certification data in a separate table, and create a foreign key field that you will use to connect it to a specific employee, by storing a value equal to the primary key from the employee information table. [I added certification expiration because - for obvious reasons - this would be critical information in managing personnel.]

Using the Relationships tab, you will tell Access that the two fields are related, and define the nature of their relationship. (In this case, many-to-one. The "many" side of the relationship is marked with the infinity symbol and the "one" side with the numeral 1.)

The application to your bookstore database is obvious...
Faculty members teach more than one course.
Students enroll in more than one course.
Courses require more than one book.
etc.

However, as you go along, you will realize that...
Faculty members teach more than one course, and the same course will be taught by more than one faculty member.
Courses require more than one book, and the same book may be required in more than one course.
etc.

This will, eventually, require you to establish many-to-many relationships... but you can cross that bridge when you get to it. You have to master many-to-one, first.

 

[mike60smart]

There WAS a diversion. we are no longer discussing the actual application. You are all wrapped up in the sample. Have fun going down the rabbit hole.

Sorry Pat but the Op said he did not understand how to relate the fields.

If he doesn't understand the initial steps then he has no chance of building the database he requires.

 

[D.J.Ida]

If your job is to order and stock the books, you are concentrating on the wrong data. Do not take on data entry/management that is not required to do your job. For example, you have no Order and OrderDetails tables. Are you going to prepare the RFQ's and Orders by hand?

The employee table will have an autonumber PK. When you enter an employee, the BeforeUpdaete event of the form should look for another employee with the same name and ask if the new one is a duplicate. You can't make name the PK because names can be duplicated and you need to allow that to happen.

Job History also has an autonumber PK with a foreign key to EmpID. Do NOT use table level lookups!!!!!

Certifications Could have a natural key but it is better to use an autonumber and make a unique index on the Cert name. You also need an Employee Certification table.

BTW, this is no longer the database you described. It has become all about employees and not about ordering and managing an inventory. Are you saying that the school doesn't already have a database to manage employees?

This is sounding more like a school assignment than a real world application.

No, this is an example from one of the links I was recommended to look into, in order to understand how Access handles data better. It's been 10 years or more since I opened Access, and even then it was a very short course where we were guided on what to d and how to do it. So I'm trying to understand how exactly the person who wrote this managed to assign this PK. I don't even know what table level lookups are.

 

[D.J.Ida]

His attribute grid is simply a grid (not a database table)--could be just on a piece of paper. It is not part of the database per se. Just think of the grid as a piece of paper or notepad. He is leading you through the process of identifying things that should be tables and/or fields and how tables are related.

Alright, got it!

 

[D.J.Ida]

@Pat Hartman, I'm pretty sure that D.J. said that the db that D.J. is working with just now is "the ZYX laboratories example," - just trying to learn the skills - not the db for D.J.'s bookstore. So of course it sounds like a school assignment. That doesn't contradict D.J.'s statement about the reason D.J. is learning Access - D.J. wants to develop the bookstore db with the skills D.J. is learning from the ZYX exercise and other exercises.

@D.J.Ida Yes, the place where you assign or change the primary key is, in fact, the design view. You're just using the table like a scrap of paper to organize notes on the data elements you need. Therefore, the primary key on your attributes grid is not important, as you won't connect that grid to any other tables.

Each column of related data on your attributes grid represents a table you will create with that data in it. Each of THOSE tables will have primary keys to uniquely identify each record in the table and - as needed - foreign keys to connect the tables to one another.

By the way, it is NOT recommended to use the Social Security Number as the employee's identification number. It has nothing to do with database management; it creates significant risks of identity theft. I tried to post a link to an article on the subject but the forum won't let me. For some reason, inserting a link causes the website to ID it as "spam-like".

Yes, I'm trying to understand these concepts; I've used Excel for several years to manage our book orders, but it has become increasingly difficult since the university is growing and offering more departments and courses. I was told Access would be a better choice. The developers are currently dealing with several major projects and cannot work on this one, so I might as well try to learn as much as I can.

 

[D.J.Ida]

I'm not going to attempt to split the thread but I am also not going to participate because I don't want to have to figure out if we are talking about some example or your initial request.

Fair enough; thank you for trying to help in any case.

 

[D.J.Ida]

Here's an example of the table organizations you're identifying in your attributes grid. Because each employee can have more than one certification, you'll put the certification data in a separate table, and create a foreign key field that you will use to connect it to a specific employee, by storing a value equal to the primary key from the employee information table. [I added certification expiration because - for obvious reasons - this would be critical information in managing personnel.]

Using the Relationships tab, you will tell Access that the two fields are related, and define the nature of their relationship. (In this case, many-to-one. The "many" side of the relationship is marked with the infinity symbol and the "one" side with the numeral 1.)

View attachment 98598

The application to your bookstore database is obvious...
Faculty members teach more than one course.
Students enroll in more than one course.
Courses require more than one book.
etc.

However, as you go along, you will realize that...
Faculty members teach more than one course, and the same course will be taught by more than one faculty member.
Courses require more than one book, and the same book may be required in more than one course.
etc.

This will, eventually, require you to establish many-to-many relationships... but you can cross that bridge when you get to it. You have to master many-to-one, first.

OK, I think I'm beginning to see. One of the linked examples mentioned the many-to-many relationships, and if I remember correctly, said that the way to deal with this was to introduce a table as a go-between for the table that are in a many-to-many relationship.

One thing I noticed as I was trying to work out how many ... do you call these tables? fields? I need to create and testing relationships, I am not seeing the symbols on the line connecting these tables. There's no number one, not the infinity symbol - it's just an ordinary line.

And I was going to say that we only have one book per course, but then I remembered that while 90% of courses do follow that, there are a couple of them where the lecturers said they must have an additional book. Students get their assigned reading from the library or the instructors create readers for them.

... yeah I know this is going to be difficult. I am a rank beginner, but my colleagues are absolutely swamped and we're already outsourcing several things. I either have to wait or try my best, possibly prepare the database in such a way that they import the data into whatever system they make for me.

 

[mike60smart]

OK, I think I'm beginning to see. One of the linked examples mentioned the many-to-many relationships, and if I remember correctly, said that the way to deal with this was to introduce a table as a go-between for the table that are in a many-to-many relationship.

One thing I noticed as I was trying to work out how many ... do you call these tables? fields? I need to create and testing relationships, I am not seeing the symbols on the line connecting these tables. There's no number one, not the infinity symbol - it's just an ordinary line.

And I was going to say that we only have one book per course, but then I remembered that while 90% of courses do follow that, there are a couple of them where the lecturers said they must have an additional book. Students get their assigned reading from the library or the instructors create readers for them.

... yeah I know this is going to be difficult. I am a rank beginner, but my colleagues are absolutely swamped and we're already outsourcing several things. I either have to wait or try my best, possibly prepare the database in such a way that they import the data into whatever system they make for me.

Hi
You said "There's no number one, not the infinity symbol - it's just an ordinary line."

If you double click on the ordinary line and select the option "Enforce Referential Integrity" you will get the 1 to Infinity Symbols

 

[MajP]

So lets pretend you have a table of courses
tblCourses tblCourses

CourseID_PK	CourseName
1​	History 101
2​	History 201
3​	Math 101
4​	Math 201
5​	English 101
6​	English 201

And you have a list of books
tblBooks tblBooks

BookID_PK	BookName
1​	Book History 1
2​	Book History 2
3​	Book EFG

Some classes have more than one book, some classes resuse the same book. This is a many to many. A course has many books, and many books can support different courses. There is overlap.
tblBooksCourses tblBooksCourses

bookCourseID	CourseID_FK	BookID_FK
1​	1​	1​
2​	1​	2​
3​	2​	2​
4​	2​	3​

In other words using a query
Query1 Query1

CourseName	BookName
History 101	Book History 1
History 101	Book History 2
History 201	Book History 2
History 201	Book EFG

History 101 uses books History1,2. History 201 continues with History 2 but uses another book EFG.

 

[D.J.Ida]

Based on your responses to me (and in #36 above), YOU are making this application much harder than it needs to be. YOUR application does not need any information about the programs run by the school. All it needs to know is what books are required for what courses for the upcoming semester. And then what students are taking what courses so you know how many books to order and can package them if you have the staff and space to do so. When you want other advice on the app, please start a new thread.

It does need that information, because the admin wants me to present them with a report of every course package within every department, year and semester. I recognize this complicates things, but I don't know how to uncomplicate it for myself and still present the admin with every detail they want to see. I need to have a price comparison for the prices sent by local suppliers and the price difference needs to be visible so that the admin can decide which supplier they will choose.

It's a back and forth process: I collect the information of all course textbooks from the dean of each department, send the price request to suppliers, they send their offers, I prepare them for the admin so the price difference is clearly visible, the admin says alright, we will accept the offer from this supplier. Or they may say, No, they're both too high, let's negotiate. And one of the suppliers may say, we cannot offer a more affordable price, so we will withdraw. Or they may say, Alright since we've worked together for years, here are some discounts, so that needs to be entered into the table and again presented to the admin. Then when everything's been decided, I need to transfer the rows containing books from publishers we order from directly, and those who only work with intermediaries, i.e. one of the suppliers. I worry that during that process I might miss a textbook which will not be ordered on time, and will have to be requested once the semester has already started.

I am trying to simplify this process, but I do not know how. It's been many years since I used the program, and do not have experience with database design; I'm a humanities major. I haven't said that you personally must help me; if your time can be spent more productively helping someone else, thank you again and I will continue to struggle through trying to learn some complicated concepts.

 

[D.J.Ida]

Hi
You said "There's no number one, not the infinity symbol - it's just an ordinary line."

If you double click on the ordinary line and select the option "Enforce Referential Integrity" you will get the 1 to Infinity Symbols

Oh OK, I didn't know that, thank you. I will keep that in mind for the future.

 

[D.J.Ida]

What you need to do is to recognize what data is required to do your job -- buy books. Even knowing WHICH students need which books is technically beyond your need. You need to know how many students, not which students.

Start with that. If the data regarding the departments and course programs is available elsewhere, let them make the report. You should NOT be duplicating all that data. They should be giving YOU this report not asking you to recreate the wheel and manage data that has nothing to do with buying books. Do YOUR job, not theirs. The intersection between the other applications and yours is the list of courses for the next semester and which books each course requires. That is what they give to you. You create RFQ's, order the books once a vendor is selected. Stock the books, distribute them to students based on what classes the student has signed up for. The other data already exists in some other application. Use it if you need to but do not duplicate it.

Learn how to separate nice to have from necessary. If you focus on your job, even with little experience, you will have something within a couple of weeks that you can use. One of us could do the core application in probably a week. It really isn't very difficult until you complicate it with unnecessary data collection and management.

"They should be giving YOU this report--" that would be nice, but it's not the reality. Is there a way to make this work within the constraints of what I have to do?

 

[D.J.Ida]

It is nearly midnight, and I will be retiring, but could someone tell me if I'm on the right track when it comes to organizing my tables or entities, if that's what they're called? I haven't yet attempted to create any relationships before I determine if I'm missing some information, or if some of it is redundant. There should be a quotes and orders table, where I could input the prices, but I wasn't certain if these should go together or be separate, or in the suppliers table.

Academic year is 2022/23, school year is Year I, II etc. Titles of address are Assistant, Assistant Professor, Professor, Docent etc. Supplier type is local or foreign. Status ordered, arrived, late, out of print etc.

 

[D.J.Ida]

No, you are not on the right track. You really, really, really need to concentrate on YOUR job. Three of the fourteen tables are relevant to ordering books. I'm done.

Again I thank you for your time, and repeat that the reality is that I also have to do these other things. Book orders are just a small part of my job.

Can someone tell me which tables they are (I'm guessing books, courses and suppliers) and how to integrate the less relevant tables (which I nevertheless have to take into account because that's what the management wants to see) and how to make the necessary forms and queries for the multi-step process: 1. collect information from deans, 2. send price queries to publishers and suppliers, 3. enter prices as they come in, possibly change them if there are negotiations to lower them, 4. generate a detailed report for the admin with all the student packages and their prices (e.g. year 3 CSIS package with EE costs this much, with ECON this much, etc. etc.), 5. generate firm orders for publishers and suppliers, 6. keep track of the status of three separate orders (direct-order publishers 1 and 2, and the chosen local supplier for other publishers, so I need to associate those publishers with the suppliers we work with).

Thank you in advance.

 

[D.J.Ida]

They are NOT your priority. Leave that to the IT people. Do what YOU need to do YOUR JOB. You can actually accomplish YOUR needs in a week or two if you are a good student. That job appears to be ordering the correct books in the correct quantities from the most favorable quote. Secondarily, you need to manage inventory. Thirdly, you need to distribute to students. This can be simple, they walk in and tell you what they need or what courses they are taking and you figure out what they need OR you somehow assemble all the books for each student together. This is nice but not necessary. It will be a royal pain if you don't have every book you need when you start the assembly process. You will have to keep going back and finding the right student pile, breaking it open, and adding a book at a time to it. Each time, wrapping the package up again. This is a serious waste of time and will introduce errors into the process. If you insist on doing the job of the Curriculum department, you will be at this for a year. Just let them give you the data and as long as the data includes the list of books for each program, all you need to do is to assign final prices to them. OR, you give them the final price list by book and let them make their own report.

When you waste time and money on duplicating and managing data kept by other departments, you are taking value from the students. That time and money could be better spent on reducing tuition or offering other services.

Thank you for your advice, but we obviously cannot see eye to eye. I keep telling you what our current process is, as directed by the admin, and you keep telling me what I should change. That is not an option at this moment. We DO assemble all books for each student. Everything is ordered a few months in advance. The shipments get unpacked, and the books for a particular year are stacked together for each student, so when they come in and say, I'm student of this department and year, my colleague looks up the list of courses and books for that department, semester and year and gives them the package. They sign for it, take it and leave.

The IT department already has projects underway, and are already outsourcing some of the work because they cannot do it themselves. I on the other hand cannot wait for them to create a module that will connect to other parts of the in-house system to pick up the number of students and the information on whether there's been any change of courses since the last semester or if they're using the same books. I came to ask for help on how to do this particular task under these particular conditions. I understand you believe the process should be adjusted. Again, this is not an option. I would like to get this database working, as best as possible, by the time the fall semester starts. Again, respectfully, if you don't see how your advice can help with this task, perhaps someone else would benefit from your help. Thank you for the effort you put in so far.

 

[D.J.Ida]

Also, has it occurred to you that I would like to learn this program for myself? I am an amateur bookbinder, and lately people have been contacting me to bind blank and printed works for them. I thought it would be interesting to try and make a database in which I could track these requests and the resulting items. It's also another skill I could add to my CV; I'm proficient in Word, fairly adept in Excel and PowerPoint, but know nothing of this particular Microsoft product. If I have to attend classes live, I will, but I had hoped that someone would have an idea about how to try and solve this issue by the time our next semester comes.

 

[D.J.Ida]

You are correct. I cannot help you. I was trying to get you to concentrate on the heart of the problem first. Good luck with your project.

Thank you. Believe me, I would like to just focus on the specific things you mentioned, but I cannot. All of these other things are also my duties.