Copying and summing records named in a table a given number of times as specified in that table

**AgesofDetail** · 02-05-2025, 08:36 AM

Hello,

I am trying to make an inventory/project supply planning database for personal use.

I have one table with every possible supply listed, and its units in a second field.

I have a forum with dropdowns to select up to twelve supplies from that first table, and a second field for quantity without units shown.

I have a third forum/table that has the number of times I plan to make each project, and the number of times out of those planned that I have actually done so.

I would like to have something that looked up each record for each project listed in the last table, times however many instances are planned but have not been made, and sum up all of the supplies needed for everything, displayed with the relevant unit from table 1 where applicable (some things do not have units)

I have not been able to figure out what tool or combination of tools to use for this, or find an effective way of looking it up (I suspect I'm missing some key vocabulary.)

**madpiet** · 02-05-2025, 09:34 AM

"copying and summing" just sounds SOOOO wrong.

Can you post a diagram of your table design? (or an empty database... don't build forms yet!)

What is this database supposed to do for you?
"a form with dropdowns to select up to 12 supplies" sounds extremely suspect. Normally one would put something like that into a subform, since it's pretty much functionally equivalent to an Invoice Line Item table.

**NoellaG** · 02-05-2025, 09:44 AM

Maybe better start from the beginning. It doesn't sound as you have much database or programming skills. So let's look at the project from the start.

First decide how and where you want to use it? On a local PC/laptop? On your smartphone? On the web?
Do you need reports you can open on every workplace from the web?
Take a look at existing packages like MS Project, or other project management tools, maybe the perfect tool for you already exists.
Exel and Access both have templates doing part of what you want.
If you want to develop it yourself, what are your skills? Are you willing to spend time learning how to design a database and program the user interface?

**AgesofDetail** · 02-05-2025, 09:52 AM

Originally Posted by NoellaG

Maybe better start from the beginning. It doesn't sound as you have much database or programming skills. So let's look at the project from the start.

First decide how and where you want to use it? On a local PC/laptop? On your smartphone? On the web?
Do you need reports you can open on every workplace from the web?
Take a look at existing packages like MS Project, or other project management tools, maybe the perfect tool for you already exists.
Exel and Access both have templates doing part of what you want.
If you want to develop it yourself, what are your skills? Are you willing to spend time learning how to design a database and program the user interface?

Ive looked and couldnt find a template-- Im wanting to do more than this as well, this just seemed like my first point of stuck. You are correct that this is only my second database I've made, and the first did not have nearly as much complexity. I am explaining further in a reply to the first response.

**AgesofDetail** · 02-05-2025, 10:29 AM

Im not specifically wanting to copy-- just get the totals.

I figured I was probably going about this massively wrong, as its only the second time I've done a not from template database.

My end goal is a template that I can use to track various sorts of projects requring lots of different supplies measured in different units, that will tell me how much of each I need for a given thing. Twelve was just picked as a semi random starting point.

Eventually, I would like to link this up to my inventory spreadsheet, and add in prices for supplies in inventory and price ranges for supplies not in inventory. I would also like to add dates to it eventually, as in "if I did projects A, B, and C each by their own due date, how much of each supply would I need by X and what is the price range/ money already expended on those supplies?" or also "If I do X instances of this project by the end of february, what is the total amounts, prices, etc? " I would also like to add the ability to put time in hours and minutes, and to have the ability to have projects also be supplies-- for instance project 1 is a supply for project 2.

Im not specifying a use case here as I have several where I've been wanting the same template for a few years now. At this point I realized that I should probably just spend the time to learn how to do it properly, since nothing premade I found worked.

My smallest use case has a supply list of under 70, my largest over 3600. Hence the idea of having a table of all of the items and unit, and then inputting projects initially as a set of those items referenced from the table, which from what I read seemed best done via forum with lookup? Eventually I would want to add deadlines and hours/minutes to each project, and prices/price range per unit to supplies, but I am aware that that is way more complicated. I would also like to add categories and subcategories (and perhaps sub-sub-categories) to the 3600 supply plus use case, but I actually do know how to do that I think.

So my intended starting place is basically what I said above-- input number of times of a series of projects, output combined list of all supplies with units.

This is replacing increasingly horrible excel crimes, that got to the point where I realized I really did need to learn how to properly database it.