query returning duplicates

[hak]

I have created a query from two tables. I have created a form to do multiple field search in the query but I am getting duplicates. for instance if A is mentioned just once in the table query result will show it multiple times. can anyone pzl suggest any solutions





SQL view of the query

SELECT DISTINCT [Logging Information].[Customer Name], [Logging Information].[Contact Date], [Logging Information].[Contact Type], [Customer Information].[Mailing Machine], [Customer Information].[Folder Inseter], [Customer Information].[Printer/Copiers], [Customer Information].Shredders, [Customer Information].[Known Software], [Logging Information].Comments
FROM [Customer Information], [Logging Information];

[June7]

You have two tables in the query without a JOIN clause which results in a Cartesian relationship - every record of each table associates with every record of other table. Set a link in query between the key fields. Is there a customer ID in each table?

[hak]

thank u but now the problem is that when I connect Table:Customer Information "ID" with the logging information "customer address" and run the query there are no results displayed.

I am using this criteria to run the query.

Like "*" & [Forms]![CompetitiveAssets]![Known Software] & "*"

[June7]

Why do you duplicate the Customer Name and Address in Logging Information table? Why would you link ID with Address instead of Name? Exactly what value is saved in that Customer Name field? Is it the ID from Customer Information?

Advise not to use spaces or punctuation/special characters (underscore is only exception) in naming convention. Better would be CustomerInformation, CustomerName, Followup_Date, Application_P, etc.

[orange]

hak.

Step back from Access and tell us in plain English "what" you are trying to do.

Who is the Customer? What exactly is to be logged --in simple, plain English?

I think your table structure may be an issue, as June has noted.

[hak]

i have never worked before in Access and this is the first project of the job. I have tried hard to catchup things but I am business student so there are some problems due to lack of knowledge about databases. Thank you for your help.

[hak]

and this is the second page of the task that I was given

[June7]

This is an actual job assignment or coursework?

You didn't answer questions in post 4. Here's another - why are there CustomerName and CustomerInformation tables?

[hak]

this is an assignment that I am working as a university Intern. Its part of my program. I made two tables bcz Company A can have several different locations with different equipment and we need to identify these oppertunities at different location. I hope this answers your question

[hak]

This is the link to the database in case if you want to have a close look at this ingenious work
https://1drv.ms/u/s!Asr_jzFWEFJGaYYEPrm9iQMIU7I

[June7]

Again, advise you modify naming convention as already suggested.

I never set lookups in tables and I never use dynamic parameterized queries. As a developer I find both of these features annoying. When I view a table I want to see the actual values in field and when I view a query I don't want to be nagged with popup inputs because a form isn't open.

There is no need for the CustomerName field in Logging table. It can be retrieved by query linking tables.

Use comboboxes on forms for assets. Because you are saving text description and using LIKE and wildcard in dynamic query, your comboboxes can use data saved in CustomerInformation table.

Example RowSource for MailingMachine: SELECT DISTINCT [Mailing Machine] FROM [Customer Information] ORDER BY [Mailing Machine];

For CompetitiveAssets and LoggingInformation, set LimitToList to Yes

Comboboxes would also be useful on CustomerInformation form. Only instead of LimitToList set to Yes, set No so users can select from dropdown list if item already exists but still be allowed to enter new item (combobox would have to be requeried to make the new item available for selection in another record in the same edit session).

I don't know what PotentialProducts form is about.

Alternatively, build an Assets table and use that as source for comboboxes.

[hak]

thank u so much.

[hak]

so now my queries take criteria from 5 combo boxes in the forms

My criteria is
Is Null Or Like "*" & [Forms]![CompetitiveAssets]![MailingMachine] & "*"



So when I run the query by selecting criteria it also shows me the empty records which I don’t want but if I remove “Null” if there is a single field empty related to the five criteria it won't show me anything at all.

Can you plz suggest any solution for this.

[June7]

Deal with the Null values by creating fields in query with expression like: Nz([Mailing Machine],"none")

Apply the dynamic parameters to those calculated fields. You don't have to display these fields.

[hak]

thank you.