Help on how to track monthly ongoing data

[Jmaddog]

I am developing a database that tracks monthly expenses. I need to be able to compile each month into one table and am not able to figure out the best way to do this. Each person has a unique code, but could be listed multiple times for the year. Please let me know best way to do this.
I have a master table created of all team members with name; code, etc.

 

[neileg]

If you mean one table for each month, that's very unlikely to be the best design. You should store all the expenses in one table and then use a select query to retrieve individual months. Your expense table will be joined to the person table by holding the person ID against each record in the expense table.

 

[Jmaddog]

Thanks for your reply. I am currently compiling all months into 1 table. I have added a field for month, but do not have a PK in this table because the same name and code can have expenses processed each month. Any suggestions on how to accomplish this. I have added a month field to my table to differentiate between the months. When I link this table to my master table I am having trouble getting the results I want from the query. It is not recognizing the relationship between the two tables.

 

[neileg]

I always use an autonumber PK, natural keys and compound keys just make life too hard.

I assume you have the relevant PK value from your main table against the expense transactions. If you set up the join in the relationship manager, any queres will inheirit the join when you use the related tables. Otherwise, you can define the join in the query. What exactly is going wrong?

 

[Jmaddog]

Thank you for your help. I was able to get my query to work by making a couple of adjustments. I have another question pertaining to the same database but another topic.

I need to update a field in my expense table that has a PK code for each employee that is listed as ###-###. I would like to remove the "-" and have it as 6 character (text) field. I am importing the expense table from excel that is an actual data export from sprinter database that we use. The dash is automatically inserted in the number code. Any idea how to remove the dash from that field.

I was thinking that there might be an expression that could be written to remove the dash.

 

[neileg]

Left([MyField],3) & Right([MyField],3)

 

[Jmaddog]

Thanks for all your help - you have been a huge help.

 

[neileg]

Just thought. If you need this as a number and not text, you should use:
CInt(Left([MyField],3) & Right([MyField],3))

 

[tzh]

This is what a great thread ~

 

[Jmaddog]

Help - I am running into more problems.
So far I have compiled all my expenses for months of January thru August into one expense table by employee code. I am not sure how to calculate total expenses for each employee for those months. Some employees have a record for each month with expenses tied to them. How do I get a grand total for each employee.

Can this be done through a query or do I need to create a report? I was hoping to try via query for testing purposes.

 

[neileg]

Create a totals query (also known as an aggregate query) and group by employee for a grand total, employee and month for monthly totals.