Calculate part information difference as per date

[mischa]

Hi,

I hope I post this in the correct section of the forum, forgive me if not.

I would like to ask for some advice because I'm don't know how to implement a functionality into my access database. The related tables required for this functionality can be found as an attachment.

When the information has been entered into the tables by the user, I would like to calculate the difference between the information for each part within the same table. The main columns for which the difference have to be calculated are: [RefObsInfo->RefSources] and [AltObsInfo->StatusAltSource] and [GenObsInfo->GenSources]. The values for these fields are restricted to 3 or 4 different ones. I also would like to show if the GenEstAvailable columns are changed, but that's for later.

In order to calculate this difference a query has to select for each Part the information related to the last date AND a date which has been specified by the user (with the previous date as max). For this the user is asked to give a from date, which she/he can select from a combobox. This combobox has a standard selected value which is the MaxDate-1, i.e. the previous date. The difference should be calculated from the previous date to the last date. I would prefer to give each (predefined) difference a specific action to show the user the difference in a matter of seconds.

Due to the complexity of the problem I don't know how to solve this problem and I hope someone on this forum could help me.

I am aware that I would need multiple queries and vba modules to get this job done.

Thanks in advance!

 

[Mihail]

The picture (pdf) don't help very much.

Start a new DB.
Import necessary tables. If you can import also the associated forms.
Restore the relationships.
Fill the tables with some data for test.
Based on this data show us the correct results (maybe using Excel)

Save the DB in a previous version of Access (2003 is the best)
ZIP it (because you hven't yet 10 posts).
Upload to the forum.

 

[mischa]

Thanks for your quick reply Mihail!

Sorry for the missing information on my part.

I did as you asked and attached the file to this post.
The only issue I found during this process was that I could not select save as as a access 2003 version in access 2013 but only one which is compatible with access 2000 versions so I chose that option.

I hope this piece of information is giving the information you need.

 

[Mihail]

OK. I have your DB.
I can say that, somehow (I need more time) is doable.

BUT !!!!!!!!
In tblAltPart and in tblSubComponent you have a text file as primary key..
I understand that the values in this fields are unique and can be the primary key.

But
1) If you will change, at a later time, a value in this field then the children of that record will become... orphans.
2) Access is smarter and faster when work with numbers than when work with strings.
3) The necessary memory is (usually) less for integers (AutoNumber is an integer) than for strings.

So, my advice is to fix this.

I'll return here when (and IF ) I'll have a solution.

Cheers !

 

[mischa]

OK. I have your DB.
I can say that, somehow (I need more time) is doable.

BUT !!!!!!!!
In tblAltPart and in tblSubComponent you have a text file as primary key..
I understand that the values in this fields are unique and can be the primary key.

But
1) If you will change, at a later time, a value in this field then the children of that record will become... orphans.
2) Access is smarter and faster when work with numbers than when work with strings.
3) The necessary memory is (usually) less for integers (AutoNumber is an integer) than for strings.

So, my advice is to fix this.

I'll return here when (and IF ) I'll have a solution.

Cheers !

Mihail, thanks !
I hope you are able to find a possibility, or get a slight idea of where I should start to solve this.

The reason why I used text as PK in both tables is due to the fact that many Part Numbers contain text. Thereby is this a guaranteed unique value, The only other option would be to use a autonumber as PK (which I've used in other tables as well). I didn't know the information you gave me but I read somewhere that when using many autonumbers, conflicts could occur which is why I chose for text as PK. What do you think about this issue?

Thanks a lot!

 

[Mihail]

I read somewhere that when using many autonumbers, conflicts could occur

1) I'm sure that this is not true. Is there someone that can change my mind ?
2) What mean "many" ? 2 ? (is "many", isn't it ? ), 200 ? 2000 ? 20000 ? ..... ?

No no and NO. This isn't an argue.

I hope you are able to find a possibility, or get a slight idea of where I should start to solve this.

At this time my idea is to design this queries:
Q1 - All records with LastDate
Q2 - All records with SelectedDate
Q3 - Records from Q2 that are also in Q1 (based on PN)

After I have this queries, I can:

1) to design one more query
Q4 with this fields:
PN (from Q3),
All fields from Q1
All fields from Q2
One more set of fields that will show the differences.

Or

2) to use Q1.. Q3 as records sets and use VBA in order to create a temporary table with the differences.
But this method is slower than the SQL (queries)

 

[Mihail]

I think that, based on q_Changes query (see attachment), you will be able to continue your work.

Let me know if I do a good job and/or if you need more help.

 

[mischa]

Sorry for keeping you waiting and thank you very much Mihail for all the help!
This weekend I didn't get any change to get on the computer, which is why I didn't get a change to test it and get back to you.

Today, I will get back to you regarding this subject.

 

[mischa]

I think that, based on q_Changes query (see attachment), you will be able to continue your work.

Let me know if I do a good job and/or if you need more help.

Mihail, you're a genius!
Such a simple but very effective solution.
Love it
As for now, it does everything I want.
Thank you therefore!

 

[Mihail]

Mihail, you're a genius!
Such a simple but very effective solution.
Love it
As for now, it does everything I want.
Thank you therefore!

Nothing new for me
Anyway we must ask other geniuses for a SQL solution.
I'm very sure that my approach is not the best one and the job can be done better by using SQL.

 

[mischa]

Nothing new for me
Anyway we must ask other geniuses for a SQL solution.
I'm very sure that my approach is not the best one and the job can be done better by using SQL.

I am quite curious about a SQL solution, especially if you say it could be a better solution.

I have have a follow up question about the queries which you designed because I would like to make difference between the values calculated in the IsRefSourcesChanged and IsRefEstAvailableChanged fields. I tried several options which didn't satify my needs.

If for example the information goes from 2 or More to Obsolete it should warn the user (more critically) than if the information goes from 2 or more to sole source.

At the moment I think that this could be solved if separate tables are made with the information with a new calculation formula. Before I continue this, I would like to know what you think about this or if there would be a much simpler solution to the case.

 

[mischa]

I am quite curious about a SQL solution, especially if you say it could be a better solution.

I have have a follow up question about the queries which you designed because I would like to make difference between the values calculated in the IsRefSourcesChanged and IsRefEstAvailableChanged fields. I tried several options which didn't satify my needs.

If for example the information goes from 2 or More to Obsolete it should warn the user (more critically) than if the information goes from 2 or more to sole source.

At the moment I think that this could be solved if separate tables are made with the information with a new calculation formula. Before I continue this, I would like to know what you think about this or if there would be a much simpler solution to the case.

Is it possible to make a formula in access that could show from and to which status it goes in one field?

 

[Mihail]

????????? Some example ?

 

[mischa]

????????? Some example ?

I made a new field where both values are combined into one field.
I think that by using vba I can get it to work.

Thank you for your reply!