Relational Data Problem

[leo31]

Hi

Not sure if anyone can help. Its been about 10years since I've built a relational access database, but offered to build one for someone, but am now a little stuck.

Its a photo sales tracking db.

I have 3 main tables. Photos, Sales & Customers.

The main form is a photo form with embedded subform showing the list of the sales related to the photo. This works well!

The second form is a customer form with embedded subform showing the list of purchases (sales form) related to that customer. This again works, but I cant get it to show the photo title of that sale in that subform. I think I am missing some sort of link.

I have relationships between photos and sales, and customer and sales. But it always shows a blank photo title field in the customer purchases list

This is a very brief outline and I can explain in more detail if some thinks they can help. So far the database looks good and mostly works but it is all a bit relevant if I cant get the customer purchases to automatically show the name of the photo sold.

Any help most appreciated!

 

[ajetrumpet]

The second form is a customer form with embedded subform showing the list of purchases (sales form) related to that customer. This again works, but I cant get it to show the photo title of that sale in that subform. I think I am missing some sort of link.

I think so too. It sounds like the recordsource of this sub is missing a section of "JOIN" SQL. It's almost like the "photo title" field, wherever it is, has an invalid control source.

*Check that property.
*Also check to see if is a bound control.
*And check the SQL (or object name) of the recordsource.
*Last, check the types of relationships between all of the tables that are a part of this form.

 

[jdraw]

Hi

Not sure if anyone can help. Its been about 10years since I've built a relational access database, but offered to build one for someone, but am now a little stuck.

Its a photo sales tracking db.

I have 3 main tables. Photos, Sales & Customers.

The main form is a photo form with embedded subform showing the list of the sales related to the photo. This works well!

The second form is a customer form with embedded subform showing the list of purchases (sales form) related to that customer. This again works, but I cant get it to show the photo title of that sale in that subform. I think I am missing some sort of link.

I have relationships between photos and sales, and customer and sales. But it always shows a blank photo title field in the customer purchases list

This is a very brief outline and I can explain in more detail if some thinks they can help. So far the database looks good and mostly works but it is all a bit relevant if I cant get the customer purchases to automatically show the name of the photo sold.

Any help most appreciated!

Can you show your Table definitions?
Sounds like Sales would have fields like

SaleDate
CustmerId
PhotoId
Quantity
SalesAmount

If Customer can buy many Photos, you may want to have a SalesDetail Table as well.

 

[leo31]

These are my table definitions

Photo table. (data type of text unless stated)

ID (Autonumber)
Photo (OLE Object)
Filename (primary Key)
Phototitle
LimitedEditionTotal
EditionCount
LimitedEdition

Sales Table (in form embedded as a subform in Photos)
Filename
Phototitle
EditionCount
Invoicemuber
Location
Description
CustomerName
CustomerID

no primary key.


Customers Table
CustomerID (autonumber, primary Key)
CustomerName
InvoiceNumber
address etc...

Relationships:
Photo-Filename (1 to many) Sales-Filename
Customer-CustomerID (1 to many) Sales-CustomerID

My other option is to get the phototitle field to autopopulate in the subform form of the photo form (which I also dont know how to do) but the above is preferred to get this working.

Thanks for your help so far