Data Entry & Multiple Table Reference

[dr4ke]

Good afternoon,

I am currently very (VERY!!) new to access, but I'm looking to convert (half-convert) from Excel to make certain tasks easier! One of these tasks is the following;



Every month I receive a report, with about 10 columns, in excel. We'll call that report 'Report1' (mind-blowing huh!?). I think have to cross reference Report1, against the previous data that I hold in my logging system.

What I'd like to do is to have a interface; so that when the Access file is opened it will ask for the data from 'Report1' and then the new months data from the logging system.

Then...(yes there's more), I'd like to compare one set of data in 'Report1' called 'Reference' to the previous call logging data (at the moment I have 3 months, but this will grow every month to +1), and if it finds that information in any of the other 'tables' it will pull out the relevant data from the call logging data.

I hope that makes sense, and if it's possible; well i'd be very grateful if somebody could explain HOW it works, so I can learn as well as improve what i'm using

Thanks in advance,
Dr4ke

[pbaldy]

Welcome to the site. The lack of replies all day would imply that nobody really understands the question. I know I don't. You aren't storing each month's data in a new table, are you? That would be a normalization mistake. Typically you'd have the data in one table, with a date field. Perhaps you can post a sample of the data and the result you expect to achieve?

[dr4ke]

Thank you for your response, I can't seem to attach the database (even when zipped) perhaps I could email your direct?

Rgrds,
Dr4ke

[pbaldy]

Did you compact/repair before zipping? That will make most db's small enough.

[dr4ke]

Yeah it's only 10,000kb - not sure why it wont let me attach it. Strange!

[pbaldy]

You can email it to

removed

[dr4ke]

Thanks mate :-)

have emailed.

[pbaldy]

Here is the text of your email and the file for the forum (all I had to do was compact/repair):

Report 1 is the data that will be incoming from a third party every month. Logging Data are the reports that I will have to input every month, from our call logging system. So, essentially, moving forward every month there will be a new ‘Report 1’ to replace to previous, and an additional ‘Logging Data’ which won’t replace the previous data.

What I would like to do is, using the ‘Customer Reference’ in ‘Report 1’ lookup in the different ‘Logging data’ tables, and then display certain fields from the ‘logging data’ along with data from ‘Report 1’.

I’m not sure if this can be automated into an interface where you enter the data in and it automatically adds a new table and amends the query automatically to include it in this months search?

I hope that makes a little bit more sense?

Thanks,
Dr4ke

[pbaldy]

The more normalized approach would be not to have the monthly tables for logging data. I would probably have that data in one table, with an additional field for the date. If this is data you get somehow, you could run an append query to add it to your table. Otherwise you're looking at a UNION query to pull all the logging data into one place so you can lookup the customer reference.

[dr4ke]

Im happy to have the monthly data in the same tab, but how do i think get the query to copy across to the new 'third party' data?


regards,
Dr4ke

[pbaldy]

Where does it come from? You said "I will have to input every month", so you could input into this table. If you have it in a table or spreadsheet, you should be able to run an append query to copy it into the "one" table.

[dr4ke]

It comes from a third party company, but i'd like to have a seperate report for every months data.

[pbaldy]

If it's all in one table with a date field, you can easily isolate any month's activity with a parameter query.

[dr4ke]

Thank you :-)

[pbaldy]

No problem. Post again if you run into problems.