Basic Query: display on form if found question.

[jal]

Here's an attachment based on the new version of the query.

 

[hunoob]

Hi Jal! Thank you so much! This is definitely what I wanted. Thank you so so much!
There should be one more last thing before it is perfect. Displaying only the codenumber is not enough, so there are a few fields which are in the same row as the codenumbers and should be also displayed.

But unfortunately the attached sample file is not goodf for this. So I'll have to make again a sample file, with the same structure as the original. I'll have to alter the values and translate the columns. Tomorrow I'll post the new file. Thank you so much for your help!!!

 

[hunoob]

Hi Jal! Please find attached the new database file. As for the query you made last, there should be some additional data displayed.

Lets take for example the first row. The display of a codenumber should look like this (i write the name of columns):
Important_0 Detail0/4-Needed Detail0/1-Needed Detail0/2-Neede

If we take the first row as exmple, then it would look like this:
90091070 0Nh989hmnR0c kxFxKmnxmnÉ89h0 É8uh KxRx8uhKxhxLM0 RÉ8uhZVÉNYmnÁR8uhc8uhÁG "FxL8uhZÁM12LÁ8uh cLcmnmn" 67355 L 8uh

Please find the attached file! Thank you so much for your help!

 

[jal]

Can we continue to discard the whole record wherever Important_0 is duplicated?
I can see that Important_0 is often duplicated, but I"m not sure if ALL the columns are duplicated in these cases.

 

[hunoob]

You are right. Not whole the records are duplicated, and many records in Important_0 are duplicated. I am not sure what do you mean by discard?

 

[jal]

You are right. Not whole the records are duplicated, and many records in Important_0 are duplicated. I am not sure what do you mean by discard?

Sorry, didn't mean to suggest I was going to delete the record. What I meant is "ignore the dups." Let's say these two records are in the result set

Important_0......Detail_0
452.................AAAAAA
452.................BBBBBB

Are these dups? In one sense Yes. In another sense No. In another sense No. So do you want to see both in the result set? Or just the first one? You have to carefully define for me what a "dup" is and let me know if you want to include or exclude them.

 

[jal]

Are you looking for a evenly-spaced layout (a tabular layout where all the columns are aligned). With a textbox, this isn't so easily done.

This is easy with a listbox, but then you probably won't be able to copy and paste the material from the listbox into a Word document.

 

[hunoob]

Hmm... are you sure that the Important_0 and Detail_0 are different?? They should be always the same... Anyway only one record is enough (first one) and for me a table format also is good, or listbox format! It does not really matter because I will need to make a printable report form of the findigs.

 

[jal]

Okay the basic query would probably be this:

SELECT Important_0, [Detail0/4-Needed], [Detail0/1-Needed], [Detail0/2-Needed]

FROM [Database]
WHERE Important_2 = @NumberToFind

UNION ALL

SELECT Important_0, [Detail0/4-Needed], [Detail0/1-Needed], [Detail0/2-Needed]
WHERE Important_1 = @NumberToFind

However, I am trying to decide whether I should try to rewrite it as to eliminate the dups up-front instead of doing it later by means of a dictionary. Haven't decided yet.

 

[jal]

Here's a revised query that should eliminate dups up-front. Only I'm not sure I did it right because I started getting confused (I'm easily confused) as to which column I am supposed to be looking for dups. I think it's Important_0.

SELECT Important_0, FIRST(D.[Detail0/4-Needed]) as [Detail0/4-Needed], FIRST(D.[Detail0/1-Needed]) as [Detail0/1-Needed], FIRST(D.[Detail0/2-Needed]) as [Detail0/2-Needed]
FROM [Database] as D
WHERE Important_2 = @NumberToFind
GROUP BY Important_0

UNION ALL

SELECT Important_0, FIRST(D.[Detail0/4-Needed]) as [Detail0/4-Needed], FIRST(D.[Detail0/1-Needed]) as [Detail0/1-Needed], FIRST(D.[Detail0/2-Needed]) as [Detail0/2-Needed]
FROM [Database] as D
WHERE Important_1 = @NumberToFind
GROUP BY Important_0

 

[jal]

Ok, here's a possible solution.

 

[hunoob]

Hi Jal! Thank you for the code! I am trying to get it work with the origianl file, but I am having some problems. I'll try to experience a bit and get back to you soon! Thank you so much!

 

[hunoob]

Hello Jal! Thank you very much! It is fully working!!! Very nice work! Thank you! I have a question about one of the columns/fields. In the original file the column 'Detail0/2-Needed' is for storing some codes (like: S, C, L, EO_50VBC). But I would like that instead of these codes meaningfull words to be displayed (like: S stands for New partner, L stands for Old partner and so on). Perhaps the best way is to make another table where first field would contain the codes and the second field would containt the description of the codes. And then the macro (after the companies are sorted) would change these code values to the meaningful description. I hope you understand what I am trying to explain! I really appreciate your help!

 

[jal]

Hello Jal! Thank you very much! It is fully working!!! Very nice work! Thank you!

Make sure you FULLY test. All code, including queries, is extremely subtle. Make absolutely sure it returns the correct set of results in all possible circumstances.

I have a question about one of the columns/fields. In the original file the column 'Detail0/2-Needed' is for storing some codes (like: S, C, L, EO_50VBC). But I would like that instead of these codes meaningfull words to be displayed (like: S stands for New partner, L stands for Old partner and so on).

You could do this in the first query but it gets pretty ugly.

Perhaps the best way is to make another table where first field would contain the codes and the second field would containt the description of the codes.

This might be a better way, I'm not sure. I'll give it some thought.

 

[jal]

I got it working with a separate table - sort of. The new query runs fine from SQL View. But once you save the query, Access reorganizes it, resulting in a syntax error when run from VBA code !!!.

The way around this would be to type out the query in full in the VBA code like this:
qDef.SQL = "SELECT....."
I'm a little busy right now, though. Take a look at the new Abbreviations table, though, in answer to your request. Here's the version that works in SQL view:

SELECT Important_0, [Detail0/4-Needed], [Detail0/1-Needed], A.FullName as [Detail0/2-Needed]
FROM 
(
SELECT Important_0, FIRST(D.[Detail0/4-Needed]) as [Detail0/4-Needed], FIRST(D.[Detail0/1-Needed]) as [Detail0/1-Needed], FIRST(D.[Detail0/2-Needed]) as [Detail0/2-Needed]
FROM [Database] as D
WHERE Important_2 = @NumberToFind
GROUP BY Important_0
 
UNION ALL 
 
SELECT Important_0, FIRST(D.[Detail0/4-Needed]) as [Detail0/4-Needed], FIRST(D.[Detail0/1-Needed]) as [Detail0/1-Needed], FIRST(D.[Detail0/2-Needed]) as [Detail0/2-Needed]
FROM [Database] as D
WHERE Important_1 = @NumberToFind
GROUP BY Important_0
) as Connections
LEFT JOIN AbbreviationsForDetails02Needed as A
ON A.Abbreviation = Connections.[Detail0/2-Needed]

 

[hunoob]

Hello! As I told you I have this file in an excel file and searched for one codenumber, and it returned the same results as this access file. Anyway just to be sure, I will make more searches on different codenumbers, just to be sure that it works properly. Thank you for reminding it! Thank you again so much for your continuous help and support!

 

[jal]

In the original file the column 'Detail0/2-Needed' is for storing some codes (like: S, C, L, EO_50VBC). But I would like that instead of these codes meaningfull words to be displayed (like: S stands for New partner, L stands for Old partner and so on).

Yes, that's what I was working on. The idea was for you to type in these translations into the new Abbreviations table - I didn't you know had already stored them in an Excel file.

Again, though, it failed when I ran the new query from the main form. It only worked from Sql view.

 

[jal]

I made a copy of the query string and stored it in a new table called Miscellaneous. I pulled the query from this table since, as stated above, Access is corrupting the syntax of the "real" query.

Now it runs fine - you will need to type your values into the abbreviations table to make this version work properly. For example:

Abbrev.......FullName
O .............Old Partner

 

[hunoob]

Hi Jal! Thank you so much for your help! I am really glad that I met someone so helpful. I did not have enough time to check the code, but I think I should mention this... Your code works even if there are more than one codenames in the field? I mean something like: L S (these are two different codenames: L stands for something and S stands for something else). In excel I separated them using text to columns function... I don't know in case of access how can it be done... Anyway if it works with multiple codenames please just ignore my message. I will try the code tomorrow as now is very late and I am very sleepy! Thank you again for your help!!!

 

[jal]

No, that probably won't work - I'm not even sure how to program that. My code looks for exact matches. If the main table has:

Code
L-S

And the abbrevitions table has:


Abbrev.......FullName
L............. Lattice
S..............Sophet

This will result in a non match. My code would be looking for L-S:
Abbrev.......FullName
L-S............Lattice Sophet