subform displays only one record :(

[silentwolf]

Hi again! I know it has been discussed on the net but I can still not get it to work.
I have a searchform with many different typ of txt boxes to filter a subform wich is based on a qyr_ContactAll
The subform property is set to datasheet few and when I apply a flter it shows me how many records it has found. All good!
But for some reason I can not get all records to show on the datasheet few of my subform.
Where can be the problem?
Would be nice if someone can tell me what needs to be changed in order to make this work.

 

[silentwolf]

So I do it again!!!

What I have is tables and forms and quieries!

I start all over again once again!

tbl=Tables
frm=Forms
qyr=Queries
subfrm=Sub forms

I have following:
tbl_Contacts = all contacts I have either Personal, Customers, Suppliers, Employees
this table has all information regarding the contact but no address

So I have tbl_Contact, tbl_Con_Art, tbl_Contact_Art
The tbl_Con_Con_Art is the linked table as I have a many to many relationship
why? Because a contact can be a customer and a employee for example
tbl_Contact_Art has information on what is that contact! Is he or she a customer, a employee, a what ever.

So then I have forms.
frm_CardList, has controls on it like pages different pages like Customer, Employee, Personal, All, Supplier.

In those tabcontrols or pages or multipages I don t know for sure how they are called in english but the pages = pge_Supplier for example has a sub form on it.
Subfrm_Supplier which has its values of a query qyr_Supplier.
So when I select the page pge_Supplier a subform opens in that page and shows me all the suppliers in my subform.
And when I select employees on may pge_Employees then the subform_Employees opens and shows me every employee and so on.
I have no idea if I explain it right but I hope or just can hope it is now understandable!!

 

[Norie]

Sorry I'm confused - do you have more than one table?

Perhaps I misunderstood but in the earlier posts it sounded to me as though you had single table.

Why are you storing suppliers, customers and employees in one table?

That doesn't really make sense, though I sort of understand that an employee could be a customer etc.

That doesn't mean however that they should all be in the same table, they are different things.

For an employee you aren't going to have a delivery address are you, for a supplier you could actually have more than one contact etc.

What type of data is it you are dealing with anyway? What type of business/organisation is it for?

 

[silentwolf]

Hi Norie,
Thanks for your post.
Well I do have one table contakt where all contacts are stored. This is because I was dealing with customers and suppliers for a long time and they do have contact Id numbers.
When I change that again then all the contact numbers are wrong and I have a hard time finding correspondenz and so on to the right contact.
Or is there a way of getting it working with more tables too??
I am sorry I did got help from a friend who is a programmer so I was trusting him that it would be set up correctly.
The business is a housekeeping, cleaning services, and hole sales with a shop.
So I need to keep track of projects, tools and machinery, Inventory, and so on.
I am not sure how to explain it all it is a little complex I guess
So now I am a bit sad that all my work is not really working and well set up so now I guess I need to start again all over .(

 

[silentwolf]

Hi Norie,
not sure if you are here but I have now split the table tbl_Contacts to tbl_Customers, tbl_Employees, tbl_Suppliers, tbl_Personal
I am not sure if I need to open now a new thread or should stay on this one??
Now as for adresses should I have one adress table and linke them to customers, employees, and so forth or is it better to have the adress always in the relevant tables?
A customer can have more then one adress where work is done or where bills need to be send to.

 

[Norie]

It might be an idea to have a table for addresses but not for each customer.

If there is more than one place work can be carried out for a customer then there should be some sort of table for 'jobs' or 'work'.

That would have the details for the job location as well as any other data relevant to the job, eg start date, type of job.

It would also have a field which would link back to the customer.

This would probably be the primary key for the customer, Customer ID perhaps, and would be stored in the job table as a foreign key.

As for splitting out the original table, you could do that and keep the current data for each contact in whatever table they are moved to.

I hope you've kept the original database, tables etc.

If you are going to make any changes, never mind start again, you should make a backup before doing anything.

By the way what you have might not necessarily be set up 'wrongly' and if it has been working then that's the most important thing.

Might just need a bit of an update.

Please remember I'm only going on what you've posted, one thing that might help would be some sample data.

Or perhaps just more information about the fields etc in the tables.

 

[silentwolf]

Hi Norie,
thanks for your reply! Well what I have at present is following tables.
tbl_Customer,
Cust_id_PK
Cust_Fname txt
Cust_Sname txt
Cust_email hyperlink
and a few more but not addresses

tbl_Ad
Ad_id (lng) PK
Cust_id_f (lng) PK

tbl_Adress
Adr_id PK
Nr txt
Street txt
Town txt
PostCode txt
and a few more

As I have a employee table with simmilar values as in the tbl_Customer I would also need to link adresses to employees. So tbl_Ad is at prestent the link table for tbl_Customers and tbl_Adress.
Question
1. Can I use tbl_Ad also as a link table for tbl_Employees and further for tbl_Suppliers, tbl_Personal?
2. What about the Adress Typ (like is it a project adress, a billing adress, some kind of second home adress)

What I did create was a new table tbl_Adr_Typ for that reason. This table is not linked at the moment and has no data in yet.
tbl_Adr_Typ PK
Adr_Typ txt (for Project Adress, Billing Adress, Delivery Adress,...)

Question
3.Would I link tbl_Adr_Typ to tbl_Ad? Or how would I know what kind of address it is otherwise

Thanks for any input!!

 

[Norie]

I'll need to think about this, the last time I remember someone asking a similar question there was some discussion whether or not one table could/should
be used for the link between an address table and multiple other tables.

Personally I don't think it's practical to mix up all the addresses together.

For example an employee adrress is used for different purposes than a customer address is even if the person is an employee and customer.

Also how likely is it for a customer or employee to have more than one address anyway?

If multiple addresses are involved aren't they more likely to be for multiple jobs?

Another example, suppliers - surely the majority of contacts are going to have the same address, eg the main office for the supplier.

What could be different is the contact details, eg phone no, email etc

Does any of this make sense?

 

[silentwolf]

for an employee and supplier you are right there are not many who would have more than one adress.
But we are dealing with say a building company or arcitects who have more projects and on those projects can be one or more employees working on.
One other scenario is that a customer could live in Hanover for example and has a few holiday homes in and those are projects that we are looking after for hime or her
So that is really that what we are facing and maybe there is a easier way of structuring it as I have at present.
But sorry again I am still not fit enought to work it all out on my own and get a little lost sometimes when I am dealing with so many tables

 

[Norie]

I'll take your example of multiple projects for a customer living in Hanover.

Where would you send invoices to? The addresses of of the projects or to the customer's address in Hanover.

Aren't the multiple addresses related to the project rather than the customer, and the projects related to the customer.

eg one customer has many projects, each project has an address

 

[silentwolf]

The invoices would be send to the adress in Hanover for example.
Yes you are right with the customers have many projects and each project has a address!
It sounds like we are getting somewhere
Great! I really appreciate your help Norie!!! Many thanks to you !!