OK, I'm starting over - DB Basic help needed

[MikTylor]

Hi
I posted a thread in the tables section but two things have made me decide to try again.
1) No one answered my Question
2) I Asked my question in a way that was VERY confusing.

So I'm trying again here. I have a DB that will be used for invoicing clients. Some of the data is variable between jobs, clients & vice versa. Some of the data is recurring.

I have several tables that I need to link. They hold information that is labourious to enter over & over in a spreadsheet so I thought a database would be the answer.

One table is called tbl_Consumables
This table has data which varies from time to time but not all data is changed (IE the price of an item may change from month to month. This presents a problem because if I use a table to store the consumables data & the table is linked to an invoice via the ConsumablesID field I can't change any of the consumables data without changing past invoices that should show the old price not the new price.

Basically I can get my head around how the basic design should be set up. I have two main problems.
A) Trying to link a days work to an invoice & be able to change data in the future without affecting past invoices and,
B) I don't know how to set up the relationships & tables so info is linked.

I have a copy of the DB here. The tables are only minimal fields & data at the moment as I don't want to go to all the trouble of setting this up & then having to change everything.

Thanks in advance.
Regards
Mik

 

[Junkee Brewster]

I'll help you Miki. Gimme a few hours, and I'll post ur db (though it would help if you were more specific, and your db HAD a table called "consumables"). I'm also going to have to make several assumptions because alot of it looks jibberish to me. But, you should be able to see sort of what I've done.

In the meantime, I suggest you do a couple of things: These forums are really fantastic and you can learn a lot and also get a lot of problems solved, but it seems like you need to develop some fundamental basics, outside of the forum too.

Advice: Look for Beginners tutorials on the internet. There's some good ones
Also, you know there's a wizard in access that will help you build a database? I'm not saying use that to MAKE your database, but perhaps have mess around with it and build a few using their wizard and look and see how it's all put together.

 

[Junkee Brewster]

P.S If you're around, could you explain to me what PLODS and HOLES are or how they relate?

Also, do you have SET rates per client, or will they vary per job?

 

[Junkee Brewster]

Ok,

It's very rough - totally unformated and half of it is running from tables (I run off Queries), but it's very late and I've been working 14 hour days on my longweekend on Access. It works, and most importantly, it will show you about relationships and how you don't have to keep typing out the same stuff over and over if you get it right.

Without you explaining properly, I've just had to guess at what you were trying to achieve. Personally I'd set it up rather differently, but it's just to give you an idea.

You need to learn about Queries. And soon. When you get your relationships right, they let you "join" data from different tables together - a big help if you look at the few I've made and how I used them. You also might consider adding more identifying information (i.e. Job descriptions, start dates or what have you). That's why I added some extra client details and Comment sections for starters.

Not knowing how you do your rates is an issue; I've compensated by making two rate entry methods, though, the Invocing form is built assuming rates are allocated per job, not per client. You'll just have to work it out.

This is a db at its most raw. You've got a lot of work to do. Now go look at some Tutorials! (Db is A2000 format)

 

[MikTylor]

Junkee

Thank you very much. I've taken onboard what you've said about the wizards etc & promise I will do my best to work this out. I have tried looking for answers elsewhere, I also searched for 2 days on this & two other sites for answers to my problem before I posted my question. The problem I have is that I'm a monkey see monkey do kinda person, I really needed some examples that were close to what I was dealing with.

As an access beginner I find the program is a little daunting when you start poking around in other peoples raw designs without someone showing me as I go. Regardless of all this, I really appreciate that you took the time to check it out for me. I'll run it now & have a look at what you've done then post back with results.

By the way, in answer to a couple of your questions, the rates do change between jobs not clients, some clients may have repeat business but the rates are done from quotes per job as each job has many variables to consider when doing the initial quote.

Plods and holes, the Plods are daily accounts of work performed. There may be one or there may be many holes completed in one day. Some holes continue for weeks while others are completed in several hours. Plods only ever relate to one client and thus one job. Brief rundown on how it all works.

We are a drilling contractor. Companies pay us to drill exploration holes for minerals. We charge by the metre with certain types of drilling being different rates to others. The rates to drill are worked out in increments. For example RC Drilling (Of which we can drill to a max depth of approx 150 metres) is charged like so, 0-50 metres is say $20 / metre, 50-100 metres is $25 / metre & 100-150 metres is $30 / metre. We can also do Diamond drilling to around 800 metre depth (Using diamond impregnated bits) which is charged in 100 metre increments, like so 0-100 is $50 / metre, 100-200 metres is $60 / metres etc. There are several types of drilling so there are several different rates in the rates table applying to each one. Each plod records the metres we drilled and at what metre increment they were drilled. So If a hole continues over more than one day it may be recorded in the holes table as a different record each time. In addition to this, we also charge our clients for other things like consumables (Drill fluids etc) or maybe things like mobilisation to & from site. These items may or may not relate to a particular hole. I figured the best way to do that was to have the consumables records linked to the plods rather than an individual hole as there may be times when we have no actual drilling done but there are charges to the client in the form of consumables, time billing or mobilisation etc. So as you have seen, the actual setup was doing my head in.

Also, I realise I need more info in the DB, but I didn't want to create 1000 fields only to have someone tell me I need to start over. I was being cautious because I have already re-created this DB in several attempts and it was getting very frustrating.

Thanks again & I'll post when I've had a look.

Cheers
Mik

 

[MikTylor]

Hey Junkee

I am playing with the DB you sent, wow, thanks for all that work! I haven't yet determined exactly if it will do as I think I need to add ALL my required fields first then see (In particular fields for rates, jobs etc.) I'm plugging away here just so you know I'm making use of your hard work.
I'll let you know & maybe post the resulting DB again for you to see.
Thanks heaps.
Mik

 

[Junkee Brewster]

No worries Mik,

I hope it helps you with the relationships. Once you see how they work then you can link all sorts of data together.

You had the right idea in many ways already. Your rates table relationship was off - you need to make sure you have a field the will correspond exactly to the table you were linking it to, that's why you couldn't get your rates to work, because TblRates needed a JobID field in order to link it to the right job.

I also like to try and figure out things myself by looking at how they work, but there comes a point where you really do have to take a more systematic approach, and do some reading, before you ingrain bad habits (like me and VB at the moment haha). If you're full-on interested in Access, consider getting a book, some may snort at the "For Dummies" type books, but they're pretty handy for a beginner, and they only cost about $30 australian (I just bought a big VB one heh).

Best of Luck

 

[MikTylor]

Junkee
Yeah I see what you mean now about the rates relationship. I think I originally had it linked to plods.

I've gone ahead now with trying to add all the fields I need & I have struck another little glitch. My tables have alot of fields (Particularly rates & holes) which is becomong a problem for form design. When I get the bulk of it done I will re-post it. Maybe you could check it out for any suggestions on how to deal with large field counts?
Cheers, I'll be buying a dummie guide tomorrow!!!
Mik

 

[Junkee Brewster]

Sure Mik, I'd love to see your progress.

Some ideas about dealing with lots of fields. You could mean that your tables now have lots of fields (going horizontally across your form), or you could mean that because you've entered so much data, your form display data is growing longer and longer, and hard find the record you want.

If you like to compartmentalise, or prefer ease of display, or if only some parts of data need to be entered in one go, instead of just inserting a long subform, you can insert a tab form. The button is the one left of the "insert subform" button on tool bar. Then all you do is set up your smaller subforms like you usually would, and insert the different subforms onto each tab (linking parent and child fields like normal too). BINGO. Then you can select the tab you want.

Say we're looking at your jobs. On your main form you'd perhaps have the client details, subbed to the job ID details, or even just have the Job ID details. In the tabbed form, you could have one tab for job rates, and a tab for say, materials used, one tab for your PLOD summaries, one tab for start and end dates and locations and so on. That's the easiest way for a beginner I reackon.

Sometimes a horizontally long subform is better from a data entry point of view (take it from an old data-enterer!) - they look bad and don't DISPLAY well, but INPUT I find faster. But it comes down to individual tastes.

If your data display is getting vertically too long, there's many ways to combat that (archive functions, search functions, setting queries to display certain date ranges etc), but they're more complex and maybe I'd need to make an example? Too hard to explain here now.

 

[MikTylor]

Junkee
Hey very cool with the sub fors tab idea, I didn't know you could do that. I'm still working away at the the new db but unfortunatley my work (The one that earns the money, is taking my time up at the moment. But I am still plugging away at this thing & as I need to have it up and running very soon (To send invoices for the work thats earning money) I'll hopefully have something close to working very soon.

I may need some advice on the sub-forms tabs idea but it can wait awhile as the main thing I need right now is a db that actually works, I don't care how ugly it is. (And beleive me its ugly LOL)

Thanks again, catch you soon for an update on my progres.

Mik

 

[MikTylor]

Oh dear, Junkee I'm afraid I've made a complete hash of it!

Got things working ok but its all gone down hill since I started trying to do subforms with a consumables table I added.