Access Database Design to allow Data Imports (1 Viewer)

[Fearnix]

"Hello World"

Without going into too much detail, I require some assistance with setting up an Access database to track earned hours for my company. I am extremely comfortable with Excel and have been tracking many different things for my company via spreadsheets I have set up, but my Access skills are pretty novice at best. My company has decided they want this database so many users can access the data and run reports from the data.

To be 100% clear, first allow me to outline what my desired table/query/ w/e will need to look like.

I will want 5 columns, one for Jonas Codes, a column for the actual quantity installed to date, a column for actual quantity installed this week, a column for labor hours to date, and a column for labor hours this week. The last 4 columns should be a readout for the corresponding Jonas code in Column 1.

The data in the table above will be imported to Access 1x per week. It will be coming from two separate sources, one for the hours associated with each Jonas code and one for the actual installed quantities associated with each Jonas code. The nature of the import sources is such that only codes that have associated values will be presented.

Therefore I would like to be able to do these imports such that inside the final table it will have a list of all the Jonas codes ever entered into the database with no duplicate rows, and the corresponding values in columns 2-5 as outlined above.

I have tried tinkering around with Access to create such a table but I am having terrific difficulties in getting Access to take these separate sources of input and merge them into a table/query/report w/e that has the data as I specified it. It seems like either I end up with duplicated Jonas codes as it treats them as separate items even when they are the same number or I end up with Jonas codes not showing up. I want to be able to say something along the lines of [If the Jonas code = 0000x, then get columns 2-5 that correspond to Jonas code 0000x, if the Jonas code =0000y, then get columns 2-5 that correspond to Jonas code 0000y] and so on, so that my total number of rows = total Jonas codes with data.

From there I could design 1000s of reports to play with the data as I outlined but I am having difficulty figuring out how to get to that table result. Maybe I am thinking about this problem in the wrong manner. If so I would love to hear how someone else would approach this task.

I know I could create Excel spreadsheets to complete this task but it would be nice to be able to automate this reporting in Access.

Thanks in advance!

 

[Fearnix]

Sorry for not being more clear. Jonas is the project management software that our accountants log all our payroll information into. Literally the only reason I am using Jonas numbers is because it allows me to compare two separate things with identical ID numbers, for instance installing piping to a facility might be Jonas code 000001 for arguments sake. The Jonas Code number is just a predefined random serial set up to classify work activities on the construction site.

Therefore when my payroll department gives me labour hours it might have 40 hours last week for code 000001... then my quantity department will give me a report and and will say 100 meters installed in code 000001. Therefore I want my table to say for last week code 00001 had 40 hours and 100 meters installed. I can import both of the two departments charts but when I try to compile it into one table so code 000001 yields 40 hours in one column and 100 meters in another, I just get two entries for 000001 with one of them having 40 hours and 0 meters, and another one that says 0 hours and 100 meters... I just want one entry that says 40 hours and 100 meters.