Question (1 Viewer)
- Thread starter Nicolette
- Start date
Comments on Tables.
tblCustomers: Not good idea to repeat Captions. Country listed twice. No big deal but it can be confusing later if you are not sure which "Country" you are looking at. Postal Country is different to Delivery Country. Caption doesn't need to be one word only.
tblInventory: Good idea to use double names if possible to avoid using a reserved name. Try ProductCost and ProductTitle.
The reserved name list is very long and there are different versions
Watch out for the Validation (cost) in the table. Maybe just use this in the form.
SuppID combo box should be in the Form, not the table.
tblOrderHeader: Maybe change Status to OrderStatus - Status can be vague.
Move Combo Box to Form.
tblOrderDetail: Remove InvoiceDate - this is in Order Header. Not needed in Order Detail and can cause problems later.
tblPayments: Set Format for Date - maybe short Date?
tblPaymentsMethods: OK but you could just have the options in the Form.
Handy to have a table to make additions but for two options and later, as the database grows, you will be glad it is not cluttered with little tables.
tblStates: suggest change State to StateName. Caption can be State.
Do you want to allow duplicates for ShortState & StateName ?
Table Relationships look good.
Tidy database.
I will study the Queries, Forms and Reports early in the morning. 8pm here now.
tblCustomers: Not good idea to repeat Captions. Country listed twice. No big deal but it can be confusing later if you are not sure which "Country" you are looking at. Postal Country is different to Delivery Country. Caption doesn't need to be one word only.
tblInventory: Good idea to use double names if possible to avoid using a reserved name. Try ProductCost and ProductTitle.
The reserved name list is very long and there are different versions
Watch out for the Validation (cost) in the table. Maybe just use this in the form.
SuppID combo box should be in the Form, not the table.
tblOrderHeader: Maybe change Status to OrderStatus - Status can be vague.
Move Combo Box to Form.
tblOrderDetail: Remove InvoiceDate - this is in Order Header. Not needed in Order Detail and can cause problems later.
tblPayments: Set Format for Date - maybe short Date?
tblPaymentsMethods: OK but you could just have the options in the Form.
Handy to have a table to make additions but for two options and later, as the database grows, you will be glad it is not cluttered with little tables.
tblStates: suggest change State to StateName. Caption can be State.
Do you want to allow duplicates for ShortState & StateName ?
Table Relationships look good.
Tidy database.
I will study the Queries, Forms and Reports early in the morning. 8pm here now.
See revised qryInvoiced.
and qryOrder.
Made changes to the query joins. I assume you are looking for Orders and related info to same.
Amend frmOrder and frmInvoiced to reflect records from the above queries and the two forms should populate.
Added Shipped as an option as well as Invoiced for qryInvoiced as a shipped item will also be Invoiced. Easy to edit out but also not Invoiced data in database. Just Order and Shipped.
Still working. More to follow.
PHP:
SELECT tblOrderHeader.OrderID, qryCustomers.FirstName, qryCustomers.LastName, tblOrderHeader.CustomerID, tblOrderHeader.Status
FROM (qryCustomers RIGHT JOIN tblOrderHeader ON qryCustomers.CustomerID = tblOrderHeader.CustomerID) LEFT JOIN tblOrderDetails ON tblOrderHeader.OrderID = tblOrderDetails.OrderID
WHERE (((tblOrderHeader.Status) Like "Invoiced")) OR (((tblOrderHeader.Status)="Shipped"));and qryOrder.
PHP:
SELECT tblOrderHeader.OrderID, tblOrderHeader.CustomerID, tblOrderHeader.Status, tblCustomers.FirstName, tblCustomers.LastName
FROM (tblCustomers RIGHT JOIN tblOrderHeader ON tblCustomers.CustomerID = tblOrderHeader.CustomerID) LEFT JOIN tblOrderDetails ON tblOrderHeader.OrderID = tblOrderDetails.OrderID
WHERE (((tblOrderHeader.Status) Like "Order"));Made changes to the query joins. I assume you are looking for Orders and related info to same.
Amend frmOrder and frmInvoiced to reflect records from the above queries and the two forms should populate.
Added Shipped as an option as well as Invoiced for qryInvoiced as a shipped item will also be Invoiced. Easy to edit out but also not Invoiced data in database. Just Order and Shipped.
Still working. More to follow.
You made changes to the two queries that run the Orders subforms in your At A Glance main form. This may require some editing to get the forms to work.
The other changes you would have made are in the tables where some names were changed and one field deleted.
Just look at the forms that don't work and see if any changed names need some editing in the forms and queries.
I am working on the forms here now.
I made the changes to my copy of the data base and amended the queries and forms and everything seems to work how you set it up.
Have run out of time tonight but will get back to it in the morning.
I am looking at the Order Entry process to try and get a simple option for you to see.
Have run out of time tonight but will get back to it in the morning.
I am looking at the Order Entry process to try and get a simple option for you to see.
Is Product Cost your Cost or your selling price?
You should have both ProductCost and ProductSell.
You could have the sell price calculated in the form but then you would need either a Fixed Markup or a ProductMarkup in tblInventory for each product.
Please advise.
You should have both ProductCost and ProductSell.
You could have the sell price calculated in the form but then you would need either a Fixed Markup or a ProductMarkup in tblInventory for each product.
Please advise.
most items sell for 0.99 or 1.00 but they do occassionally go for more I wasn't sure how to handle the fluxuating selling prices so I just left it out of the inventory table
the PoductCost is my cost for each item.
the PoductCost is my cost for each item.
did you have to make any changes to the forms? I didn't see where the changes in the queries would affect the forms since those queries are just for the summary page.
If you changed the name of Status then yes, some forms will need to be updated as they look for Status and not OrderStatus.
Some forms also have an sql with them and this just needs an edit and then edit the Forms' text box control for Status.
What form are you on?
Did you change the table fields from lookup to text? I note the froms already had the combo box in them which means no need to hav eth etables as a lookup and advice is to not do this.
Tables should just be text boxes.
Check your tblOrderDetails and remove the lookup you may have on OrderID. Make Display Control "Text Box"
i'm on frmCreateInvoice yes I removed i believe all lookups in the tables although i have to admit I don't remember putting them there in the first place. there isn't a lookup on OrderID in tblOrderDetails or tblOrderHeader which is where the orderid is being pull from
ok now i'm working on the frmRecordPymt... i need the dropdown for the orderID i need to autopop the customerID and the orderdetails
also on frmAtAGlance in the invoiced section it is showing the order more than once... I only want it to list orders thatbhave an OrderStatus of Invoiced once
also on frmAtAGlance in the invoiced section it is showing the order more than once... I only want it to list orders thatbhave an OrderStatus of Invoiced once
This sql for qryInvoiced should get what you want for 2nd question.
Code:
SELECT tblOrderHeader.OrderID, qryCustomers.FirstName, qryCustomers.LastName, tblOrderHeader.CustomerID, tblOrderHeader.OrderStatus
FROM (qryCustomers RIGHT JOIN tblOrderHeader ON qryCustomers.CustomerID = tblOrderHeader.CustomerID) LEFT JOIN tblOrderDetails ON tblOrderHeader.OrderID = tblOrderDetails.OrderID
GROUP BY tblOrderHeader.OrderID, qryCustomers.FirstName, qryCustomers.LastName, tblOrderHeader.CustomerID, tblOrderHeader.OrderStatus
HAVING (((tblOrderHeader.OrderStatus) Like "Invoiced"));ok I don't know what happened but the customer id in frmCreateInvoice isn't autopopulating... I was already having problems with this sometimes it seemed to work but not always.... which I don't understand how it can sometimes work correctly
