Query based on data in a table

[akedm]

Hi,

My level of Access knowledge is low, low, low. IT at my work has asked me to create a table of invoice numbers, which I have. This table is the criteria - a long list of 6-digit invoice numbers. (attachment : criteria)

I've another table (and later maybe more than 1 table) that also contains these invoice numbers and balance information, etc...(attachment : table)

IT has assisted me in developing a query which pops up a window asking for an invoice number, then pulls up the information from the table (attachment : table)

The guts of the query are here (attachment : query).

The result of the query is here (attachment : result).

What I'd like to do is substitute the need for typing in the invoice number and producing one result with setting the criteria to the invoices I've entered (attachment : criteria) so I get all my queries at once.

Thanks.

 

[namliam]

Do you know how to make a join? Simply join your two tables and done

 

[akedm]

The IT Department said the same thing, and I've been reading about an "outer join"???? So if I'm getting you, and I'm probably not, but in my head I think you're saying that I need to

1) add the criteria table into my query
2) link the two tables together with that black arrow thingy between then when I select two of the same columns (in this case the invoice # columns)
3) place the invoice field from the criteria table into the location where the "[invoice number]" is at in the QUERY attachment above.

Right?

 

[InDFW]

If I understand what you are asking, your issue should be resolved by following these steps:

1. TOOLS
2. RELATIONSHIPS
3. If your tables do not appear, add them
........a. RELATIONSHIPS
........b. SHOW TABLE
4. Left Click on the Invoice Number, keep the button held down, drag to Field1, let go of the button

hope this does not confuse too much. I’m killing time waiting for an answer to my question (and procrastinating on getting back to my db)

 

[namliam]

The IT Department said the same thing, and I've been reading about an "outer join"???? So if I'm getting you, and I'm probably not, but in my head I think you're saying that I need to

1) add the criteria table into my query
2) link the two tables together with that black arrow thingy between then when I select two of the same columns (in this case the invoice # columns)
3) place the invoice field from the criteria table into the location where the "[invoice number]" is at in the QUERY attachment above.

Right?

Right, but forget about 3 as that is automaticaly done with 2. By creating the line (inner join) or changing it to a Arrow (outer join) you are doing 3...
The only thing is that the arrow will allow "null" values (non-existant) to show up on the arrow(ed) side.

The inner join absolutely requires a value to be there on both sides,

i.e. If your searched invoice number doesnt exist it will not show up at all on an inner join while on an outer join it will show up as the searched invoice, but with no additional (found) data behind it.