Pull a data from Customer Table based on a combo box

[hrdpgajjar]

Hi all,
I have a table named "CustomerT" which has several fields like,

- CustomerID
- CustomerName
- CustomerCity
-CustomerContactNo

Now i have another table named "DispatchOrderT". I need to copy all the customer details like, Name,City and contact no to this table bases on a form with combo box.

how can i pick all customer details with the id selection in combobox ?

 

[arnelgp]

the easiest way is to create a query against DispatchOrderT and Left Join it will CustomerT on DispatchOrderT.CustomerID = CustomerT.CustomerID and use the Query as Recodsource of your Form. Bring all fields from DispatchOrderT and all fields from CustomerT.

 

[hrdpgajjar]

the easiest way is to create a query against DispatchOrderT and Left Join it will CustomerT on DispatchOrderT.CustomerID = CustomerT.CustomerID and use the Query as Recodsource of your Form. Bring all fields from DispatchOrderT and all fields from CustomerT.

I have make the query and select it as record source of my form, and created a combo box but still not able to get the data, below i my sql view of query,

SELECT DispatchDetailsT.FarmerID, DispatchDetailsT.RegiNo, DispatchDetailsT.FarmerName, DispatchDetailsT.Village, DispatchDetailsT.Taluka, DispatchDetailsT.District, DispatchDetailsT.Area, DispatchDetailsT.MISsystem, DispatchDetailsT.DealerName, DispatchDetailsT.EmployeeName
FROM DispatchDetailsT LEFT JOIN FarmerT ON DispatchDetailsT.FarmerID = FarmerT.FarmerID;



What i need is, when i select a customer ID or name from combobox, then it will get all other details.


It would be great if u make any sample database and a sample form


cheers!!!

 

[arnelgp]

is the FarmerT not same as CustomerT?
why are you not saving the CustomerID?

 

[hrdpgajjar]

is the FarmerT not same as CustomerT?
why are you not saving the CustomerID?

Actually CustomerT = FarmerT
CustomerID = FarmerID


i have mentioned it as customerT for easy understanding.

i have attached my database file for reference. Please look to "NewdispatchorderF" i have created a combo box for regi no. This is the place where i need to pull all data from customer table


can u take a look pls ?


thanks

 

[arnelgp]

aha,, then what are the real fieldnames of FarmerT?
something like this:


SELECT DispatchDetailsT.FarmerID, DispatchDetailsT.RegiNo, DispatchDetailsT.FarmerName, DispatchDetailsT.Village, DispatchDetailsT.Taluka, DispatchDetailsT.District, DispatchDetailsT.Area, DispatchDetailsT.MISsystem, DispatchDetailsT.DealerName, DispatchDetailsT.EmployeeName,
FarmerT.Name, FarmerT.City, FarmerT.ContactNo
FROM DispatchDetailsT LEFT JOIN FarmerT ON DispatchDetailsT.FarmerID = FarmerT.FarmerID;

replace the one in red letter with correct fieldname from FarmerT table.

 

[mike60smart]

Hi all,
I have a table named "CustomerT" which has several fields like,

- CustomerID
- CustomerName
- CustomerCity
-CustomerContactNo

Now i have another table named "DispatchOrderT". I need to copy all the customer details like, Name,City and contact no to this table bases on a form with combo box.

how can i pick all customer details with the id selection in combobox ?

Hi
Your FarmerT should be named CustomerT to avoid the confusion you have caused with your question regarding Customers.

Why would you want to copy the details of the Farmer to the DespatchDetailsT ?

You are selecting the Farmername using a Combobox so that identifies the Farmer by storing the FarmerID in the DespatchDetailsT.

Also the FarmerT does not even contain the fieldnames you have quoted as Name, City & Contact No

 

[GPGeorge]

First,

Actually CustomerT = FarmerT
CustomerID = FarmerID


i have mentioned it as customerT for easy understanding.

Spoiler:

i have attached my database file for reference. Please look to "NewdispatchorderF" i have created a combo box for regi no. This is the place where i need to pull all data from customer table


can u take a look pls ?


thanks

1. Using made up names for objects does not make understanding easier; it makes understanding harder. It is confusing to people trying to understand what you really have in your database.
2. You do not need to copy data from one table to another. That's not only inefficient, it's also a quick path to bad data getting into the table.

The query Mike60Smart offered, with a Left Outer Join on the tables, returns all of the fields needed from the two source tables without copying anything from one table to the other.

 

[plog]

Stop everything and step away from your forms. Your table structure has a lot of errors that you need to fix before doing anything else. In fact, what you asked about initially shouldn't even be done--you get your tables right and it's no longer an issue to address.

First, foremost and in general--data does not get copied around in a referential database. It doesn't move from one table to another, it gets referenced. Instead of moving fields of data into a foreign table you just put the id value of that field (e.g. FarmerID) in the foreign table (e.g. FarmerT) into the appropriate field of the other table (e.g. DispatchDetailsT.FarmerID). You set your tables up to do this, but then you added fields for all those other fields as well which isn't correct (e.g. DispatchDetails.District, DispatchDetails.Area, DispatchDetails.DealerName, etc.).

I don't fully understand your data, but just looking at the above screenshot of your tables I see these errors:

1. Duplicated fields. This is what I explained above. If you have FarmerID in DispatchDetailsT you don't need those other fields. You just use a query, link FarmerT to DispatchDetailsT via FarmerID and you have all that data available. Don't copy data over, use a query and get it that way.

2. Spiderweb of relationships. Looks like every table is connected to every other table--that's not correct. There should only be one way to trace a path between any two table, you've got a spiderweb of paths. From InvoiceT I can go directly to FarmerT (path 1) or I can go from InvoiceT to LogisticsT to FarmerT (path 2) or I can go from InvoiceT to DispatchDetailsT to FarmerT (path 3) or I can go from InvoiceT to LogisticsT to DispatchDetailsT to FarmerT (path 4) or I can go (honestly since they all relate the paths are infinite). There should only be one path, I don't know which it is, but I know your relationships are wrong because I can trace so many ways.

To fix this, you need to eliminate some of the ID fields in those tables that make those relationships. Only 2 tables should have FarmerID fields--FarmerT for sure, then whatever one of those other tables is most directly related to Farmers. Same goes for all those other ID fields in more than 2 tables.

3. Storing totals. Not 100% sure you did this, but its odd that you have TotalInvoiceAmount in DispatchDetailsT. Seems like that should either be in InvoiceT or shouldn't be a field in a table at all but in a query where you sum InvoiceAmount. You shouldn't stiore values you can derive in other ways.

Again, put away any form building you are doing and get your tables right first.

 

[hrdpgajjar]

Hi
Your FarmerT should be named CustomerT to avoid the confusion you have caused with your question regarding Customers.

Why would you want to copy the details of the Farmer to the DespatchDetailsT ?

You are selecting the Farmername using a Combobox so that identifies the Farmer by storing the FarmerID in the DespatchDetailsT.

Also the FarmerT does not even contain the fieldnames you have quoted as Name, City & Contact No

My specific needs are,

1. Each farmer has assigned unique Registration Number, there must not be duplicate registration number in farmer table.
2. But each farmer can have multiple Dispatch orders and so multiple invoices. (that's why i need to copy all data to dispatch order table because i am not able to duplicate in my "FarmerT" as Registration field is unique)
3. I need to generate a query of which cases are pending for dispatch and which cases are done dispatch.


I know i am duplicating data in my dispatch order table, but is there any other option ?


pls suggest.

 

[hrdpgajjar]

Stop everything and step away from your forms. Your table structure has a lot of errors that you need to fix before doing anything else. In fact, what you asked about initially shouldn't even be done--you get your tables right and it's no longer an issue to address.

View attachment 118430

First, foremost and in general--data does not get copied around in a referential database. It doesn't move from one table to another, it gets referenced. Instead of moving fields of data into a foreign table you just put the id value of that field (e.g. FarmerID) in the foreign table (e.g. FarmerT) into the appropriate field of the other table (e.g. DispatchDetailsT.FarmerID). You set your tables up to do this, but then you added fields for all those other fields as well which isn't correct (e.g. DispatchDetails.District, DispatchDetails.Area, DispatchDetails.DealerName, etc.).

I don't fully understand your data, but just looking at the above screenshot of your tables I see these errors:

1. Duplicated fields. This is what I explained above. If you have FarmerID in DispatchDetailsT you don't need those other fields. You just use a query, link FarmerT to DispatchDetailsT via FarmerID and you have all that data available. Don't copy data over, use a query and get it that way.

2. Spiderweb of relationships. Looks like every table is connected to every other table--that's not correct. There should only be one way to trace a path between any two table, you've got a spiderweb of paths. From InvoiceT I can go directly to FarmerT (path 1) or I can go from InvoiceT to LogisticsT to FarmerT (path 2) or I can go from InvoiceT to DispatchDetailsT to FarmerT (path 3) or I can go from InvoiceT to LogisticsT to DispatchDetailsT to FarmerT (path 4) or I can go (honestly since they all relate the paths are infinite). There should only be one path, I don't know which it is, but I know your relationships are wrong because I can trace so many ways.

To fix this, you need to eliminate some of the ID fields in those tables that make those relationships. Only 2 tables should have FarmerID fields--FarmerT for sure, then whatever one of those other tables is most directly related to Farmers. Same goes for all those other ID fields in more than 2 tables.

3. Storing totals. Not 100% sure you did this, but its odd that you have TotalInvoiceAmount in DispatchDetailsT. Seems like that should either be in InvoiceT or shouldn't be a field in a table at all but in a query where you sum InvoiceAmount. You shouldn't stiore values you can derive in other ways.

Again, put away any form building you are doing and get your tables right first.

My specific needs are,

1. Each farmer has assigned unique Registration Number, there must not be duplicate registration number in farmer table.
2. But each farmer can have multiple Dispatch orders and so multiple invoices. (that's why i need to copy all data to dispatch order table because i am not able to duplicate in my "FarmerT" as Registration field is unique)
3. I need to generate a query of which cases are pending for dispatch and which cases are done dispatch.


I know i am duplicating data in my dispatch order table, but is there any other option ?


pls suggest.

 

[mike60smart]

Hi
I agree with everything that Plog said.

Your current FarmerT should not contain the following fields and should only contain field that describe the Farmer:-
MISSystem
DealerName
EmplyeeName
TotalMISCost

 

[plog]

Replying to your numbered points in post #11:

1. Unique RegNo. Not an issue. Make sure in Design view of the table you enforce this fields value to be unique.

2. Multiple Dispatch orders/invoices. Having multiple related records in another table is not a reason to copy over data. Your conclusion isn't logical. Can you elaborate on how you made this decision? All you need is the FarmerId vale not any other field.

3. Query for dispatch status. Simple enough but this has nothing to do with any points I made in my original post so I don't know what you are addressing by listing this

 

[hrdpgajjar]

Replying to your numbered points in post #11:

1. Unique RegNo. Not an issue. Make sure in Design view of the table you enforce this fields value to be unique.

2. Multiple Dispatch orders/invoices. Having multiple related records in another table is not a reason to copy over data. Your conclusion isn't logical. Can you elaborate on how you made this decision? All you need is the FarmerId vale not any other field.

3. Query for dispatch status. Simple enough but this has nothing to do with any points I made in my original post so I don't know what you are addressing by listing this

well let me try to work on your suggested path and will revert back if struggle in doing it.