Need to pull address from first record selected

[tanyapeila]

This is hard to explain in a post!
First let me say I am no Access Pro, I am self taught and turn to the Internet for all issues I have.



I have a database with several tables.

I am trying to pull a report based off a query.

The first and most important part of the query is that the record is selected, which is have set by adding a check box to each record, and have queried to pull this field as "true". This has worked in past queries from the same form with great success.

Now comes the confusing and not working part.

Of the three shippers (records) I am trying to pull, all three have different "Consignee fields" pulling from a Consignee table. When I pull this query even though all three have different consignees I only want it to pull the address of the first shipper's (record's) consignee for the report it is creating but still pull all three shipper's (records') names.

I'm sure more information will be need please let me know what you need!

[tanyapeila]

SELECT OUTBOUND.[SELECT], OUTBOUND.SHIPPER, OUTBOUND.GBL, OUTBOUND.RDD, OUTBOUND.GROSS, OUTBOUND.CUBE, OUTBOUND.[PCS X OF], OUTBOUND.[TOTAL PCS], OUTBOUND.SCAC, OUTBOUND.REMARKS, OUTBOUND.[CONT#], OUTBOUND.VESSEL, OUTBOUND.VOYAGE, OUTBOUND.TO, OUTBOUND.FROM, OUTBOUND.[SEAL#], OUTBOUND.ETD, OUTBOUND.ETA, OUTBOUND.[CUT OFF], OUTBOUND.SIZE, OUTBOUND.SHIPLINE, OUTBOUND.[ALSO NOTIFY], OUTBOUND.[BK#], OUTBOUND.NET, [INTERNATIONAL CARRIER].[CARRIER NAME], [Copy Of Consignee].ConsigneeName, [CONSIGNEE ADDRESSES].[Address 1], [CONSIGNEE ADDRESSES].[Address 2], [CONSIGNEE ADDRESSES].[Address 3], [CONSIGNEE ADDRESSES].City, [CONSIGNEE ADDRESSES].State, [CONSIGNEE ADDRESSES].Field1, [CONSIGNEE ADDRESSES].PostalCode, [CONSIGNEE ADDRESSES].WorkPhone
FROM [CONSIGNEE ADDRESSES], (OUTBOUND INNER JOIN [INTERNATIONAL CARRIER] ON OUTBOUND.SCAC = [INTERNATIONAL CARRIER].SCAC) INNER JOIN [Copy Of Consignee] ON (OUTBOUND.TO = [Copy Of Consignee].Country) AND (OUTBOUND.SCAC = [Copy Of Consignee].SCAC)
WHERE (((OUTBOUND.[SELECT])=True));


So I currently have three records selected. I want to take the field "Consignee Name" from "Copy of Consignee" table and join it with the Consignee Address table. However all three records have different Consignees and when I add this relationship it only give me one record. I want the query to pull all three records but just the consingee address from the first selected record.

Does that make sense?

[June7]

You probably have the join links set as INNER. Click on each link line to open the relationship dialog and change the join type. What happens?

Each consignee should show their related address. Making the additional consignees show address of the first would not be easy. What determines which is the 'first'?

[tanyapeila]

Good question, I'm not sure how to tell it which one is first so what we have found it the order you enter records in Access is the order the print on report. So "first" would be the record with lowest record number.

[tanyapeila]

sorry did not answer the first part of your message. I did change the join links and that seemed to have worked! Now I just have to figure out why it is not pulling the whole address just part of it. But that's another day! Thank you!!!!!!

[June7]

So what makes the 3 records associated with each other - an order number, shipping number? Is record number an autonumber field? What are actual field names?

[tanyapeila]

Well there are several fields that relate them, but I have created a checkbox field and we select the records we want on the report and that is part of the query. There is a container number and a booking number that are the same for all the selected records as well as the TO field.

[June7]

You still need to return the 'first' address? An expression using domain aggregates might help. What you need to know is the MailingListID of that record. Is BookingNumber text type?

Create a query that joins Outbound with Copy of Consignee. Then reference that query:

SELECT *, DLookup("MailingListID","queryname","RecordID=" & DMin("RecordID","queryname","[BookingNumber]='" & [BookingNumber] & "'")) AS FirstAddress FROM queryname;

Another approach might be a nested query using TOP N parameter. Review http://allenbrowne.com/subquery-01.html#TopN