How do I not show duplicates

[Mtlca401]

I have two tables and I combined those tables into one query.

Table1 fields:
Number, Type, discipline

Table2 fields:
Number, Account, Bill, Requester

Table1 & 2 are joined in the query where all the records from 'table1' and only those records from 'table2' where two fields are joined.

The query looks like this:

Number Type Discipline Account Bill Requester
0001 A pipe
0002 G pipe 4567 45678 Ham
0002 G pipe 3678 36789 Ham
0003 B pipe

I only have three records total in table1, and two record in table2, both that apply to 0002.

how can i create a form based on the query that shows only one of the duplicated records. I want the total number of records in table1 to match the total number or records in the query.

 

[JeremyNYC]

You'll have to experiment with totals queries. Click on the sigma (backwards E) on the toolbar while looking at this query in design view. Check out the help files on totals queries to learn a bit more about them.

You're going to figure out the basis on which you want Jet to show you records--do you want to see that max of those other fields? The Sum? There are lots of choices, but you'll have to choose one or not show the field.

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[bsupport]

Mtlca401,

See your Help File in Access for "UniqueValues Property" (and I quote):

Prevent duplicate records in a query based on fields in the design grid.

[ol][li]Open a query in Design view.[/li]

[li]Select the query by clicking anywhere in query Design view outside the design grid and the field lists.[/li]

[li]Click Properties on the toolbar to display the query's property sheet.[/li]

[li]Set the UniqueValues property to Yes.[/li]

[li]If the query's results include more than one field, the combination of values from all fields must be unique for a given record to be included in the results.[li][/ol]
Who takes 7 seconds to develop,
7 mins to document,
7 hours to test,
7 months to fix will always blame the clock. s-)

 

[Mtlca401]

Neither of those worked.

this is what I have right now:

This is the SQL statement:
SELECT tblfian.FianNo, tblfian.[Receive Date], tblfian.[Unit No], tblfian.[Hull No], tblFianbill.[Bill No], tblFianbill.[Assignment Date], tblFianbill.FianNo
FROM tblfian LEFT JOIN tblFianbill ON tblfian.FianNo = tblFianbill.FianNo;

The first field(fian no) Is a primary field, but it has P-0006-F in their twice. But each fian no can have more than one bill no. the last Field (Fian No) can have duplicates. The first four columns are in a table by themselves. And the last three fields are in a table by themselves. I don't what any records to show up with the same fian no at all, in either one of the fian no fields. oh and the last fian no field will not actually be on the query. I just did that as example purposes.

 

[bsupport]

Mtlca401,

Question: Why bother with table 2 when you only want unique values from table one? If you don't care about the data in the fields of the duplicated records (last three fields of table two), then why have table two? What is it you really are trying to display or trying to do on your form? Who takes 7 seconds to develop,
7 mins to document,
7 hours to test,
7 months to fix will always blame the clock. s-)

 

[Mtlca401]

Well it is not my decision to do it this way.

What I am trying to do is build a form with a subform on it. I originally tried to to put the first four fields (fian no, date, hull no, unit no), in one sub form. Then put the last three fields in another sub form, and have the two subs work together, using a continous form. For the fian no. some will have bills and some will not. The ones with bills may or may not have multiple bills for it. If their is mutliple bills for a fian no, then a little indicated pops up. So this is what they are asking for. The fian sub form should show each record created in the fian table. Then the bills sub form should show the bills, if any, for the records created in the bills table, so the line up with the records on the fian sub form. They need to line up right, without showing duplicates on the first sub form. That is why am trying now to use a query. Because the first way didn't work. It's a little confusing, so keep asking questions until you get this.

 

[bsupport]

Ok, I think I understand what you are trying to show. Just to let you know, these tables need to be normalized, but I won't go into it now. For a quick form-fix:

1. Create a query using the fian table and the field fian_no only. Make sure your query includes the keyword DISTINCT.

2. Create a main form setting the recordsource of this form to the query you just created.

2. Use the wizard to create a combobox. Select the option to "Find a record on my form based on the value I selected in my combo box". Click Next. Select the fian_no field, click next. Click next on the "How wide..." screen. Give your fian_no field a decriptive name and click Finish.

3. Create a Subform/Subreport. Make sure the wizard is turned on, (the menu icon that looks like a majic wand with stars). Selct the option, "Use and exting form" and select your form you displayed above in this forum. Click next. On the next screen, select option "Define my own" and select fian_no in the top two boxes. Click next and give the subform a name and click finish.

You should now be able to select a fian_no in the combo box and have all related records from your original form displayed without the duplicate fian_no data.
Who takes 7 seconds to develop,
7 mins to document,
7 hours to test,
7 months to fix will always blame the clock. s-)