separate a form by months

[jzwp11]

Since an item can have many prices, the prices should be in a related table. Also since an item is not unique to a customer (any customer can buy any item) having the customerID in the tblItems is inappropriate. The item table should look like this:

tblItems
-pkItemID primary key, autonumber
-LongItemNo
-txtItemDescription

tblItemPrices
-pkItemPriceID primary key, autonumber
-fkPriceTypeID foreign key to tblPriceTypes
-currPrice

tblPriceTypes (a table to hold the 3 types of prices: regular, sale, cash & carry as records)
-pkPriceTypeID primary key, autonumber
-txtPriceTypeName

Now, I assume that the price type is pertinent to the invoice for a customer, so I think you need to record the invoice

tblInvoice
-pkInvoiceID primary key, autonumber
-fkCustomerID foreign key to tblCustomers


-fkPriceTypeID foreign key to tblPriceTypes (this will determine the price type to be used for this invoice

You do not need to bring in the account number since it is already part of the customer table.

...each driver has a LOAD, with differentes customersaccounts

Since a load can have many accounts/invoices, it describes a one-to-many relationship.

tblLoads
-pkLoadID primary key, autonumber
-LoadNumber
-dteLoad (date of the load)
-fkEmployeeID foreign key to tblEmployees (this would be the driver)

tblLoadInvoices (the invoices that make up a load)
-pkTransID primary key, autonumber
-fkLoadID foreign key, tblLoads
-fkInvoiceID foreign key to tblInvoice

Can an invoice contain many beer items?

Based on tbltransaction I would like to have a Nice Input fom to make it easy to put data every day

In order to have easy data entry, you have to have the correct table structure. It is the most important part of setting up a relational database.

[graciemora]

Yes, an invoice has customer number, load number, date of delivery and has item description, qty and total. and date.

[jzwp11]

Will you be generating the actual invoice from Access or is that handled through another software application?

I will assume that the invoice is generated from another application and that you just capture the invoice # and load # in Access.

Continuing my table structure example...

A load can have many invoices associated with it

tblLoads
-pkLoadID primary key, autonumber
-dteLoad (date of the load)
-fkEmployeeID (the driver)


A load can have many invoices

tblLoadInvoices
-pkLoadInvID primary key, autonumber
-fkLoadID foreign key to tblLoads
-fkInvoiceID foreign key to tblInvoices (from my earlier post)
-fkEmployeeID (employee reviewing the load details)

tblLoadInvoiceDetail (equivalent to your transaction table)
-pkLoadInvDetailID primary key, autonumber
-fkLoadInvID foreign key to tblLoadInvoices
-fkItemID foreign key to tblItems
-currPrice (price pulled depending on the price type associated with the invoice)
-longQtyOrdered
-longQtyDelivered

If the same person reviews all the details of a load then you can reference the reviewer in tblLoadInvoices as I have shown. If you can have different reviewers for each line item in the detail table then the fkEmployeeID goes in the tblLoadInvoiceDetail table.

[graciemora]

yes an invoice can have many differents beer or waters, another thing cash and carry are special prices for special account only(wholesaleaccounts) so on prices we have only regular price and sale price. yes the invoice number is generated by another software. i will just get the invoice number from the invoices we received from the drivers(paperinvoice)

[jzwp11]

I assume that an invoice can have many items, and the structure I presented should handle that. Please review it and let me know if you have any questions.

[graciemora]

Do I need to relate tblItems-tblItemPrices-tbllPriceTypes?
I belive I understand better the relationships. Do I need to input data using only "ID numbers) in my LoadInvoiceDetail, I do not need to record Long Qty Ordered, I just keep record of what they do not bring back.

[jzwp11]

Yes, you will need to join tblItems to tblItemPrices and tblItemPriceTypes to tblItemPrices. I've made the modification in the attached database.

Do I need to input data using only "ID numbers) in my LoadInvoiceDetail

Yes, but you can use combo boxes on your form for data entry. Each combo box would use the respective table to supply the information. You can adjust the combo box so that your user sees the more informative text rather than the ID. The ID will actually be stored in the underlying table.

[graciemora]

Then, I need to created first a form, add the combo boxes and after that the table will be populate with the ID numbers? sorry I' am confused because is kind of hard to be looking for the ID number every time I want to add a new record in the table

[jzwp11]

A combo box can display many things when you first click on the dropdown arrow. Then when you move away from the combo box it can display a specific value. Additionally, a combo box can store a value to the underlying table that is not displayed (such as your ID field) that makes them very useful. The combo box wizard in Access steps you through the process of setting up a combo box. What I usually do is create a form using the form wizard and then replace the textbox controls with combo boxes where appropriate and use the combo box wizard to create them.


But before you start working on forms, you probably have to add additional fields to the various tables in your database. I only provided the main frame work, you will have to add additional details. Once you have completed that, then you can start on the forms. If you want me to take a look at your database at any time, all you have to do is post it here.

[graciemora]

hi! I added additional fields to my tables. I believe I do not need more fields.
what means curry price? is the price depending on price Type?
I added two tables to the DB. (LoadDay) we use diferentes load number per each day of the week,(monday 1) tuesday 2... 101,202.. etc. thanks for your help and have a good day. Can you check my db again.!! please!

[jzwp11]

currPrice is the price field; I use the curr prefix to designate that the field is a currency data type field. The price you put in depends on the type as you thought.

I did have a tblLoads in the last version of the database, so you did not need another table. You can actually use the date and a sequence number field to "calculate" the load number value you mentioned. A calculated value would generally not be stored. With that tblLoads would look like this:

tblLoads
-pkLoadID primary key, autonumber
-longLoadSequenceNo
-dteLoad (load date)
-fkEmployeeID (driver)

You would need to enter the sequence # and date and then use an expression to show the load number

=cstr(weekday(dteLoad,2)) & cstr(longLoadSequenceNo)

There are ways to reinitialize the sequence number for each new date, so you could actually automate the assignment of the load number. If the load numbers are assigned by another department, then automating it might not be of value. You will have to decide on that aspect.

I'll have to look at the database tonight from home since it is in Access 2007.

[jzwp11]

I reviewed your database and saw several issues.

1. In tblItemPrices, you had multiple price fields, you only need 1. You will have 4 prices records for each item (one for each of your four price types) or 4 prices x 497 items or 1988 records. I moved the data around with a series of update/append queries and got rid of the unneeded fields
2. I adjusted your dayload table (and renamed it tblDays) to just have a date since for any given day you can have many loads. As a result, we did not need the date field in tblLoads any longer. I updated your relationship window as well.
3. I removed ItemsTotalAmount from tblLoadInvoicesDetail since this is a calculated value (currprice*longQtycsShort). Calculated values are generally not stored in the table. You only store the base data needed to do the calculation. You would calculate the value when you need it in a query, form or report. I also removed the quantity delivered field based on your earlier post
4. I removed fkItemID in tblInvoice, you already have fkItemID in tblLoadInvoiceDetails since you have many items per invoice in a given load.
5. Added some additional relationships that were missed.

The modified database is attached.

[graciemora]

why I can't see the field information in the forms? I use form wizard, and simple forms, same thing is happening with queries, all I can see is the title of my form. Thanks for your time.

[jzwp11]

What forms and queries are you referring to? In the database version I posted, I did not have any forms. Are you making changes in your database based on the database I posted? Since I altered the tables, any forms/queries created prior to the change may need to be rebuilt in order for them to work properly. That is why it is important to get the table structure completed fully before working on anything else.

[graciemora]

I will start all over again with my forms, but I know this is probably a simple question, but since tblloadinvoicedetail will be the base of my form. ?! I have only fk in the fields, so when I want to create simple forms it shows only the ID numbers,and if I add data with text it said that wrong.
Is better to use a blank forms? and add combo box?
If a use form wizard and I add field like employee name,last name, Account number it shows only the title of my form..
why is not giving me many of the form properties.. like record source, etc.