Need to Create a New Report based off Existing Data

[DBWalsh12]

I have never used Access. I am the entire IT department at my job and I am trying to learn how to use Access in order to be able to keep our Jobs system updated. I have Access 2013.

How do I create a new Table that locates all of these functions and then how do I have my Front End access it and Display it nicely?

This is exactly what I need to do:

I have a Front End and a Back End - All of the numbers and calculations I need to access are all within the Access file already in different areas. I need to create a new report, it will be called something like Job Cost Breakdown.

The report will show:
[Total Sales] [Total Costs] [Cost of Goods Sold] [Gross Profit] [Commissions Earned] [Net Profit]

These are all basically functions of each other
*Total Sales and Total Costs are accessible somewhere in the Database and are already reporting on Job Reports.
*Cost of Goods Sold is simply Total Costs / Total Sales (displayed as a %)
*Gross Profit is Total Sales - Total Costs
*Commissions Earned is a calculation based off commission rates found unique to each sales rep (already in the database on the commissions report)
*Net Profit is the Gross Profit - Commissions Earned.

Is this not enough info? As I said I have never used Access so I am not sure exactly what information needs to be provided.

Thank you!

 

[jzwp22]

The calculations you mention can be done in the report design itself. What you have to worry about is extracting the data necessary to do those calculations. The data resides in tables in the back end file. You would use a query to extract that data. You would base the report on that query.

In order to determine how the query has to extract the data, we have to understand how the data is organized. The back end file typically holds the data in tables and those tables are related to one another. You might have a table that holds information about the salespeople; you might have another table that holds information about sales that are tied to each salesperson. The sales data might be very granular in which case we will have to do some totalling and likewise for the cost information. Can you provide a list of the tables along with the field names in each of those tables?

 

[DBWalsh12]

There are quite a few tables and fields within those tables.
I am trying to figure out the best way to show you this. any helpful ideas?

 

[jzwp22]

Many posters just post a screenshot of the relationship window (database tools-->relationships).

 

[DBWalsh12]

attached the screenshot

 

[jzwp22]

Since I cannot see all of the fields, I'll just have to give you some general guidance. The sales information will need to be obtained through the series of invoice tables. The costs will have to come through the purchase order related tables. The join will have to be made via the part number from the best that I can tell. As to the commission, I cannot see what table that comes from but it appears that the sales person is related to the customer is tied to a job and it is the job that gets invoiced.