Access Database help needed

August 13, 2023 4:11 PM

I'm looking to build what I think is a very basic inventory database that I check volumes of certain items three times a day, and am able to look for trends and run reports on. I understand the basics of building databases, and have done so in the very distant (20 years ago) past. I asked on another forum and was talked down to. I'm hoping that you can help. Details within.

I work for a company that has 12 main queues of work. They may increase or decrease throughout the day. I think I have the tables down, but am not sure how to get the relationships down, and how to have a form work properly with the tables below. Here's what I need:

Main Table:
AutoID
Date
Queue_Details
Queue_BucketNum

Table 2: Buckets
BucketID (linked to Queue_BucketNum)
7am
11am
3pm

Table 3: Queue Details
Queue_Details (linked to Queue_Details)
Queue_NumberID (reference to system number)
Queue_Name
Queue_Type (referencing something along the lines of fruit/vegetables, etc)
Queue_SortOrder (plan to use this for reports and queries)

Here's where I get stumped. I think the two relationships are one-to-many. But I can't seem to get a form that allows me to enter buckets for each queue into a grid format (almost like a spreadsheet) three times a day.

I know I'm not anticipating details that you may need so I'll keep an eye out for clarifying questions without threadsitting. I really appreciate any and all help that you can provide.

posted by Mr. Hazlenut to Technology (15 answers total)

I don't like that table 2 has multiple times. Those seem like those would be more like calculated fields, derived from the data in table 1's date field, than actual data you would be entering. Also AutoID in table 1 doesn't seems to be doing anything. Unless that is another link to table 3?
posted by Windopaene at 5:00 PM on August 13, 2023 [2 favorites]

Best answer: Questions for you so I can give an informed answer:
- is each record in Main Table meant to represent a single one of the 12 queues?
- does each queue have only one bucket, or can a queue have multiple buckets?
- what is the primary key of table 3?
- when you say “they may increase or decrease throughout the day,” what does that mean, exactly?
- where in these tables are the “items” you reference represented?
posted by sevensnowflakes at 6:09 PM on August 13, 2023 [1 favorite]

1. I assume you mean Microsoft Access. Is MS Access a requirement?

2. If not, are there any restrictions on where the database can live, e.g. is a hosted SaaS provider acceptable, or does it have to live within your organization's managed IT space?
posted by armoir from antproof case at 6:09 PM on August 13, 2023

Is Queue_Details a unique field on Table 3, like it is the same queue name every day and there are just 12 of them? Can you give us examples of what those values are?
posted by soelo at 6:12 PM on August 13, 2023

I assume the times of day are the three times that you are checking the volumes. is that right? Also, the volumes you want to enter into the form, are those going to be records in Table 2? Like if you have a bin of bananas, you would have one line that is Bucket29094 | 65 | 40 | 8. To get the date and the queue name, you'd be joining back to Table 3 and Main Table. Is all that the way you want this work?
posted by soelo at 6:30 PM on August 13, 2023

Here’s my understanding of what you are trying to accomplish:

Table QUEUE (
...QUEUE_ID (AutoID)
...QUEUE_DATE
...QUEUE_NAME (this appears to be an attribute of QUEUE)
...QUEUE_TYPE (this appears to be an attribute of QUEUE)
...QUEUE_SORT_ORDER (this appears to be an attribute of QUEUE)
)

Table BUCKET (
...BUCKET_ID
...QUEUE_ID (parent key from QUEUE)
...BUCKET_TIME
)

QUEUE has 0-to-many BUCKETs. (You'll likely want BUCKET_TIME as an attribute of BUCKET in the event that the times change or you add or subtract buckets.)

Not really understanding the need for QUEUE_DETAIL since, if I’m reading this correctly, these are single-valued attributes of QUEUE.

You’ll want value table for valid QUEUE_TYPEs, and maybe one for QUEUE_SORT_ORDER if that isn’t a simple ordinal value.

I hope this helps.
posted by SPrintF at 6:49 PM on August 13, 2023

What does the grid format look like? I.e. in the context of your data, what are the labels along the top and side, and what data would go into each grid box?
posted by gible at 11:45 PM on August 13, 2023

Response by poster: First off, thank you all for coming forward to answer, I appreciate it. Here's answers to your questions:

@Windopaene - I put multiple fields into that table because I thought that the concept was to put "like" fields together and each of the fields was a receptacle for a time block's volume.

@sevensnowflakes -

- is each record in Main Table meant to represent a single one of the 12 queues? I meant for table 3 to serve that purpose as in it would like all of the queues and their specifics to be used as a reference or for sorting(i.e.
- does each queue have only one bucket, or can a queue have multiple buckets? The only table with actual storage that is recorded when I do my inventory check is the one with 7am, 11am, and 3pm
- what is the primary key of table 3? I think I forgot to type it above. It was late for me. It would just be an autonumber tied to the main table.
- when you say “they may increase or decrease throughout the day,” what does that mean, exactly? Say the first queue is called 12455, it might be 244 at 7am, 100 at 11am, and 3000 at 3pm. It's variable and that's some of the stuff I'm hoping to capture actually.
- where in these tables are the “items” you reference represented? So this is in two parts. The buckets themselves are stored in the Queue_Details table. The specific inventory are stored in table 2.

@armoir from antproof case - Yes, I meant Microsoft Access. I've always abbreviated it. While the queues do not contain important information, the industry that I'm in requires that it stay in our infrastructure.

@ soelo - Is Queue_Details a unique field on Table 3, like it is the same queue name every day and there are just 12 of them? Can you give us examples of what those values are? Yes, they are the same each day, don't change from day to day and they are unique IDs. The buckets are like Magic Wands, Brooms, Cauldrons. Does that help?

I assume the times of day are the three times that you are checking the volumes. is that right? Also, the volumes you want to enter into the form, are those going to be records in Table 2? Like if you have a bin of bananas, you would have one line that is Bucket29094 | 65 | 40 | 8. To get the date and the queue name, you'd be joining back to Table 3 and Main Table. Is all that the way you want this work? Exactly what I am looking for.

@SPrintF This is really helpful and I'll try it when I log in to work in a few minutes. I think that you are right and I'm potentially using multiple tables and complicating things when it could be less so? If I'm understanding, your method allows me to change the times recorded in the future should it be necessary?

@gible So this is another great question: I'll try to answer but I struggle with this. For each workday, I want to create a new day in the database, and then have a spreadsheet type view with the queue names and numbers on the far left side and columns for the 7am, 11am, and 3pm time volume recepticles. This would give me the visual aspect to the queue as I type them in. I'll also be able to run pretty specific reports to know highs every morning for last 7 weeks, or other ways to look for trends.

I hope this is clarifying what I left out before. I truly appreciate all of your input and assistance.
posted by Mr. Hazlenut at 4:23 AM on August 14, 2023

It may help to consider developing your design via an entity-relationship diagram. Access is a relational database system, so it's best to start with an ERD, so that the relations are well-defined. It might also help to understand database normalization. Third normal form is enough to make queries and updates easy to implement.
posted by SPrintF at 8:38 AM on August 14, 2023 [1 favorite]

Best answer: Thanks for the followup, OP. The problem I'm having is that I don't exactly understand your use case, I think. Here is where I'm at with thinking it through:

- There are 12 distinct queues, and those queues are static
- Each queue contains X items, a number that varies as items are added to the queue
- What you want to do is be able to fill out a form that has a queue as its object and a set of times and counts as its attributes, rather than having the items you are counting be database-backed themselves
- You want to be able to produce reports covering an arbitrary amount of days showing how many items were in each queue at each time

If I am correct that the items themselves are not database-backed, to avoid hardcoding arbitrary times as DB columns in a table, I'd model this with a snapshots table (rather than buckets), so the schema would look like this:

queues
- id
- system_number
- name
- type (I'd implement this as an enum)

snapshots
- id
- queue_id (foreign key, the id of the queue for which a count is being entered)
- date (the date for which the snapshot is being submitted)
- time (the time for which the snapshot is being submitted, so it'd be your 7am, 11am, 3pm, or whatever else)
- item_count (the count being entered)

It's not clear to me what queues#sort_order is for (since you aren't representing your items in the DB), or what queues#date is for (since the queues are static, and the important timestamp is the one where you make the count), so I'm not including those.

So a queue would have many snapshots, and when you went to build your report, you could grab all snapshots from date to date, and look at their counts on each of those dates.
posted by sevensnowflakes at 8:56 AM on August 14, 2023

Response by poster: Thanks to everyone who has stayed along the ride so far. @sevensnowflakes, I think you have me near the end and potentially understanding. Just the word snapshots has shifted my thinking.

The queues#sortorder is so that I can present the order in a digestible non-alpha/numeric fashion to the end users. I should mention that I present this data to my team throughout the day so that they can see their progress.

I have one series of follow-up questions, I think.

Primarily, what does the relationships table look like? I assumed queue_id being the one and snapshots being the many. Is that right? Would they both need to be integers? Would they both be auto_ids?
posted by Mr. Hazlenut at 12:04 PM on August 14, 2023

My simplified solution looks a ton like sevensnowflakes's one. You don't need a relationship table if a snapshot can only belong to one queue. That's contained in the snapshots.queue_id which should be id from the queues table.
posted by advicepig at 2:03 PM on August 14, 2023

Best answer: No problem, OP! Given your follow up about sort_order, I’m going to walk through what I am assuming about the use case in more detail, just to make sure I have it right.

The schema I defined imagines that a queue record has many associated snapshot records, and a snapshot record belongs to a queue record. This is a one (queue) to many (snapshots) relationship.

When I imagine the (web) form you are filling out, I imagine something like:
- There is a select dropdown field that contains the name of each queue, and one queue can be selected
- There are an arbitrary number of snapshot fieldsets
- Each snapshot fieldset has the following fields:
— a date picker that allows you to choose a date (e.g., 8/14/2023)
— a select dropdown that allows you to choose an hour
— a count field that accepts any integer. This is where you would enter the count of the items.

Filling out the form and submitting it would then create N snapshot records with the given attributes that are associated with the given queue.

The reason I’m going through this is because it is still not clear to me why you need to define a sort order at the database level. If I wanted to generate a report that showed, for example, what was happening with Queue Z during the month of August 2023, I would get the records by asking (pseudo)

SELECT * FROM snapshots WHERE queue_id=the_id_of_the_queue AND date BETWEEN ‘2023-08-01’ AND ‘2023-08-31’ ORDER BY date, time;

Then I would iterate through the snapshots and use them to populate a CSV, or build a view, whatever, since they’d be ordered by date ascending and by the hour ascending.

I hope this is helpful! Let me know if I’ve gotten anything wrong about your use case.

(One curiosity I have about the use case is - why do we have to rely on the item count being manually entered rather than extracted programatically via some sort of API call that runs on a cron schedule, for example? If we could get the item count programatically we could completely obviate the need for a web form. But this may not be relevant or possible under your particular circumstances so I’m ignoring it for now.)
posted by sevensnowflakes at 5:46 PM on August 14, 2023

Best answer: Also - just in case I wasn’t clear, there is no need for a join table because each snapshot would have a foreign key (queue_id) that corresponds to the primary key of one queue record; a single snapshot can’t have many queues.
posted by sevensnowflakes at 5:53 PM on August 14, 2023

Best answer: Last thing (sorry) - I realized I didn’t answer your question about the datatype of the primary keys in each table. I work in Postgres, and Postgres has a special datatype for primary keys that is an automatically incrementing integer. I don’t know how Microsoft Access works, I’m afraid, but according to this documentation, it seems like AutoNumber does something similar.
posted by sevensnowflakes at 6:01 PM on August 14, 2023