why does it show none?

[Tech]

Hi there.

I am using a query/making one but for some reason, it doesn't show a specific entry ..

for example, I may wish to show all orders taken by EMP2 or EMP5, it shows none but in the actual order form, it shows they did take the orders.

any help or advice?

ftp://temp:temp@techftpserver.co.uk/mdffc.zip

 

[Pat Hartman]

Post your query.

 

[Tech]

it's in that zip file.....

 

[Tech]

any idea?

 

[Tech]

nope?

 

[Jon K]

Each time the download stops at 72%.

 

[Tech]

wierd, it shouldn't, works for other ppl..

email me ur email addy so I can email u it

 

[neileg]

I presume you are basing your query on the table OrderForm. The EmployeeID here is empty. This may be because your form [Order Form] is based on some SQL that does not include this field. You have brought in the EmployeeID from the CustomerDetails table.

I don't understand:
1) Why you have the employeeID in the customer details
2) Why you didn't build the query first and base your form on that

 

[Tech]

it's not based on a query.

the reason the customer details has the employee is cause so the company can assign certain employee's to customers.

and I don't have time to redesign the whole thing.

so, how can I solve this? yes, sqp statment was used

 

[neileg]

Tech, your reply does not make any sense. You have not answered the questions I posed.

You said:
I am using a query/making one but for some reason, it doesn't show a specific entry ..
for example, I may wish to show all orders taken by EMP2 or EMP5, it shows none but in the actual order form, it shows they did take the orders

As I said, I assume you are basing the query you refer to above on the OrderForm table. The EmployeeID is empty in this table.

I'm not suggestion you redesign the whole thing. I told you why the EmployeeID is empty in your table. The problem is in the record source for the form. You have to fix this for it to work. How you address this is up to you.

It is not clear how the EmployeeID in the CustomerDetails table relates to the EmployeeID in the OrderForm table. These have the ability to be different (as you currently have). You can hold only one ID in CustomerDetails but you can have many in the OrderForm table.

Whatever query you are designing could pick up the EmployeeID from the CustomerDetails table. Whether that is the right employee for the orders you are returning will be a matter of luck since it will always contain the most recent value entered on the [Order Form] form.

You may not have time to redesign the whole thing, but there is a design flaw that makes some of your data unreliable.

 

[Tech]

ok, yeh I follow.

the reason the employee ID is in the customer details table is because, if we put it in the order form, for some reason, it would overwrite all the employees taking a particular order....

so I now know from u that, it won't write to the order form table because of this(?)

what should I do?

I do not know where to begin r get started from.

it won't write the values to the order form table cause the query for the employee ID is used..correct? so it can't write to the order form table...correct?

 

[neileg]

the reason the employee ID is in the customer details table is because, if we put it in the order form, for some reason, it would overwrite all the employees taking a particular order....

If I change the SQL to include the employeeID from the OrderForm table, the form seems to work for me, writes the employeeID to the OrderForm table as I would expect. I haven't tested the whole system, but I can't see a problem.

so I now know from u that, it won't write to the order form table because of this(?)

Quite correct

what should I do?

Open the query builder on your Form RecordSource delete employeeID and add employeeID from the OrderForm table.

I do not know where to begin r get started from.

Now you do

it won't write the values to the order form table cause the query for the employee ID is used..correct? so it can't write to the order form table...correct?

No, it won't write to the EmployeeID field in your OrderForm table because you have not included that field in your form's recordsource.

 

[Tech]

thank u so much. u r a genious! I appreciate this.

one last request, in the query, if we of course, lets say order 5 items in one order, and emp1 took this order, if we enter it in the query and run it, it will show it fine but how can i get rid of all the items ordered and only show one set of order number?

so if we run this now, it will show 5 records of one order number, how can i just reduce this to one? (really, just showin the order number)?

so this will save alot of time and show like:

EMP1 = ORDER NUMBER 44 45 47
EMP2 = ORDER NUMBER 66 34 78 2 45

etc...

 

[KevinM]

If Emp1 has 5 orders then there is 5 records to show for that Emp in the query.

That is the correct way of showing the data.

To show it like the way you want would require a crosstab query, which isn't really needed.

 

[Tech]

thx.

cause if emp1 takes 1 orde (order 52), this order has 2 item, it shows order 52 2 times...y?

 

[KevinM]

Yes, correct

 

[Pat Hartman]

Having the orderdetails included in the query is what is causing the orders to be duplicated. If you don't need the detail, get rid of the detail table.

 

[Tech]

orderdetails? I can't find that...

 

[neileg]

Create a query with just EmployeeID and order number. Set the unique values property to yes. You will get one record per order.

 

[Tech]

hmm...ok, but where is this unique value? what happens if I need to put in additional info, such as customer number and price and also customer name?