Need help with recordsets

[hbrems]

Hello,

i'm new to recordsets, can you help me out?

Table layout:

- contains reference numbers. Each ref number can hold multiple orders.
- each reference is linked to 1 e-mail address
- everything is stored in 1 table (no seperate reference and order table)
- this means reference numbers are in fact stored multiple times

How can I scroll through my table in order to get the following info.

Refnr1 = order1 + order2 + order3
Refnr2 = order4 + order5
Refnr3 = order6 + order7 + order8
etc.

In the end I need to mail this info to the customer.

Kind regards,
Hans B.

 

[The_Doc_Man]

Well, I can't resist a quick chide: Look up database normalization to see why your layout is wrong.

OK, given what you've got, if it is at least one order per record despite the non-unique reference number, then what you want is to sort the table by reference number. To do this, create a query that contains the information you want. For the reference number field, you can do a GroupBy or Order By to keep all reference numbers together. Then instead of opening the table, open the query. For most other parts of Access, the difference between a table and query is almost nil. A recordset can open a query as though it were a table (though opening it as a Dynaset is a better choice, usually).

For now, you can get by with your design. But it is seriously flawed if you intend to use this stuff long-term. You can find out why by searching this forum, access help, and google-search the web for "database normalization." Start at Wikipedia but don't stop there.

 

[The_Doc_Man]

The other part of your question is recordset operations. My best advice is for you to read the Access Help on recordset operations and look at the examples provided in the links within the Help facility.

 

[hbrems]

Well, I can't resist a quick chide: Look up database normalization to see why your layout is wrong.

I'm well aware that the lay-out of the table is wrong. The table is just an imported excel file which we get on a daily basis from the customer.

As I need to delete the old table every day and replace it with the new imported excel file I'm not considering to arrange it according to normalisation rules.

All I need to do is make sure that I send 1 e-mail for each reference number, containing it's orders. That and that only.

Kind regards,
Hans B.