Newbie Question

[do12uk]

Hi to all members!
I am quite dealing with excel vba for reporting and I usually use Forms to make them userfriendly.
Well,I just wanted to check access for better usage for the task which needs to be repeated in a way.

The task is as following: User extract data via using crystal reports and import it to excel.
After that,this datasheet will be imported into access as a table,which is needed for storage too.

This task will be done for not every day but at least four times a week.
The differences between last two tabels (as data) needs to be defined.
For now, I accomplish this task with using "Find Unmatched Query Wizard" though.

What I want,or I can say "thought", is make this procedure dynamic.

For ex, there are tables named as "Data1-2009-01-05","Data1-2009-01-06","Data1-2009-01-09","Data1-2009-01-10" and so on.
User will have the ability to choose data tables (which are specific,by date) via using Form (may be Combobox will do it) and compare them with not using wizard but -I dont know- sql or vba embedded into CB.

I would be glad if anyone at least give me a clue; so I can dig it up.

Thanks and Regards,

Doruk

 

[MarkK]

Welcome to the forum:
My 2 cents is that it doesn't make sense to have tables named by date. A date is a scalar quantity that lends itself better to being data. Like in accounting software you don't have a separate table for transactions under $1000, a table for transactions between $1000 and $10,000, and a table for transactions over $10,000. In accounting you also don't have separate tables for transactions that occur on Tuesdays.
The object is the transaction, and the date and the amount of the transaction are attributes of the object, which equate to fields in the record.
I think you'll save a lot of time and create a far more flexible and useful system if you can rework things so the date of whatever object you're dealing with is a field in the record, not the name of the table.

 

[do12uk]

Dear lagbolt,
Thanks for your reply.
Let me explain why I do such a way to accomplish the task.
The db which I extract records works based on the "data entry".Think this db as a operator put data as an input.But it does not record entry dates!(I know its a shame of coders)
Well, what I do is, since I can not extract from our company's db as date based, to get data from there using crystal reports.
The thing is, since I do such thing, data repeat itself (this is why I save tables based on dates).
Let me give brief info regarding data structure:
Think as this is an extract which I have get yesterday:
OP A/C Rev Initiate Date Closure Date
111-10 1 A 01.10.2008 01.12.2009
111-11 1 13.08.2008 01.12.2009

And today I got as following:
OP A/C Rev Initiate Date Closure Date
111-10 1 A 01.10.2008 01.12.2009
111-11 1 13.08.2008 01.12.2009
111-12 1 13.08.2008 01.13.2009

So, OP:111-12 is the difference I need to find.
I hope I did explain myself this time.

Thanks and best regards,

Doruk

 

[do12uk]

Hi again,
Ok,I got some way, managed to create two comboboxes with table names which updates themselves dynamically, using "MSysObjects".
Simply created a query and assign it to comboboxes.
Now, what I need to do is, using selected tables, "Find Unmatched" data.
I checked sql of the "Find Unmatched Query" which I have created via wizard, that you should probably already know:

SELECT [CVS-2009-01-12].TV, [CVS-2009-01-12].SHIP, [CVS-2009-01-12].SWBS, [CVS-2009-01-12].[OPERATION NO], [CVS-2009-01-12].REV, [CVS-2009-01-12].[Comp-Date], [CVS-2009-01-12].[Curr-Date]
FROM [CVS-2009-01-12] LEFT JOIN [CVS-2008-12-26] ON [CVS-2009-01-12].[OPERATION NO] = [CVS-2008-12-26].[OPERATION NO]
WHERE ((([CVS-2008-12-26].[OPERATION NO]) Is Null));

If that would be in excel, I could do it. So based on my knowledge, I could use two variables such as tbl1 and tbl2 and assign them as both combobox values and as table names in the sql code. Finally I could use a command button to extract it.

I am kinda stuck in here, would be glad If any help/advice given..

Thanks,

Doruk

 

[Tanya]

By extract, do you mean you want to append the result of the 'Find Unmatched Query' by using a commandbutton? If so you need to run an Append query based on your unmatched query, to check that this works I would view the data in datasheet mode as aposed to running it first to see if you get the data you want. Then if you are happy with the results of the append query you can use the wizzard to create your commandbutton.