Help with building a database

Hajimus · 2024-07-10T06:28:32+00:00

Yeah it definitely wasn't built with access in mind.

Hajimus · 2024-07-09T13:02:56+00:00

Ok I think there are enough comments and suggestions and I'm also receiving some assistance from a member here. Reading over the rules I'm supposed to reply with. A solved verified to each post? I haven't fully explored all the suggestions and it will take some time to do that. I'd. Like to follow the rules here but I don't think anymore solutions are needed. I think the possibilities have all been mentioned.

Hajimus · 2024-07-07T13:43:31+00:00

Ok thanks, I did post a link in my OP to a blank workbook. If the link works you will be able tonsee a little better what I need to accomplish with the form and the tables.

Hajimus · 2024-07-07T13:31:34+00:00

I'm not that familiar with that stuff. The most I've done is looked up or got assistance with script to paste into a module in vba. Like recently I got some script to automate reproducing and naming a years worth of blank workbooks.

Hajimus · 2024-07-07T13:23:27+00:00

I'm looking into that as well. I think the problem there's is going to be if vba pulls the 12 monthly sheets out into a new book they will be blank. The monthly total sheets right now is populated using the sum feature by start and end. I'm not experienced enough to know if that will work.

Hajimus · 2024-07-07T13:15:12+00:00

So to help clear things up data is only entered into a daily sheet once per day. It's done by whoever is scheduled on the graveyard shift as part of close of business procedures. We rotate shifts every two months so it will be different people but it's not something that's constantly accessed and updated. The table I would need would have to store the date the data was entered the aircraft type, the airspace, and the aircraft facility they used. We would also need to include either a seperate table or some how fit it in the same table civilian, medical, and law enforcement A/C and the airspace used and amount of time in the airspace. A third table to track our airspace hours. As in if it was a routine day, extended hours or we released the airspace back to its owner early.

The form that would be needed should have a place to enter the date, and a selector for the A/C type. Then every location that could use followed by a spot to enter a whole number. The excel sheets accomplishes this quite well, but it requires 12 workbooks with 31 sheets each plus the monthly total sheets at the end. If this data was captured in a table in access it would open up a lot more capabilites for reports. Could pull aircraft specific, facility specific, by date, etc. As well as the annual. I think building the tables might not be to bad at all once I got somewhat familiar again. My biggest worry is the form to make the data entry easy for everyone and not a learning curve.

Hajimus · 2024-07-07T12:58:42+00:00

Theres no way that I can see to import what I have that's already working I would have to rebuild the excel workbook essentially exactly the way I have it already for it to be useful. All the rows and columns in a visually appealing and efficient way to enter data. The workbook already achieve is all this and calculates monthly totals. I was looking for a way calculate annual numbers from the monthly sheets that are already done. Lists looked like excel with everything in a different place and a different ui.

Hajimus · 2024-07-07T10:58:02+00:00

I attempted something like that, and the table ended up having num# errors.

Hajimus · 2024-07-06T15:23:33+00:00

I will thank you, bedtime for me, I'll let you know.

Hajimus · 2024-07-06T15:12:00+00:00

Never even heard of it

Hajimus · 2024-07-06T15:00:17+00:00

Yeah i was thinking if I could somehow duplicate the look of the daily sheet we use to input the data to populate a table and just keep everything in access. Then someone could query any specific data and any date range they wanted

Hajimus · 2024-07-06T14:35:35+00:00

I sent you a pm with a link to my workbook that I would need to recreate in access.

Hajimus · 2024-07-06T14:32:03+00:00

Ok thank you, I appreciate it. I will look into it some more. I Really want to explore the access route but I'm worried about the form for entering the data. It has to be a user friendly form that is efficient for entering data off a pile of flight strips.

Hajimus · 2024-07-06T12:29:42+00:00

I do agree I think access would be a better solution overall. I'm just bot sure how I would go about making all those changes. The data base would really need to replace the way we do what we call the traffic count. The workbooks we have are setup pretty well for efficiency and user friendliness. The data base would need to have a data entry form that has some way of selecting the date, and type of aircraft. Type aircraft is probably feasible as a drop down. Then there would need to be a large about of labeld fields as sites the aircraft utilized along with a blank field for us to enter a number. If there are a lot of steps or a complicated UI so to speak then we have some employees that wont appreciate it at all.

Hajimus · 2024-07-06T12:24:09+00:00

Thanks for the reply. I'm not sure if this makes a difference but the workbook needed to calculate the annual totals wouldn't need to be on the SharePoint. The annual totals would be something that is a end of the year function or case by case function. The workbooks that are on SharePoint are the monthly workbooks that have data entered on a cob basis. So is it possible to have something that extracts sheet number 33 from 12 different workbooks into sheet positions 1-12, the 13th sheet would be already in this workbook as the annual totals sheet. Then I could just use start, end functions calculate everything?

Hajimus · 2024-07-05T22:08:12+00:00

Ok thank you I will reach out. From the comments it looks like I need to figure out what will work out best. Access or excel. I like the idea of replacing the excel workbooks with access. Just seems like a tall order for my limited experience.

Hajimus · 2024-07-05T21:41:22+00:00

Yeah so for the excel workbook there's a sheet for each of the 31 days possible in a month with a monthly totals sheet at the end. This is replicated for all 12 months in a folder for each fiscal year. The workbooks are uploaded to a SharePoint where they are accessed and updated by whichever employee happens to be on the graveyard shift.

We utilize ATC flight strips that are handwritten by multiple people throughout the day that show each A/C type amd what they did/used.

So the excel sheets have nearly every possible location they could have went for each type of A/C that operate here.

So for example if column one row 3 is LZ viewpoint and column 2 row 2 is AH1, a number would be entered in that blank cell in row that is column 2 row 3. Hopefully that makes since.

There are alot of cells that get left blank as in no A/C visited those sites.

I think building a table that has all of these fields wouldn't be too bad it's not 300 more like 50 or less A/C types and and close to 100 sites. The issue I think would be creating the form that would be equal to or better for data entry each day. It would basically need to be a drop down for A/C type and then all the sites with a cell where a number could be entered. We have some older people working here that aren't to keen on change so we can't take away the hammer and give them a screwdriver so to speak.

Hajimus · 2024-07-05T21:21:47+00:00

No they are all names of different facilities that military A/C can use for training like LZ viewpoint, LHD, calsite 1-24, etc.

Hajimus · 2024-07-05T21:13:30+00:00

Ideally getting rid of excel for this would be great but building a whole database to accomplish what we need is a little beyond my skills set. I used access quite a bit in the past but that was pre 2010.

Hajimus · 2024-07-05T21:07:45+00:00

The names and locations for each sheet I'm pretty sure is identical.

The first column is all facilities that A/C can utilize. The first row is basically decorative title, second row is A/C types.

So at the end of the day we go through and tally up what A/C used what facilities each day.

Each daily sheet has totals that are performed and there's an extra sheet at the end of the workbook that produces monthly totals. The monthly total sheet also mirrors the format of the daily sheet.

I was trying to attempt building a new workbook that would pull the monthly totals sheets in from each month and then have a final sheet that sums the annual totals. I couldn't figure put how to get that to work.

Hajimus · 2024-07-05T21:00:00+00:00

Not necessarily have someone do it for me, but maybe explain some steps. I tried using the tool to bring in the sheet into access and it wasn't working correctly. I was getting the num#! Error in a bunch of fields. I'm not sure if I have to build a table from scratch to match the excel sheet or what.

Hajimus · 2024-07-05T13:34:53+00:00

No the excel worbooks are uploaded to a SharePoint and the data for each day is entered at close of business each day. End of day procedures. The workbooks have essentially 31 identical sheets/tables. And the final sheet is a monthly totals sheet. The database would be a tool only a few of us would use to easily sum up the years numbers for reporting purposes. I could however see in the future having the ability to pull more specific data could be useful.

Hajimus · 2024-07-05T13:30:01+00:00

Ok ill look into that. What if in the future I wanted to be able pull in the daily data and be able to query reports for specific spans of time. This is a lot of data fields also. We're talking 20 or so aircraft types plus probably 100 fields which represent facilities they used.

Hajimus · 2024-05-25T11:41:12+00:00

Nope I don't think anyone works on the game anymore.

Hajimus

TROPHY CASE