Displaying filtered data in subform

[crxftw]

Hello. I'm designing an invoice database where you can enter Sales done to the clients, under where you choose service, hours done, employees who did the work etc. After submitting the sale it will be stored to table called 'Sales' and after setting 'Sale Complete' it will be ready to be invoiced at the end of the month. Now under 'Create Invoice' form when I choose a client, it's gonna display me all the sales related to that client that are marked as complete and are not invoiced yet. When I submit an invoice then 'InvoiceNumber' is generated which will be added to both 'Invoices' table and also to 'Sales' table. The reason is that I need to see what sales were added to that specific invoice later on, under 'Paid' tab.



Now the solution I am looking for is that how I can get my subform to filter 2 different queries. One query selects all sales related to that client which are not complete. And now do I need another query that displays me sales that are pinned to Paid invoice or how I can achieve that? Right now what is happening is that whenever I open Paid invoice it shows me 0 results in subform because subform source object is query that selects only completed invoices. But I need it to display sales that have same InvoiceNumber with the opened form InvoiceNumber. Hope it makes sense.

Regards.

[orange]

Can you show us the code for the 2 separate queries? Perhaps there is a way to make a UNION???

There are a number of free data models available that may help with table structures and relationships, if that is an issue. For example, http://www.databaseanswers.org/data_...ices/index.htm

[crxftw]

Well the thing is I don't have 2nd query that would do the trick. What I have is a query that selects data from sales table and shows all results on continuous form where StatusID = 20 (20 points to status 'Completed'). Subform master and child fields are set to Main form ClientID from combo -> Subform child ClientID so it selects data only for selected client. I am stuck here as I need paid invoices to display sales that were submitted.

[orange]

Ok, so you know the application and I don't, so you're going to have to tell me about the application; describe the tables etc.
Can you clearly define the problem, and perhaps I can mock up something to get a query or other approach that will give what you need/want?

I have 2003, so can not work with accdb.

Can you attach a jpg of your tables and relationships?

[crxftw]

Ok so, I try to make it as easy as I can.

I have database that stores services done to the clients. I call them sales. They are stored in 'Sales' table. When sale is set to complete which means it cannot be edited anymore and it is ready to be invoiced. Every sale has unique Sale Number, which is not primary key ID. This bit is working.

Now I come into creating an invoice. All invoices are stored into table called 'Invoices'. Under Invoices creating form you have to choose a client and next it's gonna show you all his/her completed sales that are not invoiced yet and all of them are shown in continuous form. When Invoice is submitted, unique Invoice Number will be generated (+1 increment to last one) and it's also not primary key. Submit button also sets this Invoice Number to each record that was selected for that client and updates it in 'Sales' table to identify which sales belong to that specific invoice. The problem is that once invoice is submitted then all these sales are set to 'Invoiced' and they will be never shown again when invoice is created. Also under Invoices tab I have 3 options to choose: Unpaid, Past Due, Paid. Under Unpaid I have all open invoices that are not paid/submitted/saved yet. Under Past Due I see all invoices that have passed their billing date and under Paid I should see all invoices that have been marked 'Paid'.

How can I make it happen that if I open up one of the Submitted invoices or Paid invoice then I see all these sales in subform that were selected during the invoice creation?

[orange]

Are you the database person? Are you working with Production data or do you have a copy to work with?

Are you able to create a query? Do you know the query wizard (again I have 2003 not 2010)
If yes, then (this is supposing that your main form is a Single record with many records in subform)
if you have a form open with the InvoiceNumber showing
you could create a query, along these lines.

SELECT * from Sales WHERE InvoiceNumber = " & Forms!YourFormName!InvoiceNumber

that should give you all data from Sales related to that InvoiceNumber.

[crxftw]

Yes I understand that. But the thing is that to view an invoice (not the report) I open up the same invoice creation form and all I have is only one subform which has Record Source query set to the one that only selects 'Completed' sales. I somehow need to attach 2 queries to that subform and it selects different record source depending on if I'm creating new invoice in which case completed sales will be chosen or if I'm trying to view a submitted/paid invoice in which case I want to show already invoiced sales.

[orange]

Yes, I understand that the system is restricting what you are being shown.

I was trying to create a query based on the Invoice number in the open form---to see if that would give the required data. Then, you'd have the info needed to either adjust the current Form, or create a new form etc. Until you know what data is needed in the solution, it would be hard to design a solution. Also, there may be other methods to get the required data on the Form for the User. The data base person and perhaps Operations personnel may influence any decision to be made to a production system.

The main point is you must clearly understand the issue/problem with the current situation; you must provide information to the people who will decide on the approach to solution; the "proposed solution should be developed and tested in a non production environment"; testing should have a clear definition of test data, processes and expected outcomes; when testing is done any differences between expected and observed outcomes must be fully rationalized; at some point technical and management personnel must approve a production change; the change must be scheduled and monitored, and any unforeseen situations must have an identified fall back position etc etc.

As I said at the beginning, you know your application and I don't. So, if I am to provide assistance, you'll have to describe what is happening; what makes it an issue; what is need to "fix" the issue; how do you implement changes.....

[crxftw]

Hehe thanks, but you are going too far with this. I am designing this whole database, there is no one else involved. Company is small, few employees and around 40+ growing clients.

Coming back to that query then yes it works as I would like to and I did it before, but I would need a solution how I can open 2 different forms for invoices perhaps?

[orange]

How about a form with an option group

1 -- open sales with this InvoiceNumber or
2 -- other option
3 -- another option with code behind the group to adjust the record source or whatever..

But, more realistically, it seems your Business Rules are not quite what you need.
Since you are the designer and because it appears that the business rules which should be supported by the design are not being handled as needed, a design change is required.

I think you have to list what information should be available when you process an Invoice and under what conditions if it is an issue of conditions.

Have you thought of other options that might apply?
Perhaps, you have build too many constraints and something should be relaxed. Perhaps under some circumstance(s)??

In your relationships, why isn't EmpHours joined with Sales??

Did you look at the data models at www.databaseanswers.org ?

[crxftw]

Ok so basically what I need is that I can create invoices based on the sales that have been recorded. I would say all related to sales are working as I need. This EmpHours table is something I work on later, under Sale Creating tab you can choose which employees did work for that client and for how many hours which will be stored into that table accordingly and later payslips will be printed to employees based on that and every employees hourly rate, but that's not the case I am on right now.

I think I need a different solution how invoices are created and displayed. How I need it to works seems fairly simple. I click Create Invoice, I choose a client, I get a list of all invoices that are complete and I submit it. Also I need to be able to mark this submitted invoice paid later on.

[orange]

Have you detailed the process of Sale and how a Sale becomes an Invoice?

When you make the Sale do you create a "pending Invoice item" or something?
Every time you create a sale (perhaps it is an Order?? or OrderItem???) there is an accompanying expected to be paid action ("pending invoice").

Do clients get 1 Invoice with Invoice Items at Invoice time, or do they get 1 Invoice per Sale?
Perhaps an Invoice isn't Complete until it is Paid? maybe there are statuses? Started, Pending, Sent, Paid...?

Do you have a list of Processes? If so, can you map these processes to Forms etc?

I don't see any Payments or related info in your model, is that a separate thing? How do you account for Payments against Invoices?

These thoughts are meant for communication only. The intent is to clearly identify the need so that a solution can be devised.