Listbox issues whilst linking to another form (1 Viewer)

[flyers123]

Hi,

I have attached database, for some reason I am getting the debug message as in snapshot attached.

I am trying to link the invoice with appointment and service

Thank you in advance

 

[MarkK]

Your tables don't really make sense yet. What does it mean, for instance, that an Invoice can have many Appointments? Is that how it works in fact, that you invoice a customer once for multiple appointments?

 

[flyers123]

Thanks for your response MarkK. Please advise me something that I can do to fix the issue as I know already that there is issue with the database.

Thanks

 

[Minty]

You Relationships are not quite right. At the moment you can't make an appointment without an Invoice.
Link your customers to your appointments directly.
The invoice record is a separate entity, that happens to refer to one or many appointments and a single customer.
At the moment if someone cancels an appointment you still have to have an invoice record.

 

[flyers123]

Hi,

Ok, here is what I want

One Company can have many Employees

One Employee can serve many Customers

One Customer can have many invoices

One invoice can have many services (Services instead of appointments)

Please find attached i have tried to improve again but it does not seem to be doing as above

I need the following format for example


Invoice No: 1
Invoice Date: 00/00/2016
Customer ID: Cust1


Date Servicedescription Price Unit Total Amount

01/08/2016(Week 1) Wash,Comb, Shampoo $50 1 $50
08/08/2016(Week 2) Perm, haircut, wash $110 1 $110
15/08/2016(Week 3) Wash,Comb, Shampoo $50 1 $50
22/08/2016(Week 4) Wash,Comb, Shampoo $50 1 $50
29/08/2016(Week 5) Perm, haircut, wash $110 1 $110


When i change invoice number the services must be linked to a particular invoice

Please help

Thanks

 

[MarkK]

One Employee can serve many Customers

Yes, of course this is true, but this occurs in the context of an appointment, right? If a customer calls to make an appointment and a certain employee is on vacation, what happens? So it is not a business reality in your data that one employee has many customers, so that should change. Rather, an appointment should probably link to an employee and a customer, like . . .

tAppointment
AppointmentID (Primary Key)
CustomerID (Foreign Key)
EmployeeID (FK)
DateTime
Duration

Then, there are the services you offer, like in a catalog or something . . .

tService
ServiceID (PK)
ServiceName
Description
Price

...and then there are the services sold at a certain appointment, and that is essentailly the structure of your invoice, right? One invoice is a tabulation of the services sold at one appointment?

tAppointmentService
AppointmentServiceID (PK)
AppointmentID (FK)
ServiceID (FK)
Quantity
Price

Hope this helps,

 

[flyers123]

Hi,

Could you please help

I would like to link invoice number directly to services or invoice number to one appointment and one appointment has services

please help