Tables and relations and PK;s and FK''s and FU's

[Karaline]

Woops Not FU's meant FI's!! I seem to go around in circles. I have read and reread about tables, and I keep losing the plot. i think i have made progress and then lose myself. i have so many books on the subject they are coming out of my ears. they all say similar thing but it seems my brain doesn't like how they are saying it because I am not getting it.

Is a table all of the products or specific types of products? At the moment I have attachment table and addon table all are related to Table2 Docket Data. I originally had products related to suppliers, but saw a database that had them all related to the purchase order tables. and the order tables. i thought i must have made a mistake and related them 1 docket data line to many products/many addons/many attachments.

or should I have kept one table for all products and query them into categories which then become the attachments/addons. headache coming. Am I over complicating it?
I have products that have sub products. this became confusing to me. I have products that are used at same time as other products that are attached to the main product. eg: a D11 Dozer works 11 hours and the day is split as follows

7am to 11am costcode 001.001.001
GPS attachment is used for 3 hours of the day cost code 001.001.001
11am to 6pm with 50 minutes of breaks costcode 002.002.002

At the moment. I would need to enter the above via 2 forms. as i only have one field with the costcodes in it.
That is fine. can do that. but now I have the main problem i keep coming back to. I need to get all of that information into a datasheet type retrieval which can be exported into excel and not look like dog doo doo's.

i store data for work/materials/labour/machinehours completed by subcontractors. all of those items are products.

I haven't even tried to touch on labour as they have time and half and double time. daily allowances, levels of pay. that is not even worth considering how i enter basically a labourhire docket. i am just trying to enter plant and material dockets at the moment.

I thought i had them all thought out but now I am still having docket table issues. i created 2 tables
table 1 has all the basic information that every docket has. no matter what the docket supplies they all have this information to be entered
Table1
Docket number
Date
Company
Claimed Date


Accrued Date
Total Cost - I would like to have this field but do not due to the complexity of calculations of each docket that would need to be performed. Nightmare.

Table 2 has all the other data that every single docket put together has, but not every docket uses. so every row will have blank fields. can't do much about this as i tried having a material docket table and a plant docket table but it seemed to be doubling and that was against the rules as I read them.

Tables is the hardest part of Access. I keep coming back to tables because if you don't get your groundwork done, when you do queries and forms, they won't carry through properly if your tables are not on point. Online Access learning is all about selling. it is difficult to find databases that do what mine does. Purchase orders may be closest database as they are ordering products and then paying for them. but I am actually advanced excel and created a full excel database with financial dashboards and relational workbooks for purchase ordering. lol. maybe that is my problem my brain is excel hardwired. ?

It should be simple. I enter daily receipts for work completed and at the end of month regurgitate these dockets into Excel to be ticked off and claimed against. A basic storage facility. it is the regurgitating I am having issues with.

I can get the information into the tables, but retrieving it is becoming a nightmare. As was advised to me before, I have to create 50 query forms for finance department to open the company of their choice and show all receipts between the dates they choose. they then need to make sure the data is correct. Mark it as Accrued and export it to Excel to upload to their spreadsheets. Why is this so hard? I will pay someone to make me a nice little database. any takers??

[orange]

This is so true.

Tables is the hardest part of Access. I keep coming back to tables because if you don't get your groundwork done, when you do queries and forms, they won't carry through properly if your tables are not on point

Have you worked through one or two of the tutorials at RogersAccessLibrary? A tutorial will take about 30-45 minutes.
You have to work through the tutorial but you will learn a process for designing your tables and relationships to meet your business facts.

Work through 1 or 2 or these.

Entity relationships Diagramming

Class info System
Catering
Consolidated Widgets

It all starts with a description of the business, and a review and vetting of business facts.

It seems you could also review his material on Normalization, and this.

Good luck.

[Karaline]

Yes I have worked through hours of tutorials, and I understand them when i am in the tutorial with what they are using as examples, but then I look at my data and I get a sore head. I can get the basic supplier/resources/operators tables related to each other. they are simple.
I can do it when it is a noun. but I see the information I am pulling from paper dockets that need to be stored and then retrieved and I think in excel. I will just store as per docket requirements. but that is wrong.

it is the bits I need to enter I am so confused over and i am not lying I have watched so many tutorials and they are fine to understand but then i look at the data i have to store and i freak out. i tried in previous questions to separate it. this seemed to work but when i tried to bring all of these tables through to query it didn't work. so I went back to 1st norm and 2nd norm. I just cant seem to get to 3rd norm with these tables.

[Karaline]

I have anthropology/archaeology double major from my wild hippy youth when university was free and a Business Double Major Accounting/Administration from my older tame boring years. I understand business modelling data systems. I sat and did a business model but i get to what data i need to enter and i get unsure.

From previous questions, I have 50 different subcontractors/suppliers all giving me different bits of information on paper to enter and store until they send their monthly claim through.
I then want to pull this stored information and edit and mark it approved so it doesn't appear again unless it is searched by checked Approved box or ALL dockets for that particular company. Once i have checked it as approved i will export it to an excel worksheet for that particular company.

i don't care what they do with the data once i get it to that stage. fly it off the rooftops. i just need to make sure the data is exported neatly and in the correct order of columns.
Due to different data stored for different dockets. I need to create forms to show exactly what the docket sent to the tables. Ok I can do that. But I also need to pull the resource rates, calculations and totals to check.

This means for one docket i might have a query with MainDocketInformationTable/DocketDetailsTable/ResourceTable/AttachmentTable/AddOnsTable.
so far the queries don't like that many related tables.

[orange]

I'm not disagreeing with anything you said. I'd like to emphasize what you said a few posts ago.

Tables is the hardest part of Access. I keep coming back to tables because if you don't get your groundwork done, when you do queries and forms, they won't carry through properly if your tables are not on point

If you are going to automate any of your materials using a relational database, you will save considerable time and effort to get your tables designed to support your business.

If multiple excel sheets is sufficient for you and the business

Once i have checked it as approved i will export it to an excel worksheet for that particular company.
i don't care what they do with the data once i get it to that stage. fly it off the rooftops. i just need to make sure the data is exported neatly and in the correct order of columns.

and you are not "seeing you way through normalization and relational structures", then stick with excel.

Many posters want to jump into physical database, load up some data and then find out there is "rime and reason" to relational theory and concepts. But of course they don't have time to go back and correct things; they continue to frustrate themselves with countless work arounds. Many will say we have too many disparate worksheets, so we're moving to Access --thinking that all of their Excel knowledge will simplify their transition to relational database. It doesn't work like that generally.

Here is a list of things re database design from Barry Williams if you're looking for another go at this.

Any way, good luck with your project and decision.