Solved Duplicate Records in Order Query

[Sarah.M]

Hi, Plz note, My VBA is disabled at work for security reason
I can use only Expression builder, Macros, Queries, Tables, Forms and Reports only.
-----------------
I have Order Query, I want Management, Section and Group to be shown only with each records, to determine the records. but the records now disappear plz help me to fix it.
plz!
Database Attached

 

[theDBguy]

You have to be careful when you construct your query if you're going to include multiple tables. You should only include the tables necessary to display the data you're looking for. When you use an INNER JOIN, all tables must have a matching record; or else, that record won't be include in the query result. Maybe you can take a look at this article to see how you can return the records without matches from the other tables.

Query Join Basics

Recently, I have noticed a few developers, new to Access, showing up in the forums and were confused by the different JOINs that can be used...

thedbguy.blogspot.com

 

[Sarah.M]

You have to be careful when you construct your query if you're going to include multiple tables. You should only include the tables necessary to display the data you're looking for. When you use an INNER JOIN, all tables must have a matching record; or else, that record won't be include in the query result. Maybe you can take a look at this article to see how you can return the records without matches from the other tables.

Query Join Basics

Recently, I have noticed a few developers, new to Access, showing up in the forums and were confused by the different JOINs that can be used...

thedbguy.blogspot.com

Hi
Kindly can u upload fixed one here?
Plz!

 

[theDBguy]

Hi
Kindly can u upload fixed one here?
Plz!

But I don't know what you want in your query. If you can post a sample set of data (probably using Excel) to show us what you expect the query to return, then we can maybe fix your query.

 

[theDBguy]

Looking at your tables, GroupInT is empty. Since you're using INNER JOINs, that's why your query is not returning any records. There's no matching record in it.

PS. WorkInT is also empty. Looks like you need to fix your tables first. After that, your query may work. If not, then you can fix your query, once the tables are fixed.

 

[Sarah.M]

I will try to rebuild them and get back to you soon
Thanks!

 

[Sarah.M]

Looking at your tables, GroupInT is empty.

Hi, Yes that is correct my WorkInT and GroupInT are empty, I put data on them but, I have small issues,
OrderT has 13 Records,
but OrderQ has 16 Records, why it is not 13?!
Plz help me to fix it,
I really grow up in Access!!! Thanks indeed

 

[arnelgp]

table WorkInT, has 2 records for Nancy?

 

[Sarah.M]

table WorkInT, has 2 records for Nancy?

Hi,
Yes, because she can work in more than one section
so how can we fix that
plz help!

 

[arnelgp]

but i think it Doesn't matter which section she is working when
she enters Orders?
if you include WorkInT table it will Only Duplicate the records that
belongs to her (did you see the form, only her record is showing duplicate).

 

[Sarah.M]

but i think it Doesn't matter which section she is working when
she enters Orders?
if you include WorkInT table it will Only Duplicate the records that
belongs to her (did you see the form, only her record is showing duplicate).

Something went wrong I am lost?
I put OrderQ in OrderSingleF and he did not duplicate them, so why in OrderF are duplicated

I put OrderQ in OrderSingleF and he did not duplicate them, so why in OrderF are duplicated over there?! plz help

 

[Sarah.M]

but i think it Doesn't matter which section she is working when
she enters Orders?
if you include WorkInT table it will Only Duplicate the records that
belongs to her (did you see the form, only her record is showing duplicate).

I deleted them all and started over, I made 1 Order from OrderT he gave me 2 Orders in OrderF that is not make sense to me? do you think there is a mistake in my Database Relationship design? I changed query sitting to (Unique Values and Unique Records to Yes) but same nothing changed?!
please help

 

[arnelgp]

here try this one, i use Dlookup and a function concatSection.

 

[Sarah.M]

here try this one, i use Dlookup and a function concatSection.

You did a great job,
but I am sorry my VBA is blocked, kindly can you make them by Query or SQL
This is one of the obstacles and difficult circumstances
please do not be disappointed
I am sorry!

 

[arnelgp]

i made small queries (query1 - query4) and qryUserSects.
instead of vba i used dlookup on qryUserSects.

 

[arnelgp]

you'll have a though time without VBA. not all that you can do in VBA
can be translated to macro.

 

[Sarah.M]

you'll have a though time without VBA. not all that you can do in VBA
can be translated to macro.

Thanks indeed!!!
I understand that, but my VBA is disabled at work for security reason. at the same time I do not want to give up! I do not want to stop learning skills
-Special thanks to you and to ur advice

 

[Pat Hartman]

Hi, Yes that is correct my WorkInT and GroupInT are empty, I put data on them but, I have small issues,
OrderT has 13 Records,
but OrderQ has 16 Records, why it is not 13?!

You have two parallel relationships. If the user exists more than once in either of them, that will cause duplication of the order.
You were already told about the inner vs left join issue.

There is another issue which although not "wrong" will certainly cause much confusion. You have given your autonumbers all the name "ID" rather than a unique name appropriate to the table they are in. Then for the FK names you are using the name of the text field. This leads me to believe you are using table level lookups. That will cause nothing but problems going forward in addition to the confusion caused by the FK name being = the text field name rather than the autonumber name to which it is actually related.

You might want to ask your management why they have asked you to create a program but removed your tools? Is it just you they have crippled or is it everyone?

There are other ways to control security without disabling VBA. I'm sure that someone will chime in and describe them.

 

[Sarah.M]

You have two parallel relationships. If the user exists more than once in either of them, that will cause duplication of the order.
You were already told about the inner vs left join issue.

There is another issue which although not "wrong" will certainly cause much confusion. You have given your autonumbers all the name "ID" rather than a unique name appropriate to the table they are in. Then for the FK names you are using the name of the text field. This leads me to believe you are using table level lookups. That will cause nothing but problems going forward in addition to the confusion caused by the FK name being = the text field name rather than the autonumber name to which it is actually related.

You might want to ask your management why they have asked you to create a program but removed your tools? Is it just you they have crippled or is it everyone?

There are other ways to control security without disabling VBA. I'm sure that someone will chime in and describe them.

Hi, Thanks for your post, kindly can you show us what you just told by upload the database as alternative solution, easy for me to understand it.
plz!

 

[Pat Hartman]

Thanks for your post, kindly can you show us what you just told by upload the database as alternative solution, easy for me to understand it.
plz!!

I appreciate your conundrum but how much are you willing to pay me to do work which you are going to pass off as your own and get paid for producing? Think about it. You are asking me to do work for you for free that you are going to get paid for. We are here to help you to solve problems but we don't get paid for this. We are volunteers. Arnel has done several things that he thinks fixes the problem. I can't tell. I didn't examine his "try this" because I didn't know how he had integrated it. I didn't even download the database, I just made my comments based on your schema.

I assumed that arnel had solved your problem and I was commenting on other things that will cause problems later rather than sooner. So, if you haven't tried arnel's solution, you should try it and post back with results or questions.

You are very new here and possibly to forums in general but it is really poor form to ask people to download your database and fix it for you. Sometimes we do that but there is way too much wrong with your database that is not at all involved in the problem you have. The database will work if you don't make the changes I suggested. It will be harder to change because the names are too confusing. The picture below tells you what I am talking about. This technique is a crutch and there are at least a dozen ways it will get you in trouble. The combos belong on forms and sometimes reports but NEVER in tables. Having this lookup in the table is helpful to you because you see a name rather than a number but this is just a crutch since you can always see the name if you create a query. But the downside is that you don't see the number which when you are testing might be critical to your analysis. The user is not affected by this at all because users never interact with tables or queries directly.