a puzzle, how to extract the right info from 2 tables

[dirkjan]

Hi,

I am working on a database for an airport. I made one table that registers all the numbers of sold airport tax numbers (these are tickets that are retrieved from the passengers, who purchase them earlier). I have another table that registers the next set of airport tax numbers that are given out (this info is being put in the database by the finance department). so I want to make a form that shows:
- all numbers that have been are sold, with a number higher than the number that has been issues by the finance department (these tickets have to be fake)
- all numbers that occur more than once in the table (people reusing of making tickets themselves).

table: sold_tax:
ID
input date
number (for example: 3200, so tickets can have the numbers from 1 to 3200)

retrieved tax tickets:
id
flightnumber
tax_number (for example; 2376, or 1865 or 56).




Who can help?

 

[lightray]

These tables don't necessarily have to be linked by a common field to be able to queried. However it seems 'number' and 'tax_number' are similar fields that could be linked in a query. You could use the find Duplicates wizard for some of your issues. Once you have created a query you can report it. Hope that is of some help.

 

[dirkjan]

duplicate works but..

So the duplicate option in the query module helped get half the problem fixed. Thanks for the tip.

I am still strugling with the other part, namely that one table only shows the 'ceiling' of the numbers that are allowed, and I have to match them with the actuall numbers in an other table. I am working with subforms, but it is not the most bueatiful solution.

 

[lightray]

Have you been able to create a query linking the two tables? linking on the fields I mentioned previously?

 

[dirkjan]

...

No, well I can only link the two in a form, having the field in a form and than create a query in a subform, in the query I relate to the formfield (maxnumber) and in the criteria I state that the number may not exceed the maxnumber. It is a workaround, cos when I want to refer directly to that field in the table, the query doesn't work. Also, when I load the form, I create a query will only show the last record of the table, which contains the latest max-number. Is works right now, I am happy and tired. But is remains a workaround.

Thanks again.

 

[lightray]

Are you able to post a cut-down version of the mdb with just the two tables and a dozen or so records and I will look at it.