How should I arrange this Database?

[jbeling]

I am making a database for my business. It is a Purchase Order Database. Please help me with the table set up. A Purchase Order has a unique #. My numbers start with R00109 and go up to R99909. Only the three digits between the R and the 09 will change. So i can have 999 purchase orders, which is pleanty.

Each Purchase Order, or PO, must have
1. The PO number (obviously)
2. The date it was issued
3. Who issued it
4. The company that is being given the PO
5. A description of what is being ordered
6. The amount or price of the PO
7. The invoice number

Also, I have a list of all possible issuers (#3 above), and all possible companies (#4 above).

What I am looking to do is make a simple form where i can issue a new PO and the PO# auto populate when I decide to issue a new one. In that same form I want tobe able to enter in all that info, & search for a PO#.

Any suggestions on how I should set up the tables and what has to be a primary key or whatever?

Thank you!

 

[sendtobrad]

You can download a ready to go one from the new objects menu. But if you want it should be relatively straight forward. tblcompanies (list all possible companies) with 2 fields in design view, 1st field [CompanyID] Autonumber 2nd field [Company] text
tblissuers (list all issuers), 1st field [IssuerID] Autonumber 2nd field [Issuer] and tblPurchaseOrders with 1st field [Purchase_orderID] Autonumber 2nd field [Purchase_order] text, 3rd field [Purchase_date] Date/time (default value = Date()), 4th field [Issued_by] text, 5th field [Company] text, 6th field [Description] text (change default value for size from 50 to 250 - If you need more characters than this (unlikely) you will need to change it from "text" to "memo". 7th field [Price] number (currency) Invoice Number seems uncessary as you can track it by PO number.

Then run form wizard on tblPurchaseOrder, change Company and Issued_by to Comboboxs with tblCompany and tblIssuers as record sources.

Hope this helps. There is a way to format your PO # to what you have but it woud be unnessary if all you want to do is be able to retrieve records accurately.

 

[jbeling]

You can download a ready to go one from the new objects menu. But if you want it should be relatively straight forward. tblcompanies (list all possible companies) with 2 fields in design view, 1st field [CompanyID] Autonumber 2nd field [Company] text
tblissuers (list all issuers), 1st field [IssuerID] Autonumber 2nd field [Issuer] and tblPurchaseOrders with 1st field [Purchase_orderID] Autonumber 2nd field [Purchase_order] text, 3rd field [Purchase_date] Date/time (default value = Date()), 4th field [Issued_by] text, 5th field [Company] text, 6th field [Description] text (change default value for size from 50 to 250 - If you need more characters than this (unlikely) you will need to change it from "text" to "memo". 7th field [Price] number (currency) Invoice Number seems uncessary as you can track it by PO number.

Then run form wizard on tblPurchaseOrder, change Company and Issued_by to Comboboxs with tblCompany and tblIssuers as record sources.

Hope this helps. There is a way to format your PO # to what you have but it woud be unnessary if all you want to do is be able to retrieve records accurately.

So far so good.

Here are a few things that are a must for this database.
1. I need that PO number to match the format above and it must autopopulate. I see how this is tricky especially when you try to make a search or a combo box to find a record.
2. I need to have the invoice field becasue I will be using this to not only accuratley record the Purchase Orders, but if i need to pull an invoice that relates to this I know exactly which one it is and since I have the company name i also know by which company it comes from.


So, In the table you told me to make, Instead of having the 1st field[Purchase_orderID] Autonumber & 2nd field [Purchase_order] text, can it just be 1st Field [Purchase_orderID] Auto number, which is in the format above and will always follow that format when searching or using a combo box?

 

[jbeling]

Also, when i am entering in data on the form that i made using the form wizard, it used the ID number assigned to the Company and Issuer. How can i make it so it shows the actual issuer and company?

 

[sendtobrad]

R00109 as for how to increment from this number with only the 001 increasing I am sure it can be done but I would have to work way to hard to figure that out and I am still new too. I would suggest another another way to do it. Just create a new field called R_no (R number) and Just type R_no:"R"+[PurchaseOrder_ID]+"09" I am not at work where I have the program but I believe this will create your new value as a combination of R, value of PurchaseOrder_ID (for example 1 for first record) and 09 for the following result in the query R00109. To get three digits you would have to format the PurchaseOrderId field to show three digits.

I still think you are making it way more complicated than it needs to be. Just make record # 1 = R00109 #2 = R00209 etc....

Second Part: The reason it shows the number instead of the value is you make it show the actual issuer and Company is with a query. The tables need to have a relationship. You should just call me tomorrow. 3374998512

I dont understand why you need so many ways to track the orders. If you have a primary key with an autonumber each order will be unique and you can pull it up by this unique number, you can search all orders by date, company, issuer or order number with ease. If you send me the file you have so far as a zip or make a copy of the database....database.mdb and change the extension to .doc you can attach it and send it as an email to sendtobrad@yahoo.com

 

[sendtobrad]

I have it created and doing what you want.

 

[jbeling]

R00109 as for how to increment from this number with only the 001 increasing I am sure it can be done but I would have to work way to hard to figure that out and I am still new too. I would suggest another another way to do it. Just create a new field called R_no (R number) and Just type R_no:"R"+[PurchaseOrder_ID]+"09" I am not at work where I have the program but I believe this will create your new value as a combination of R, value of PurchaseOrder_ID (for example 1 for first record) and 09 for the following result in the query R00109. To get three digits you would have to format the PurchaseOrderId field to show three digits.

I still think you are making it way more complicated than it needs to be. Just make record # 1 = R00109 #2 = R00209 etc....

Second Part: The reason it shows the number instead of the value is you make it show the actual issuer and Company is with a query. The tables need to have a relationship. You should just call me tomorrow. 3374998512

I dont understand why you need so many ways to track the orders. If you have a primary key with an autonumber each order will be unique and you can pull it up by this unique number, you can search all orders by date, company, issuer or order number with ease. If you send me the file you have so far as a zip or make a copy of the database....database.mdb and change the extension to .doc you can attach it and send it as an email to sendtobrad@yahoo.com

I just emailed you what I have so far.