Beginner needs help with Access

D

darkwolf45

New Member
Joined
Oct 2, 2009
Messages
33
Good morning,
I have a database, with several tables that I import from other sources. The two main tables have identical fields, however, the data in them differs in the fact that one is Dollars and the other is hours. Both tables have the following columns:
Cost Ctr
Cost Ctr Description
Account
Account Description
Year-to-date
Current Period

For each year there would be two tables like described above (very large, the table with dollars is almost 104,000 rows)

The next table allows me to map the first table according to my needs:
MC Line
Cost Center
Cost Center Description

Finally, I have another table that allows me to categorize data:
Account
Account Type

Right now, I'm trying to keep my scope of how I use this database rather modest. All I want right now is to be able to put dollars and hours side by side. This is difficult because both tables have items that don't have a matching value in the other table (i.e. hourly accruals that there are no dollars for or salries paid that there are no hours for). My attempts at querying haven't met with any success in returning all records.

The other option I came up with was to simply rename one of the fields in the hours table, add a like named column to the dollars table and simply append the hours table to the dollars table. This seems like a waste, however, as there are many duplicate records that have either nothin in the hours column or nothing in the dollars column.

Could anyone give me some insight into how to better set up this database?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
There may be better ideas but I have generally created a query to pull from the two main tables of interest a set of all the accounts I need. Then I use that query to join back to the tables - this way I can get all accounts no matter whether the account is in either one or both.

An example would be, first the query to get all accounts (let's assume here you need all accounts & Cost centers):

QUERY_1:
Code: 
SELECT [Cost Ctr], [Account] FROM MainTable1
UNION
SELECT [Cost Ctr], [Account] FROM MainTable2

Now, using the above query, which will be a set of all these account/cost center combinations (Union discards duplicates), we join back to the tables. Since it's a left join, any missing values will show up as Null (i.e, missing hours, missing dollars, or even missing accounts).


Code: 
SELECT
    q.[Cost Ctr],
    q.[Account],
    t1.[Dollars],
    t2.[Hours]
FROM
    (([COLOR="royalblue"]QUERY_1[/COLOR] q
    LEFT JOIN MainTable1
        ON q.[Cost Ctr] = t1.[Cost Ctr])
    LEFT JOIN MainTable2
        ON q.[Cost Ctr] = t2.[Cost Ctr])
;

As a general rule of thumb (throwing in 2 cents), some database systems don't allow spaces in field or tables names, so its helpful to avoid them in case you ever hook up your data to a larger system. Saves you typing brackets too.

I'd certainly be interested to hear if there are alternative solutions - I run across this problem a lot (when comparing accounts from two sources).
 
Upvote 0
For an variation on xenou's approach, look at the second option on this page.

Differences:
Union query has an additional field that shows which table the data came from
The union is then pushed to a new table
This table is then laid out using a crosstab, to give a side-by-side comparison of the tables and showing the gaps.

Denis
 
Upvote 0
Thanks Xenou. I don't know sequel, so writing a union query seems to be a challenge. I seem to be hit or miss getting the syntax right right now... I haven't gotten to the second part of this. It looks like your approach is joining the original tables to the union query you suggested? If I'm interpretting it right, that's clever, I wish I had thought of that before.

I'm looking forward to giving Sydney Geek's solution a shot as well, but can't as another project has come up that I need to focus on for the near future.

The irony here is that the source for both the dollars and hours are the same, we just receive separate reports. I talked to the owner of the reports though, and he's combined the reports so I will havew a field for dollars and hours in one table going forward, which will make this a lot easier.

Thanks for your help, I will definitely be coming back to this thread to reference as soon as I can!
Brian
 
Upvote 0
Alright, so SydneyGeek, your solution worked out marvelously, as far as I can tell, with this one hiccup. All I had to add to the solution was a make table query so Excel could do a pivot table into Access.

The number of records comes out just how it should, however, when I add up the 5 original files I'm combining on the DOllars side I come up with a total of 114,510,445.49.

The Pivot Table on the other hand comes up with a total of 114,510,615.00. So there is a difference of 169.51. I know, I know, this is immaterial, but that will not fly if I try to use that excuse. I assume there is some kind of rounding error going on here, the question is, why?
 
Upvote 0
If there are 2 or more identical records, UNION will remove the duplicates. Change to UNION ALL and see if the total is correct.

Denis
 
Upvote 0
SydneyGeek said:
If there are 2 or more identical records, UNION will remove the duplicates. Change to UNION ALL and see if the total is correct.

Denis
Click to expand...


In thoery, I don't want duplicate records, but I tried what you suggested. This time my total is 1,369,793,766.00, so I'm even farther off. This sum that is an even dollar amount, that shouldn't be happening, as far as I can tell.
 
Upvote 0
I've looked into some of the source data of the pivot table. In all cases, values that should have values out to two decimal places are rounded to the nearst dollar. The data in the new union table alse has been rounded... Not sure how to undo this...
 
Last edited:
Upvote 0
ALright, sorry, the problem is somehow going all the way back to my orignal data tables. FOr some reason, access has rounded all the data to the nearest dollar, even though this was not the case before. reimporting my files doesn't seemt o be doing the trick. I would edit/delete the previous posts, but can't...
 
Upvote 0
Watch out of for datatypes in the tables (perhaps in the temp table). It should be Number Double or Currency, not Number Long Integer.
 
Upvote 0
You must log in or register to reply here.

Similar threads

JenniferMurphy
  • Question
How to structure a variable product rating database
Replies
5
Views
411
mdmilner
M
K
  • Question
Help repairing a Query Expression show records by a specified year
Replies
1
Views
276
Micron
M
H
  • Solved
ACCESS suitability for multiple column "TEMPLATE" concept
Replies
4
Views
434
heathball
H
airforceone
  • Question
CODE ALTERNATIVE?
Replies
8
Views
479
airforceone
airforceone
P
  • Question
If statement help needed
Replies
6
Views
376
Joe4
Joe4

Forum statistics

Threads
1,216,216
Messages
6,129,564
Members
449,516
Latest member
lukaderanged

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top