My head is going to explode. Please help

[guinness]

Hi Guys I've joined this forum in desperate need of help.

I'm building my first ever database without any help and was doing ok up till now.

I have the following scenario. I deliver training to five business areas and also projects to the same five businesses. I have a table for projects, a table for delivery and a table listing the businesses.

I want to report on all of the work that I deliver to each business (delivery and project). Whe I run a query by business and delivery it works fine. When I run a query by business and project it works fine. When I try to report a total of projects and delivery together by business I get one line back. The one line I get back is the only time when I had a single member of staff on a piece of delivery and a project to one business.

I've attached a picture that may help.

Loads and loads of thanks if you manage to save my sanity

Cheers

 

[JamesMcS]

So you're looking to show training by project? This one might need a post of a stripped out copy of your DB - just delete all the personal stuff out and stick arbitrary data in there, and I'll have a butcher's for you.

 

[guinness]

Thanks James

I'm actually looking to report on the total hours (training and project combined) for each business.

I appreciate your offer to take a look and I'll try to make a stripped out copy for either later today or early tomorrow. There's possibly a lot wrong with what I have but it does seem to be working with my test data which is good. Even the holiday/sick part thanks to yourself.

Cheers

Kev

 

[boblarson]

From what I'm seeing (but it is hard because the pic is a bit blurry when enlarged), your table structure may not quite be there. Can you post a copy of your database with just the tables in it?

 

[CraigDolphin]

I second Bob's suggestion. From what I can see from the very blurry image, there's a need to revisit your basic table structure.

Not knowing the nitty-gritty of what exactly you record and what you need to report, it's hard to be sure. But something like...

tblBusinesses
BusinessID (auto, pk)
BusinessName
BusinessPhone
etc

tblDeliveryTypes
DeliveryID (auto, pk)
DeliveryType
(this table contains two rows: one for 'Training' and one for 'Products' but more can be added in the future if you need to)

tblDeliveries
DeliveryID (Auto, pk)
DeliveryDate
DeliveryTypeID (fk)
BusinessID (fk)
Hours

...might be what you need.

 

[HiTechCoach]

I also think that your issues are from the table structures not set up properly to handle the data.

Posting a sample database would be best.

 

[guinness]

Hi Guys

Here's a copy of what I have so far.

I only first started using access last wednesday and it probably shows. I've already scrapped everything and started again three times so my table structure might well be all over the place.

At least you'll be able to see the table structure. The data in there is completely made up so don't look for it to make sense.

I want to be able to combine the 'Project Tracker' and 'Delivery Tracker' queries to show a summary of what I do for each business.

I might as well also ask when I'm on and you can see the database. When I put this out I want people to be able to view those queries and toggle between table, pivot table and pivot chart. The thing is when I disable design mode (which is a must) I also lose the toggle buttons. Because this is a query I can't see how to make new toggle buttons.

All help is very welcome and, as I am completely new, I wont be too offended if you tell me it's the work of a lunatic and that I should go back to paper and crayons.

Cheers

 

[HiTechCoach]

The way I look at it is this:

Business >> have >> Projects >> that have >> Activities (transactions)

Activities would include:
Deliveries, Productive Time, Non Productive Time, Project Workload, etc.

I want to be able to combine the 'Project Tracker' and 'Delivery Tracker' queries to show a summary of what I do for each business.

They indicates that you probably should have combined the two tables into the same table. I would also say you probably would also want to combine Non Productive Time and Project Workload into the same table.. You would add an additional field to define the category. You should not use table names to define the category of activity.

The same would apply to all people. All people should be in a single table (Employees, Team Leader, Business's contacts, etc.). If needed, you use junction tables to define the relationships between the people table and other tables.

 

[HiTechCoach]

I would like to add that you have some very nice looking forms. Well done.

In Access 2010 it look great with the Black Theme. Are you using Access 2007 or 2010?

Here is the main switchboard/menu form in Access 2010 with the Black Theme for Office.

See attached screenshot to see how Access 2010 can look.
Credit: Screenshot created with SnagIT 10

 

[HiTechCoach]

Here is a screenshot with the Ribbon visible

Credit: Screenshot created with SnagIT 10

 

[guinness]

Sorry to take so long to reply guys.

Hi tech coach is of course right about the table design. The pproblem I had though was that people can work on a number of projects at once and I needed them to be able to select a different project title for delivery and design within the same input form.

I solved my problem by creating a union query. I created blank fields in table A named after the fields I wanted from table B and vice versa. I then created two identical queries and then made a third union query which brought both pieces of data together.

Messy but it worked. I hid the blank fields in the original tables so they can't get used by accident. So far, touch wood, the results are exactly what I was after.

Cool

 

[HiTechCoach]

Using a Union query was a good idea.

When you have normalization issue with your tables you can use a Union query to get your data into a more normalized structure.

I would recommend you fix the table design as soon as you can. It will make everything else easier from that point on. As the amount of data grow it will probably also help with performance.