Help or suggestion needed on tabledesign

[MarMo]

Hi ,

From a table "tblInvoices" (imported data) I need to send to several
companies ,the monthly status of the invoiced orders.

For this i need to query the tblInvoices with multiple clientnbrs for
1 client and mail it to 1 or mulitple mailadresses.

Example : for masterclientnr 100 (Xerox) i need to extract data for
his subclientnbrs 100, 101, 102, 103 and send this query (as txt or
Excel) to (e-mail address removed) , (e-mail address removed).

So i'm trying to put all this info into tables with relationships ,
but i'm a little stuck.

Has anyone have a suggestion what tables i should create in order to
be able to retrieve the masterclientnbr , the subclientnbr(s) , the
mailadresses .

Many thanks for helping me out.
Mario

 

[KARL DEWEY]

It depends on what data you have in the tblInvoices. What is the structure
- field names and datatype. Post sample data.

What is the structure - field names and datatype of table with client
information. Post sample data.

 

[MarMo]

I'll try to be as clear as possible, cause i'm not good in explaining
things.

The data in the tblInvoices is standard data like
Ordernbr , Clientnbr , Clientname, Adres ,
and all other related data for these orders.

ORDERNR CLIENTNR CLIENTNAME REFERENCE
10010 1212 xerox
X14
10011 1212 xerox
X15
10012 1213 xerox
Z101
10013 1213 xerox
Z102
10014 2255 HP
HP77
10015 1212 xerox
X16
10016 2255 HP
HP78
10017 2255 HP
HP79
10018 1213 xerox
X17
10019 1212 xerox
X18

It does NOT hold the emailadresses. These will be put in another
table.
A client could have more than 1 clientnumber (xerox 1212 - 1213)
A client could have more than 1 emailaddress : (e-mail address removed) ;
(e-mail address removed)

I only need this table to extract or export the rows that match
the given criteria and send this extract to the given emailadresses :
So if i want to extract clientnumbers 1212 and 1213 and send the
extracted info to [email protected] and [email protected] i need to get
these criteria from a table or tables

I was thinking to make a table like this to retrieve the given
criteria to query the tblinvoices and send the email

My question now is : do i make only 1 table or more tables to put the
criteria data in.

Is this a good table or can this be done in a better way.
CLIENT EXTRACTNR EMAIL
1212 1212 [email protected]
1212 1212 [email protected]
1212 1213 [email protected]
1212 1213 [email protected]
2255 2255 [email protected]

Hope you understand what i mean and can help me.

 

[KARL DEWEY]

That structure for Client table is good. I used it in query below --
SELECT tblInvoices.ORDERNR, tblInvoices.CLIENTNR, tblInvoices.CLIENTNAME,
tblInvoices.REFERENCE, IIf([CLIENT] Is Null,"No match in Client
table",[EXTRACTNR]) AS Extract_Number, CLIENTnbr.EMAIL
FROM tblInvoices LEFT JOIN CLIENTnbr ON tblInvoices.CLIENTNR =
CLIENTnbr.CLIENT;

 

[MarMo]

Thanks for your time and effort to help.
Now i can continue developing my app.
Rgds
Mario