Access 2013 One record from many with same key

John, Thank you for hanging in here with me...

Table: Facility A: Person - 123456 - Visits 4; Person - 234567 - Visits 5; Person - 345678 - Visits 2..

Each Table is a Facility table

Each person is a Record

The Visits are the Fields

The Numbers are the Values in the Cell (Record.Person & Field.Visit)

Facility A

Person        Visit

123456         4

234567         5

345678         2

Facility B

Person        Visit

123456         3

234567         4

456789         1

Final Output

Person       Facility A      Facility B    Facility N   Total

123456             4                 3                                w

234567             5                 4                                 x

345678             2                 0                                 y

456789             0                 1                                 z

VBA code, really? I can manage some SQL, but VBA... if that's what it takes, then I'm more than a little disappointed in Microsoft Access...

(I presume without VBA, I could use some Sum Function in an Access Table, i.e, not a query)

Access is quite capable of doing what you want - but to do so the data must be in the correct normalized structure. Again, three tables are ALL you need:

Facilities

  FaciltyID (I'd use an autonumber)

  Facilty ("A" or the actual name of the facility)

Persons

  PersonID (primary key, your existing PersonID)

  <demographic data about the person if appropriate>

Visits

  VisitID (autonumber primary key)

  PersonID (who visited the facility)

  FacilityID (which facility did they visit)

  YourNumber (the 4 or 3 or 9 or 0 in your existing visits field)

The final report - with persons down the left and facilities across the top - can be easily generated using a Crosstab query from these tables. Since you have 28 (right?) Visits tables, you will need 28 Append queries to migrate the data from them into the tall thin Visits table; or one Append query based on a UNION query to string all 28 tables together into one long recordset).

Sorry Arlan, but you are not helping us help you. You are not presenting a clear picture of your data. It would probably help a great deal if you can post a screen shot of your Excel spreadsheet.

An Excel sheet is organized into Rows and Columns. For you to be able to convert this to an Access database you need to have a table of data. Such a table would have a column for the Facility, a column for the Person and a column for the Visits. Is that what you have or what?

Okay... I'm going to give it a shot John...

I'm sure you know what I mean when I say, I could swear I've been here many times before... I'll let you know what is what... Thanks a bunch.

John,

When you say 3 tables are all I need...

__________________

1. I created a single Visits Table which will receive, I presume, the data for the Report.
   1. In the Visits Table I have Field Names only, IDBID], Name, ID, Facility ID, Visits
2. I created 28 Visits Tables, The only differences are their names; inside, they are Standard Formatted and empty. I presume the information is going in or through here on the way to the report.
   1. I created 28 Visits Queries to carry the data to the Visits Table (Singular)
3. I created the Facilities Table with the headings of ID, Facility, address – all filled out, I expect this is a reference table.
4. I created the Person’s Table, with all the pertinent information, ID, Name, all filled out, I expect this is a reference table.
5. Time for the Process

If you are reading this, I am working on the process.