searching fields/ 1

[techkenny1]

Hi,

In a query in a single record I have 9 client fields, client 1, client 2 etc.
What I would like to do is in the query be able to query at the same time data from all the client fields.
eg: client1=smith, client2=brown, client3=smith; so that I can see all the data relating to each individual fields.
Hope this makes sense
Many thanks

kp

 

[MajP]

You can do what you are asking, but it looks like your data is not normalized. So to solve a lot of future headaches you should focus on fixing the structure. If you had a proper structure this would be very simple to do.

Can you describe your table structure and include important fields? Primary keys and foriegn keys.

tblName
fieldx
fieldy
filedz

You likely should have a client table with information about a client. Then clients probably get related back to whatever your main data is.

I assume either a Employee is related to clients, or a Case, or Bill. Also need to know if a Client can relate back to many primary records?

For example

Employe A has clients 1,2,3
EmployeeB has clients 1,3,6

 

[techkenny1]

Hi MajP,
Many thanks for your reply.
This Db was inherited, designed by a previous owner of this company.
The main invoicng is done by using code on the client form to open an invoice. This works great as one invoice per client. It is quite easy to retrieve invoice data per client.
The problem lies in some companies who want many clients to be invoiced on one invoice.
The structure of the invoice tble is, Primary Key is Invoices,The tbl has been designed as follows. There are fields for Company, CompanyID, First and Lastname, billing address etc. All this data on the invoice is derived from the client form.

Apart from the usual configuration for totals, subtotals, GST etc the structure allows 12 entries per form or invoice.

So that in a single record there are Date1, client1, Staff1, time1, followed by hrs worked by day, night, weekends etc.

The foreign key is BrokerID. So that the Invoice table is linked to the broker table.

Hope this makes sense..
kp

 

[MajP]

If you wanted to fix it the normal design is something like

TblInvoice
invoiceID
BrokeID_Fk
other invoice fields

tblClients
clientID
client fields
invoiceID_fk

This allows 1 or many clients per invoice.

If many clients support many invoices then you would need a third table

jncTbl_Clients_Invoices
clientID_fk
invoiceID_fk

If you are not interested in fixing it, you can normalize your data somewhat using union queries

select InvoiceID, client1 as Client from tblInvoices where not client1 is null UNION Select invoiceID, client 2 as Client from tblInvoices where not client2 is null,... UNION Select invoiceID, client9 as Client from tblInvoices where not client9 is null.

This should give you a complete list of each invoiceID and each client. Now you can use this query or a modification and query the union field "client"

This is an example, but you will need to use the union query in some form.