Just simple query

[BorisGomel]

Hi everybody,



I am having a problem and be honest I am stuck with it , therefore any advise will be very helpful and appreciated .
The problem is that I have a table that is used to import excel file .There 6 fields that is
Name,JSTExpenses, Amount, Date, Court and Current Balance. The table is used to sort the records and then uploaded into Oracle to print a lot of checks .Every week around 15000-20000 records have been imported so it is pretty large table . The management would like now to separate JSTExpenses that called “Alliance “ into separated table and print the checks separately for this expense .I can do it in the simple cross query , but the problem that with every checks applied to Alliance and any cost second checks comes that issued to “JJL Service” that is also named in JST Expenses so there are two records and one record in unique and another is not .”JJL Service” is the same for all JSTExpenses records .The problem I need to separated records by “Alliance” that I can do and by “JJL service” that is the same for all expenses .The arrangement is in the first initial table like this

Alliance
JJL
Alliance
JJL
Meditation
JJL

Common field in here only Name if the checks Alliance and JJL should be printed together. I need to create a query like this if the first record in the filed JSTExpessssnes is :”Alliance” the next recorded that it si JJL should separated as well into query and so on .

Just for me it is kind of frustrated .First, I did make new field called IDCost and applied 1 for Alliance and 2 for JJL , but all JJL get IDcost 2 , but I need only those that related to Alliance.

Thank you in advance for any advice or input,

The Best Regards,

BorisGomel

[June7]

Show your SQL query statement for analysis. Not quite understanding your narrative. The query is returning Alliance and JJL but not Meditation?

Common field in here only Name if the checks Alliance and JJL should be printed together. I need to create a query like this if the first record in the filed JSTExpessssnes is :”Alliance” the next recorded that it si JJL should separated as well into query and so on .

These statements make no sense to me. Please restate your issue and what you want to accomplish.

[BorisGomel]

Good Afternoon June7

I am sorry June7 for my narrative .Wiil Trying to be more clearer. Ok, The table has five fields that are Name, JSTExpenses, Amount, Date and Court . JSTExpenses field has many expenses for examples:”Alliance” , “JJL” , “Record Fess”, “Filling Fees” ,”Mediation” and so on-totally 17 of them . I am being asked to select only JSTExpenses –“Alliance” but , here is a big “but” .Every expense come in pair which is “JJL” that deliveries court paper to people . So every expenses IS recorded in table first and then second expense always JJL . I need to select “Alliance” and also JJL expenses due printing two checks one to alliance and second goes to JJL .The problem is that JJL is the same for all expenses for examples there is Meditation fees and than goes JJL check that is the same “JJL” for all expenses in the table. The only name would be the same in the “Name” field if the Alliance and JJL expenses are to be printed in the pair .The below is arrangement that I have now in the table .

Name JSTExpenses Amount Date Court

Boris Alliance 10 06/10/11 Miami County
Boris JJL 25 06/10/11 JJL
Mark Mediaiton 60 06/10/2011 Orange County
Mark JJL 40 06/10/2011 JJL
Patrick Filling Fees 410 06/10/2011 Polk County
Patrick JJL 40 06/10/2011 JJL

I need to select only “Alliance” records along with JJL in query .The both records would have the same names- for examples if the Alliance is selected in query the below record would be JJL that has the same name in “Name” field as Alliance record above .
I am very puzzled with this.

Really, Really, Really will be grateful for any advice.

The Best Regards,

BorisGomel

[June7]

Your issue is much like the one discussed in this thread https://www.accessforums.net/queries...nce-14268.html

Your table structure is not relational. Unless the Name value is unique to each data pair for the common Date, there is no way for an Access query to exclude the JJL records that are not associated with Alliance, nor to know which JJL records go with which Alliance. For instance if there is a Boris Mediation and its associated Boris JJL for the example Date, query cannot differentiate the Mediation Boris JJL from the Alliance Boris JJL.

If the names are unique to the pairs by date, try this.
One query for all the non-JJL JSTExpenses records
One query for all the JJL JSTExpenses records
A query that joins the two on the Name field and maybe the Date field
In the join query you can sum the Amount fields from the individual queries and filter on the value Alliance.

BTW, you show misspelled words - Mediaiton and Filling should be Mediation and Filing. What is the actual spelling used in the database?

[BorisGomel]

Good Morning June7,

I did as you said and I think I got it .I created two select queries one is to return only Alliance records and second is return JJL ‘s .Then , I made union query to join them together and append the union query into final append table .In the final table , I have deleted all JJL record without pairs and it looks like I got it .I set a few IFF conditions in JJL query to adjust JJL records to be associated to Alliance and it looks like I got it . Thank you June 7!!!
I am sorry for my misspelling the words .As you see English is not my native language, trying to be more punctual in writing and will use the Microsoft Word to check spelling.
Will mark it as solved.

The Best Reagrds,

BorisGomel

[June7]

I am glad you have a solution. However, it isn't a UNION query but a regular SELECT with join that was suggested. A UNION query is entirely different and essentially results in the data structure you started out with.

Also, I was not suggesting that the query be restricted to the Alliance records. Will you at sometime want the Mediation or Filing Fees records? Possibly, it was not necessary to save to a table. Will you regularly import new data in this format?

[BorisGomel]

Hi June7,

I am sorry for dealy with replay . I created union query off two select queries one selects only Alliance and second selects JJL record .The small problem that as you have noticed in previouse post that JJl is not associted with Alliance record so all JJl records have been selected .THerefore , I have combined them togeher in union quiery by SQL and b/c no deleting allowed in union query , I have append it to final table where I can work with .The result is fine , I am able to delete all jjl that don't have pairs in the final table and sorted records as I wish , just a lot deleting JJLs . The jjl and alliance have names the same .I am thinking to associte both with comon name filed .Woking out on this . But so far solution is found and it is workable.

Thank you ,

The Best Reagards

[June7]

I only pursued this to make sure you understand that my suggested solution means no need to make table and no need to delete records and all data would remain available for variety of output. The approach you took is convoluted and limiting. Requires repeating process every time new data is received/processed. Or what if you want the Mediation or Filing Fees records?

[BorisGomel]

Hi June 7,

Yes , it is a lot of deleting and if I need different output for instance “Filling Fess” , two select queries again should be created , combine to union and so on .I am thinking to put this onto form and set a list box to chose what output I would like to have and through the list box the parameters would be changed in select quires .With my knowledge of Access now I can do it .The problem is still a lot deleting . I am thinking about that I can update unique id TO EACH jjL associated of expenses right here in the Access and then sorted them accordingly in queries .It is a little challenge for me.

Thank you June 7 for all your efforts with this treads and passed. It is not the first time you helped me .I am really, really, really appreciated you help and I would like you to know that you helped me a lot.

The Best Regards,

BorisGomel

[June7]

That does sound like a lot of work. The alternative I suggest doesn't require so much effort. But then maybe there is something else I don't know about your data that makes it unworkable.

Be aware that can't build a recordset in VBA code from a saved UNION query object. At least that is what I recently encountered. Had to redesign to eliminate a UNION query. If you have no reason for VBA recordset based on UNION query, then not a worry.