Queries, Look-up, and Expressions

[Atlascycle]

Background Info: I manage the Uniform account for 13 other employees and myself, I am trying to create a database to track the weekly charges for each employee and the payments that they are making.

I have the following tables; Employees, Items, Employee Transactions.

The Items table contains the following Fields:Item, Item Type, Price Per Week.

The Employee's table contains the following Fields: Employee Name, Shirts, Pants, Other, And Set up Fees.

The I then created 3 queries from the items table, Shirts, Pants, and Other items.The Queries are to set up look-ups for the Shirts, Pants, and Other Items Fields.

I would like to be able to have the Employees table set up to select the items that are rented and be able to total all of the costs per week of each employee including tax.

My question is how do i list the item in the Employee table but use the Currency value in a query to do the math?

If employee #1 Rents Shirt Style A, Pant Style J, and Jackets,
I would like to have the Query look at the items table and Pull the Rental Value for Shirt A, Pant J, and Jackets.



Is this a special kind of look up? If so what is called? I would like to be able to do the research to find out how to do this but I am not sure what search for to find the examples.

Thank you in advance for any help you will be able to offer.

Jason

[June7]

It appears that with this structure, would have to join Items table to each of the clothing fields in Employees table. Your data does not seem to be properly structured.

What is the table EmployeeTransactions for?

Could you provide the database for analysis?

[Atlascycle]

I have attached the Database as Requested. there are several queries in the database for things that i have attempted to do.

If i am total in left field in the way that i have set this up feel free to call me crazy.

Thanks again for the Assistance.


Jason

[June7]

You are duplicating data. For instance, in EmployeeTransactions there is a 7/6/2011 record for Kelly receipt 579218 $24.91. In MoneyInOut is a 7/6/2011 record with the $24.91. This amount data should be entered in only one table. All transactions, regardless of type or source should be in one table. Details would be in related tables. Records would be related by primary/foreign keys.

You are saving the item description to Employees table instead of the item primary key. This is okay as long as the description is always unique, but it saves a longer value into Employees table. Requires more storage, okay this is not 1960 and memory is cheap but it also might make things run slower. Also, if you want to save the description, it should be set as primary key, not the autonumber field which is not serving any purpose this way.

Having a field for each clothing item requires each field to be joined to the Items table so as to retrieve the related cost value. This is not normalized structure. The MoneyInOut table also is not normalized structure (lots of empty fields). Not normalizing is not always bad but must be considered. Deciding how far to take normalizing is a balancing act between ease of data entry and ease of data output.

Advise not to use spaces, special characters (@ # $ % & - + = * ^ /), punctuation (underscore is exception) in names. If you do, must remember to enclose in []. Access will try to do for you but VBA code never will (don't know about macros, I avoid them). Better would be ReceiptNum or Receipt_Num and SetupFee or Setup_Fee

[Atlascycle]

The money in out table will be eliminated.

The Lookup for the Items is looking to the queries and not the Item table, I did this to narrow the choices to only the specific item type, is there a better way to do this and store the Primary key to the table?

[Atlascycle]

Iin the Employee Transaction Table to reduce the number of empty fields would you recommend consolidating the transaction and Payment method fields? IE Cash Payment, Check Payment, Weekly charges, Prep Fees and then elemenate the Payment method field altogether?

[dustmopheadmop]

Hi,
I think that you have to redesign your db according to best practice norms. That’s, you have combined the employee with their wearing in one table. The best design as I think is to separate them in 2 tables. One for staff information such as Id number, employee name, and so on and other table to their wearing (staff Id, item dressed and charge and so on.

Following best practice norms of designing db make your query a matter of copy and paste. You can refer to sample db that comes with ms access as free northwind that can show you the best db design.
I hope it help.

[June7]

Here is what can happen when trying to record same data in two places. I count 64 payment records in EmployeeTransactions but can identify only 56 records in MoneyInOut referring to employee payments.

What do the numbers beginning with 637 mean?

Does anyone pay by credit card?

I can see justification for a table for employee charges and a table for payments. However, you should not have a transaction record for Deposit. This is an aggregate of payments received and should be calculated by report. You can enter a Deposit ID number in a separate field of Payments. Then in a query can sum the payments by Deposit ID.

The point is data should not duplicated and aggregate data should not be saved but calculated.

[Atlascycle]

I originally was using a Spread sheet to keep track of this information. I have attached the spreadsheet to this post. I was able to make the Excel Workbook do what i wanted but was forced to physically input the payment received from the employee on two different sheets.

The Money in out Table was from the Money in out sheet in the work book, this sheet was used to keep track of the employees paying, Aramark invoices(that is the 637 numbers), Bank deposits, and Amount out(this column is when i withdraw from the bank account and Pay Aramark). The bank Balance column is the total from the previous line plus the amount deposited minus the Amount out. The Aramark Balance is the total from the previous line plus the amount billed, Minus the amount out.

the Deposit column was from the spread sheet, i would use that to get a total dollar amount that i received from all of the employees when i would go to the bank to make a deposit.

I will delete the MoneyInOut table.

I will attempt to split the transaction table into charges and payments.

Thank you again for your time helping me get my mind around how this should all work.

[June7]

That spreadsheet does look like a nightmare.

This database is appearing more and more complicated - employee charges, vendor invoice payments, employee payments, bank deposits. You might look at an OTS accounting application, like Quickbooks or MS Money.

If you want to continue developing your own, carefully consider the table structure to avoid duplicate entry of data.

[Atlascycle]

That spreadsheet does look like a nightmare.

This database is appearing more and more complicated - employee charges, vendor invoice payments, employee payments, bank deposits. You might look at an OTS accounting application, like Quickbooks or MS Money.

If you want to continue developing your own, carefully consider the table structure to avoid duplicate entry of data.

I make the spreadsheet work, the only thing i am not able to do is cross check the weekly charges to the invoice.

I have started to restructure the tables, EmployeeCharges, EmployeePayments, Employees, and Items.

EmployeeCharges contain the following fields; ID, Date, Wearer, and Charges

EmployeePayments contains the following fields, ID, Date, Wearer, Transaction, PaymentMethod, RecieptNum, and PaymentAmount.

Employees Contains; ID Employee, Shirts(lookup to Shirtsqry), Pants(lookup to Pants_qry), Other(lookup to Other_items_qry), SetupFee, and Shirts Test(forgot to delete this field before I attached the DB).

I will also add a weekly Invoice Table to track the invoices and there amounts.

My end goal for the DB is to be able to compare what the individuals charges are, what they have paid, and total the weekly charges and compare to the adjusted statement from the driver.

June7, do you feel i should move this to the database design forum?

Thank you for all of your help and advice thus far.

Jason.

[June7]

It does seem that design is now the focus of this thread. You could ask moderator to move it. Click the triangle in upper right of post to notify moderator.

What you are building is basically an accounting app. Accounting and similar apps such as inventory are probably the most intricate to tackle because of the fluidic nature of the data (cash flow, running balances). There are OTS apps that could probably do this with less investment than your cost in time and frustration to build from scratch. I do wonder why this is not handled within the company's full accounting package. What is in use for that? The employee charges and payments could be handled through payroll advance and withholding. I have used Quickbooks and it was a very easy to learn and flexible program. Around $200.

The bank account is used only for uniform related transactions? The clothing orders and invoice payments are handled separately from other company purchasing transactions?

That revision takes care of the employees. You no longer plan to track the invoice payments and bank deposits?

[Atlascycle]

June7,

I did not see that you had Edited your post.

The uniform account is in the company name but is not handled by the company, it is done personally by myself. Not the ideal situation but I was getting tired of ruining my clothes and trashing my washing machine with greasy laundry, we tried to get the uniform company to bill the employees individually but they did not want to do that, our employer did not want the hassle of doing it either(i can now see why) so here i am personally excepting payments from the employees, and Paying the Bill every month, and of course making sure that everything is adding up.

I have added another table for the Invoices.
I would like to have the Bank Deposits calculated from the employee payments for a specified time period. For example, I have it set up with the employees to pay every 4 weeks, our pay periods are every two weeks checks issued on Wednesday. That gives everybody a couple of days to make there payments to me so I can make one trip to deposit it in the bank. My thought is to run a query for the date range for the days after the last payment pay period and total the employee payments and that would be the deposit. how is the best way to store that in a table. what type of query would i use?

With the way that the employee table is formatted, what type of query will allow me to do a parameter query for the employee but instead of seeing the Item name that they are renting instead the price for the items so I can calculate the weekly charges including the individuals share of the weekly service fee and tax. I have figured out how to do the expression to total it all and then figure the tax, but not sure how to get the price for the items with the way i have the table set up.

Thank you again for the help.

Jason

[June7]

For bank deposits I would have table Deposits. In the EmployeePayments table I would have a field for DepositID (foreign key).

Then I would have a form/subform. Main form bound to Deposits, subform bound to EmployeePayments. I would enter employee payments into the subform. This will automatically populate the DepositID foreign key. I would continue adding payments to this Deposit until it is ready to go to bank. Then I would enter date of the deposit and that would close that deposit.

Now about the Employees table structure. This is exactly what dustmophead was pointing out in earlier post. The table is not normalized and does complicate querying. You can either do the restructure dust suggested or use a workaround which will probably involve a UNION query. A UNION query essentially rearranges the data into normalized form. There is no wizard or designer for UNION, must type in the SQL View window of designer. Like:
SELECT EmployeeID, Employee, "Shirts" As Type, Shirts As Desc FROM Employees
UNION SELECT EmployeeID, Employee, "Pants", Pants FROM Employees
UNION SELECT EmployeeID, Employee, "Other", Other FROM Employees;

Now this query can be joined to the Items table to match cost with item.

[Atlascycle]

Here is the latest version, I have gotten busy with work lately and not had the time to post an update.

I have made an invoice form with a employee_charges sub form. this is working quite well to cross check the invoices for errors.

I have also created a Deposit Form with an EmployeePayments subform as suggested. This is also working quite will. Thanks again for the help.

I think that i have restructured the tables as suggested.

I would like to get your opinions on the current state of this database before I move forward any more. If you feel that it is not a complete abomination in its current state i will ask to have this topic closed and create a new topic with more questions.

Thank you again for your time and help with this.

Jason