Record Results Problem

[Phil_b]

Front End: MS Access
Back End: SQL Server

I currently have a client record database. The most important tables are the client and order tables which are joined via 'clientID'. I have built a search system which displays the results in a subform below the search criteria. The problem is that if a client has more than one order the results show multiple instances of their record...

My plan is to have a 'view selection' button which will then open the form with this selection (minus duplicates).
I was thinking about doing this in one of two ways:

1) Take the client id's from the subform and build them into an IN( insert CSV client ID's here) then open the form with this filter... or
2) Try to remove duplicate records from my result set somehow...

My problem is I have no idea how to do either... I can give example or screenshots if needed. This is the last part of the database and so any help appreciated.

Phil

 

[macca the hacke]

Hi

Why don't you have your main form with the client details, and then have a subform with the order details. You would then have 1 form showing all orders for that client. When joining the subform to the main form you would obviously join by client ID.

 

[wazz]

your question is unclear.

...I have built a search system which displays the results in a subform below the search criteria. The problem is that if a client has more than one order the results show multiple instances of their record...

their record? do you mean their ID?

My plan is to have a 'view selection' button which will then open the form with this selection (minus duplicates).

'view selection'? aren't you already looking at a selection in the subform?

1) Take the client id's from the subform and build them into an IN( insert CSV client ID's here) then open the form with this filter... or
2) Try to remove duplicate records from my result set somehow...

- maybe you could refine your initial search further;
- it's hard to know what to suggest without knowing what you're looking for

I can give example or screenshots if needed.

maybe a good idea but perhaps a more refined query to begin with? perhaps show the (fewer) results in a listbox first then go to a form from there.

openform "frmClient" where Client ID = frmSearch!lstResults

something like that?

 

[Phil_b]

thanks for the response guys. I didnt want to give too much in my first post as I thought it might over complicate.

My main form shows all Clients (one record per client) with a subform showing invoices specific to that Client. Everything works fine apart from when I want to search. I can search and display/ filter information based on any criteria in the Client table. My problem comes if I want to search for information that is in the Orders table...

To do this I must add a join to my query and as soon as I do this I dont just get one row per Client but one row * number of invoices the client has...

In the attachment I have search for all records with a WholeName. By adding the join it is displaying multiple records of clients with more than one invoice. I dont mind this in the search screen but on the Client Form I cant have duplicate records. I can 'view' an individual record from the Search form but I need to ability to 'View Selection'. So Wazz yeah I am viewing the selection but not in the Client Form only in a search form.

maybe a good idea but perhaps a more refined query to begin with? perhaps show the (fewer) results in a listbox first then go to a form from there.

openform "frmClient" where Client ID = frmSearch!lstResults

something like that?

This is the path im trying to get too. If I can extract all Client ID's from the subform and add them as a filter when opening the Client Form my problem would be solved. This way I woudn't need a join on my client form (only on my search form). Would 'frmSearch!lstResults' list just the client ID data for a SubForm with the name of frmSearch?

Again thanks for your help, I seem to be getting 95% of the way there but the last 5% is causing problems.

 

[wazz]

it's still hard to figure out what you want. are you saying you're happy with the search results? and now you want to select one single record from the search results and open it in the client form?

 

[Phil_b]

hi wazz,

I have a form with controls from tblClients. I have a subform on that control which is linked to the clientid. It hence shows all invoices for the client.

Hence my query for the frmClients has no joins but just pulls information from tblClients. This is fine and allows me to search (and filter) all results that are in tblClients. My problem is I need the function to search for invoice number which is in tblOrders...

As soon as I add a join to tblClients it shows a client record for every invoice. So if they have 3 invoices it will show 3 identical records for that client instead of one... I cant think of any way to search for data that isnt in my main table...

I hope that clarifies some more

 

[wazz]

hi phil. quick question. do you have to search first or can you just enter an OrderID and go directly to the order?

i think the easiest approach is to create another form and a new query:
- create a NEW query with both tables in the grid (tblClients and tblOrders)
- add all fields from tblClients and only OrdersID from tblOrders
- in the criteria row of OrdersID add [Forms]![frmClients]![InvoiceNum] or whatever the proper location of the field InvoiceNum is
- that is your query to filter a new clients form (Clients_Filtered or something like that) (you could filter the same clients form but i want to see if we're talking about the same thing)

 

[Phil_b]

Currently my search system allows for searching in any field in the client table. Instead of going to an individual order it was decided to go to a 'selection' based on a filter. For company name:

strFilter = strFilter & " ((sp_InvRec_No_Freebie.cName Like ""%" & Me.companyQuick & "%"")) AND"

I then build up the string and add it as a filter. Thats no problem but searching and filtering by invoice number means I must add a join to my query.

As can be seen in the screenshot attached all of the top data is the tblClient data. Thats easy to filter by but the below data (in the subform) is from the tblOrders table and I have no idea how to filter based on that criteria.

The second I add a join to my query it shows a client row for every order that client has. I understand the logic but to filter and then show those results will end in alot of duplicates. Is it possible to display all results in a subform and then get from that a list of the Client ID's? If so I can then add that as a filter (without needing a join).

 

[wazz]

with some minor adjustments this might help:

first query:
- create a query with the join (you get the duplicate Client IDs) (maybe you can use the search query?)
second query:
- use the first query as your source (instead of a table)
- drag only the ClientID to the grid
- click on Totals (Group By)
- you should now have your unique client IDs

 

[Phil_b]

after the best part of 2 days this is how I did it:

I setup a stored procedure with one parameter (my invoice number) and set this as the record source of a dropdown box whenever a user searched for 'Invoice Number'. (I had to do this via VBA so i could add my invoice number parameter). The list of Client ID's was then extracted from the dropdown box and Concatenated onto the strFilter string. That way I did not need a join on my main form as my dropdown box did the leg work.


Thanks for your input and direction wazz.