Invoicing / Inventory (1 Viewer)

[RainLover]

I get an email every time there is a new post. I decided to read the whole lot again.

If you guys don't do something Christmas will have come and gone. BTW 2 Days. Very good. A lot better than I could have done.

You have a base sell price. Normally a supplier gives a discount rather than a special mark up. It really does not matter as we can handle anything.

Create a table with all the items, Effective Date, Sale Price and most likely UOM. Maybe some more but you can do that latter. So now we have a Table with all the details about the stock. Later we can even use this to do a stocktake. But much later.

Next you need a table of Customers. Name, Address, Contact, and anything else you want. I would aslo give them a Code. But not Autonumber. People can remember code but not numbers. Besides you need the Autonumber for the Primary Key. So now we have customers under control except for one thing. You need a default Discount/MarkUp rate and the date effective. You can get away with the Date if you don't want a History.

Shall we dance. Putting these two tables together requires an Invoice Table. This Table handles the Foreign Keys of Customers. You would display this in a form. Last we need a sub Form for the invoiced items and pricing.

New TblInvoiceSub Contains the Item, UOM and default sale price. If it wasn't for the discount you would be finished now, but we are not. So add the Fields, Discount (Which you get from the Customer table) and Nett Sale price. This Nett price is automatically calculated by using default discount and Base Sell price.

All Done.

Does this suit you. There is some fancy stuff you may wish to add but you now have the basics. From NOW do not use paper. Go direct to your Access, create your Tables and finally your Relationships.

Please post your first draft when you are ready. Include some dummy data. It make it easier to follow.

Apologies for the poor English. I rushed this a bit.

 

[focus12321]

I get an email every time there is a new post. I decided to read the whole lot again.

If you guys don't do something Christmas will have come and gone. BTW 2 Days. Very good. A lot better than I could have done.

You have a base sell price. Normally a supplier gives a discount rather than a special mark up. It really does not matter as we can handle anything.

Create a table with all the items, Effective Date, Sale Price and most likely UOM. Maybe some more but you can do that latter. So now we have a Table with all the details about the stock. Later we can even use this to do a stocktake. But much later.

Next you need a table of Customers. Name, Address, Contact, and anything else you want. I would aslo give them a Code. But not Autonumber. People can remember code but not numbers. Besides you need the Autonumber for the Primary Key. So now we have customers under control except for one thing. You need a default Discount/MarkUp rate and the date effective. You can get away with the Date if you don't want a History.

Shall we dance. Putting these two tables together requires an Invoice Table. This Table handles the Foreign Keys of Customers. You would display this in a form. Last we need a sub Form for the invoiced items and pricing.

New TblInvoiceSub Contains the Item, UOM and default sale price. If it wasn't for the discount you would be finished now, but we are not. So add the Fields, Discount (Which you get from the Customer table) and Nett Sale price. This Nett price is automatically calculated by using default discount and Base Sell price.

All Done.

Does this suit you. There is some fancy stuff you may wish to add but you now have the basics. From NOW do not use paper. Go direct to your Access, create your Tables and finally your Relationships.

Please post your first draft when you are ready. Include some dummy data. It make it easier to follow.

Apologies for the poor English. I rushed this a bit.

Sorry but I am unfamiliar with UOM?? Also each customer doesn't have a "discount" they have specific pricing for different items. I believe spikes comments have helped with this. I now have a working price list, or at least it seems that way lol.

Now one last thing with this, is it really necessary to assign an auto-number to each customer (for the key). We have an accounting software where we already have assigned numbers (auto) for each and Id like to just use those. No real way to reuse the same one.

 

[RainLover]

UOM = Unit of Measure.

eg lbs, Miles. Each

Re Auto Number. Feel free to use whatever you like. Just remember a Primary key is a unique identifier for each record. As such it should never be seen by the end user.

Good luck with the project. You are been helped by a couple of the best.

 

[focus12321]

UOM = Unit of Measure.

eg lbs, Miles. Each

Re Auto Number. Feel free to use whatever you like. Just remember a Primary key is a unique identifier for each record. As such it should never be seen by the end user.

Good luck with the project. You are been helped by a couple of the best.

Hm, if it should never be seen by the end user (makes sense) maybe I should just use a ID (autonumber) then our job number in the following column (row) whatever you want to call it. Probably best.

 

[RainLover]

Exactly.

And don't worry about missing numbers in the sequence. It means nothing.

Instead of calling it ClientID and the same for the foreign key ClientID try ClientPK and ClientFK.

All these little things help.

There was an article written by someone at UA on Primart Keys what they are and what they are not. If interested do a Google and see if you can find it. It is a very good read.

 

[focus12321]

Exactly.

And don't worry about missing numbers in the sequence. It means nothing.

Instead of calling it ClientID and the same for the foreign key ClientID try ClientPK and ClientFK.

All these little things help.

There was an article written by someone at UA on Primart Keys what they are and what they are not. If interested do a Google and see if you can find it. It is a very good read.

Sorry just want to clarify this, but pretty sure I get it. So even though the primarykey would be an auto number the way all the other tables interect with this table would be the foreign key (possibly my job number)?

TblCustomer
-------------
CustomerPK (autonumber)
CustomerFK (number) <-- my job # and the "relationship" to other tables
CustomerName (Text)
CustomerAddress (Text)
Ect....

^^Correct^ ^ ??

 

[RainLover]

Sorry No.

The Primary Key is Singular in the parent table. It cannot be duplicated.

If we want a Child Table, say Employee for Parent and EmployeeDetails for child.

The child Table has its own Autonumber as Primary Key. So does every other table in your Database. No exceptions.

The Foreign Key has the Parent PK. as the same value so therefore we can create a Join.

So Parent Table

AutoNumber

1
2
3
4

Child Table

PK AutoNumber

1
2
3
4
5
6
7
8
9

Plus a Foreign Key that Joins to the PK of the Parent Table and therefore is the same as the Parent Table.

FK Integer
1
1
1
2
2
3
5
5
6
7
7
7

We can have only ONE PK of a certain number. Unique.

The FK can be duplicates of the PK from the Parent Table hence you have a One to Many Relationship.

Hope I explained this right.

Please understand this thoroughly as you design every Database .along the same lines.

 

[RainLover]

If you still have a problem then create a new Database with Two tables. Add some data and create the Join.

Then post it here for clarification.

 

[focus12321]

Please look at my attached file and see if these relationships look right. Id attach the file but I now need to start from scratch. I'm going to add the Primary Keys to all be autonumber as suggested.

The relationship/table im most concerned with is the one for "pricing" as you can see I created a table that combines items and jobnumbers so I can get a unique price for each customer. But do I need to set relationships from the "sheetsT" on items and jobnumber to that one table. (Hope that makes sense im pretty tired right now)

 

[RainLover]

There are a few loose ends and a couple of things going around in circles which we don't want.

Please finish your renaming and alter the relationships if required then post the Database here.

We need to give you a bit of a nudge in the right direction.

I am going on a break for a while but, I'll be back.

 

[RainLover]

I'm back. So where is your Database?

 

[focus12321]

I'm back. So where is your Database?

Attached is my current database. Just to be clear on how workflow happens. We have clients with contracts (jobs) and because there are these contracts the pricing for material and labor hours are specific to the job. There are a lot of maintenance jobs, with these we have "field sheets" that come in and tell us what a technician did and we turn these around into invoices. The next problem that will arise is that they usually want theses invoices to be totaled for a month and given as a monthly price but I think we can do that with queries and reports. or Possibly another table so I can track the totals over the year. (a bill for each month).

 

[RainLover]

focus

This is looking pretty good. I found the use of # on one occasion. Fix it when next you stumble on it.

Most people here at AWF use tblEquipment instead of EquipmentT. The reason is that there are some places where Access shows all the Tables and Queries together. With the T at the end the sort will be a mixture of Tables and Queries. Using tbl The sort will be tables first then queries. It is good to keep things tidy. In 6 months when you come back you won't remember what is what. So do things that make life easy for you.

Problem.

Table EquipmentT is related in a circular design.

EquipmentT, SheetT, InvoiceT , EquipmantPriceT, EquipmentT and we are back to the beginning,

ItemT does the same thing.

I can't sort it out due to limited knowledge of your data base. I hope you can.

A hint. Spread out your tables so the joins show properly and then Print to an A3 printer. It might help you get a better picture that way.

Catch you later and well done so far.

BTW I saw that you used Middle Initial. You could if you wanted, not a must, use MiddleName. Then you can use the function Left to pull the Initial or use the full name on other occasions. This is one of those FYI things.

 

[focus12321]

focus

This is looking pretty good. I found the use of # on one occasion. Fix it when next you stumble on it.

Most people here at AWF use tblEquipment instead of EquipmentT. The reason is that there are some places where Access shows all the Tables and Queries together. With the T at the end the sort will be a mixture of Tables and Queries. Using tbl The sort will be tables first then queries. It is good to keep things tidy. In 6 months when you come back you won't remember what is what. So do things that make life easy for you.

Problem.

Table EquipmentT is related in a circular design.

EquipmentT, SheetT, InvoiceT , EquipmantPriceT, EquipmentT and we are back to the beginning,

ItemT does the same thing.

I can't sort it out due to limited knowledge of your data base. I hope you can.

A hint. Spread out your tables so the joins show properly and then Print to an A3 printer. It might help you get a better picture that way.

Catch you later and well done so far.

BTW I saw that you used Middle Initial. You could if you wanted, not a must, use MiddleName. Then you can use the function Left to pull the Initial or use the full name on other occasions. This is one of those FYI things.

Thank you for giving this a look. The suggestions you made I will probably do in the next couple days, they seem to make sense. (With the exception of the middle-initial, I'm really just trying to mimic our current system so I can create an ODBC in the future possibly).

Now to the relationships, I think I know where I created the problem with the items (equipement, etc) but... When I'm in "TblItemPrice" I dont think I need the relationship back to the jobID or ItemID because in that table I dont need to know anything other than what job and what item the price is pertaining to. My problem is how do I only allow a current jobid/itemid?

 

[RainLover]

Did you change ItemT to tblItemPrice.

Other than that and with Table names like Sheet and Item I am unable to understand what is what.

I think this is a case of Focus needs to make a decision.

Anyway, it is early days. If you make the wrong decision we can go back and fix it.

IMPORTANT. You need to do a back up of this or any Database that you are working on at regular intervals. Keep the old copies as you won't know how far you need to go back.

The attached should make backing up easier.

I have not tested itso if you don't have any luck let me know.

I will wait for you to decide what you are doing with your relationships.

 

[focus12321]

Did you change ItemT to tblItemPrice.

Other than that and with Table names like Sheet and Item I am unable to understand what is what.

I think this is a case of Focus needs to make a decision.

Anyway, it is early days. If you make the wrong decision we can go back and fix it.

IMPORTANT. You need to do a back up of this or any Database that you are working on at regular intervals. Keep the old copies as you won't know how far you need to go back.

The attached should make backing up easier.

I have not tested itso if you don't have any luck let me know.

I will wait for you to decide what you are doing with your relationships.

I'm not trying to be indecisive. Im not understanding what to do to fix the circular problem you are referring to.

Item, is my materials.

Sheet = daily field sheets which become billable. So if a tech went to a job and was there for 3hrs I can bill for it. But the client needs to know what they did, when they did it, what they used, etc etc. EVERY one of these fieldshiets becomes its own invoice.

Item and Itemprice are (supposively) supposed to be two separate tables. I have a table for all my items with a "List price". The regular everyday price. Then a table for "itemprice" maybe I should call it "itempricebyjob" or something. Every job, just about, sets its own prices. So I need to bring in all the jobs and all the items and give all the items a BY JOB price.

 

[RainLover]

focus

You said in the first post of this thread that you are in the planning mode only. You are not acrually writing at this stage.

I feel that in order for you to complete your pland you should at least be doing some entries in Access. I would suggest that you design as much as you can. By the time you get the Tables completed and the Relationships in place you will be in a much more informed position.

Sometimes when I start a new project and I have the relationships in place I print this our in A# if I can and pin it on the wall.

There is nothing better to help you explain things to the boss and/or your fellow workers.

What you are doing now could all be wrong. So by starting the design you can test that you are on the correct course of action.

Are you happy to do this.

 

[focus12321]

focus

You said in the first post of this thread that you are in the planning mode only. You are not acrually writing at this stage.

I feel that in order for you to complete your pland you should at least be doing some entries in Access. I would suggest that you design as much as you can. By the time you get the Tables completed and the Relationships in place you will be in a much more informed position.

Sometimes when I start a new project and I have the relationships in place I print this our in A# if I can and pin it on the wall.

There is nothing better to help you explain things to the boss and/or your fellow workers.

What you are doing now could all be wrong. So by starting the design you can test that you are on the correct course of action.

Are you happy to do this.

Ive been inputting a bunch of trial data, its helping understand the data, but I still think I might be having issues setting up the relationship to items and there prices and trying to relate the Invoice or fieldsheets. On each invoice is a jobnumber or "JobID" I need some relationship to say.. Ok its job "12345" on this invoice so I need to find the price for "ITEM 1" for job 12345. Im having a terrible mental block on this aspect.

 

[spikepl]

I had a quick look at the last db you posted and i'm sorry to say that it is far from what we discussed. Do not discern between items, labor etc...


Invoice
-------------

Item      ItemType        BasePrice  CustomerPrice       Units      LineTotal
l1212     labor             10              12              10       120
99111     widegtA           5               50               5       250

etc

You also need to think very hard about the logic and how your db reflects reality. E.g. a job is a spec of labor/parts but the charged price is not a property of job and jobitems but of that item on the invoice only. However, how the price for a given item is calculayed is initially a property of item and customer, from out earlier discussions. And so on

You also need to create the common structure for invoices:

tblInvoice -this table contains all common data about this invoice
tblInvoiceDetails - this table holds the lineitems of each invoice

or show how your current tables fit into that (as they are now, they do not).

 

[focus12321]

I had a quick look at the last db you posted and i'm sorry to say that it is far from what we discussed. Do not discern between items, labor etc...


Invoice
-------------

Item      ItemType        BasePrice  CustomerPrice       Units      LineTotal
l1212     labor             10              12              10       120
99111     widegtA           5               50               5       250

etc

You also need to think very hard about the logic and how your db reflects reality. E.g. a job is a spec of labor/parts but the charged price is not a property of job and jobitems but of that item on the invoice only. However, how the price for a given item is calculayed is initially a property of item and customer, from out earlier discussions. And so on

You also need to create the common structure for invoices:

tblInvoice -this table contains all common data about this invoice
tblInvoiceDetails - this table holds the lineitems of each invoice

or show how your current tables fit into that (as they are now, they do not).

I may have used terms loosely and made this all a little confusing. I'm not sure if it makes senses in a DB to keep items and labor completely separate but they need to be reflected on the invoices in completely different spots. Hence what I made separate tables.
... now for structure I think we might have a little confusion here.

We have clients, such as the State or a town. They have Contracts with us (IE. Our Job#). So we might have a maintenance job for all the traffic systems in Boston. The Client is "The City of Boston" and the job is "Maintenace of traffic systems" ... every time we go out and fix something its an invoice basically, (what I call a field sheet). The Job "maintenance of" is what contains all the pricing details, not the fieldsheet (invoice, etc). Maybe it would help to post a file of what we currently use for invoices (excel??).